我试图弄清楚为什么通过mysql终端查询返回的结果与php执行的mysql查询返回的结果不匹配。
以下是shell中使用的mysql：
SELECT * FROM SANKEY_NODE AS n
LEFT OUTER JOIN TYPE_DETAIL as td
ON n.TYPE_DETAIL_ID = td.TYPE_DETAIL_ID
LEFT OUTER JOIN GRAPH_TYPE as t
ON td.GRAPH_TYPE_ID = t.GRAPH_TYPE_ID
WHERE CHART_ID = 39;以下是php：
function get_nodes_by_chart_id($con, $chart_id) {
$sql = 'SELECT * FROM SANKEY_NODE AS n
LEFT OUTER JOIN TYPE_DETAIL as td
ON n.TYPE_DETAIL_ID = td.TYPE_DETAIL_ID
LEFT OUTER JOIN GRAPH_TYPE as t
ON td.GRAPH_TYPE_ID = t.GRAPH_TYPE_ID
WHERE CHART_ID = '.$chart_id.';';
$result = mysqli_query($con, $sql);
return results_to_array($result);
}
function results_to_array($results) {
$rows = array();
while($row = mysqli_fetch_assoc($results)) {
$rows[] = $row;
}
return $rows;
}两个查询都返回十几个结果，但是它们对三个结果的表示方式不同。这三个结果是特别的，因为它们有一个 TYPE_DETAIL_ID 值，该值不在类型\详细信息表中。
在mysql shell中，将显示三个受影响节点的type\u detail\u id值，而在php返回的结果中，type\u detail\u id值为null。有人知道是什么导致了这种差异吗？我将非常感谢任何其他人能提供的见解！
表结构
mysql> describe SANKEY_NODE;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| NODE_NAME | varchar(100) | NO | | NULL | |
| NODE_PARENT | varchar(20) | YES | | NULL | |
| CHART_ID | int(11) | NO | PRI | NULL | |
| NODE_TYPE | varchar(100) | NO | | NULL | |
| TYPE_DETAIL_ID | varchar(20) | NO | PRI | NULL | |
+----------------+--------------+------+-----+---------+-------+
mysql> describe TYPE_DETAIL;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| COMPANY_ID | int(11) | NO | | NULL | |
| GRAPH_TYPE_ID | int(11) | NO | PRI | NULL | |
| TYPE_DETAIL_CD | varchar(20) | NO | PRI | NULL | |
| TYPE_DETAIL_NAME | varchar(100) | NO | | NULL | |
| TYPE_DETAIL_DESC | varchar(200) | YES | | NULL | |
| TYPE_DETAIL_ID | int(11) | NO | | NULL | |
| TYPE_IMAGE_ID | int(11) | YES | | NULL | |
| ACTIVE_FLAG | bit(1) | NO | | NULL | |
+------------------+--------------+------+-----+---------+-------+
mysql> describe GRAPH_TYPE;
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+----------------+
| COMPANY_ID | int(11) | NO | PRI | NULL | |
| GRAPH_TYPE_ID | int(11) | NO | UNI | NULL | auto_increment |
| TYPE_CD | varchar(20) | NO | PRI | NULL | |
| TYPE_NAME | varchar(100) | NO | | NULL | |
| TYPE_COLOR | varchar(50) | NO | | NULL | |
| TYPE_HIGHLIGHT_COLOR | varchar(50) | NO | | NULL | |
| ACTIVE_FLAG | bit(1) | NO | | NULL | |
+----------------------+--------------+------+-----+---------+----------------+评论回复
@cedric，仅使用第一个连接运行查询会产生相同的结果。三个值的类型\u detail \u id出现在sankey \u节点中，但不在类型\u detail \u id中，它们在shell结果中有一个定义的类型\u detail \u id，但在通过php的结果中没有（请参见下面的结果）。至于语法，我是“显式优于隐式”哲学的粉丝。
@jcaron，type\u detail\u id值是整数序列或ascii字符串，例如：
mysql> SELECT * FROM SANKEY_NODE AS n LEFT OUTER JOIN TYPE_DETAIL as td ON n.TYPE_DETAIL_ID = td.TYPE_DETAIL_ID WHERE CHART_ID = 3;
+--------------------------+-------------+----------+-------------+----------------+------------+---------------+----------------+--------------------+--------------------+----------------+---------------+-------------+
| NODE_NAME | NODE_PARENT | CHART_ID | NODE_TYPE | TYPE_DETAIL_ID | COMPANY_ID | GRAPH_TYPE_ID | TYPE_DETAIL_CD | TYPE_DETAIL_NAME | TYPE_DETAIL_DESC | TYPE_DETAIL_ID | TYPE_IMAGE_ID | ACTIVE_FLAG |
+--------------------------+-------------+----------+-------------+----------------+------------+---------------+----------------+--------------------+--------------------+----------------+---------------+-------------+
| CRD | SYS | 3 | System | 101004 | 7777 | 1 | CRD | Charles River | Charles River | 101004 | NULL | |
| FactSet | SYS | 3 | System | 101012 | 7777 | 1 | FACTSET | Factset | Factset | 101012 | NULL | |
| MSCI | SYS | 3 | System | 101016 | 7777 | 1 | RISKMETRICS | MSCI RiskWorld | MSCI RiskWorld | 101016 | NULL | |
| Trade Execution | FUN | 3 | Function | 109007 | 7777 | 9 | TE | Trade Execution | Trade Execution | 109007 | NULL | |
| Portfolio Mgmt | FUN | 3 | Function | 109003 | 7777 | 9 | PM | Portfolio Mgmt | Portfolio Mgmt | 109003 | NULL | |
| Performance & Risk | FUN | 3 | Function | 109002 | 7777 | 9 | PMR | Performance & Risk | Performance & Risk | 109002 | NULL | |
| Operations | FUN | 3 | Function | 109006 | 7777 | 9 | OPS | Operations | Operations | 109006 | NULL | |
| Decision Making | FUN | 3 | Function | 109001 | 7777 | 9 | DM | Decision Making | Decision Making | 109001 | NULL | |
| Compliance | FUN | 3 | Function | 109005 | 7777 | 9 | COMP | Compliance | Compliance | 109005 | NULL | |
| Portfolio Rebalance | SFUN | 3 | SubFunction | 201091 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Position Reconciliation | SFUN | 3 | SubFunction | 201092 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| PreTrade Compliance | SFUN | 3 | SubFunction | 201096 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Step-outs | SFUN | 3 | SubFunction | 201109 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Trade Matching | SFUN | 3 | SubFunction | 201125 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Trade Settlement | SFUN | 3 | SubFunction | 201129 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Functions | | 3 | Function | FUN | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| SubFunction | | 3 | SubFunction | SFUN | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Systems | | 3 | System | SYS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------------------------+-------------+----------+-------------+----------------+------------+---------------+----------------+--------------------+--------------------+----------------+---------------+-------------+
18 rows in set, 225 warnings (0.00 sec)
1条答案
按热度按时间9jyewag01#
下面是一个简单的解决方法，为每列指定表名：
这样，您就可以确保获得所需的数据