所以,我知道这是一个非常普遍的错误,但我没有更好的线索来解释我的查询发生了什么,我也不能解释它比这更好,感谢mysql没有给用户体验一个****的,所以请容忍我。
我使用的是PHP7.2.1和MySQL5.7。
我有个问题:
SELECT
sp.*
FROM
`status` s
LEFT JOIN status_pedido sp ON sp.status_pedido_id = s.status_pedido_id
WHERE
s.status_id = (
SELECT
`status`.status_id
FROM
pedido_item_status p1
LEFT JOIN `status` ON `status`.status_id = p1.status_id
LEFT JOIN pedido_item ON pedido_item.pedido_item_id = p1.pedido_item_id
INNER JOIN ( SELECT MAX( pi.cadastrado ) AS maxcadastro FROM pedido_item_status pi GROUP BY pi.pedido_item_id ) p2 ON ( p1.cadastrado = p2.maxcadastro )
AND p1.excluido IS NULL
WHERE
p1.pedido_id = 15720
ORDER BY
`status`.sta_ordem ASC
LIMIT 1
)
它在navicat或heidi上运行良好,只返回1个结果。但当我在php上执行mysqli->query时,返回空行:
object(mysqli_result)#135 (5) { ["current_field"]=> int(0) ["field_count"]=> int(11) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }
navicat返回以下内容:
+----+-------------+-------------+--------+-------------------+-------------+---------+---------------------------+-------+---------------------------------------------------------------------+--+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------------+--------+-------------------+-------------+---------+---------------------------+-------+---------------------------------------------------------------------+--+
| 1 | PRIMARY | s | const | PRIMARY | PRIMARY | 4 | const | 1 | | |
| 1 | PRIMARY | sp | const | PRIMARY | PRIMARY | 4 | const | 1 | | |
| 2 | SUBQUERY | p1 | ref | idx_1,idx_2,idx_3 | idx_1 | 10 | const,const | 5 | Using index condition; Using where; Using temporary; Using filesort | |
| 2 | SUBQUERY | status | eq_ref | PRIMARY | PRIMARY | 4 | emgraf2.p1.status_id | 1 | | |
| 2 | SUBQUERY | pedido_item | eq_ref | PRIMARY | PRIMARY | 4 | emgraf2.p1.pedido_item_id | 1 | Using index | |
| 2 | SUBQUERY | <derived3> | ref | <auto_key0> | <auto_key0> | 5 | emgraf2.p1.cadastrado | 10 | Using index | |
| 3 | DERIVED | pi | ALL | idx_2,idx_3 | | | | 18750 | Using temporary; Using filesort | |
+----+-------------+-------------+--------+-------------------+-------------+---------+---------------------------+-------+---------------------------------------------------------------------+--+
在php上返回:
array(10) {
["id"]=>
string(1) "1"
["select_type"]=>
string(7) "PRIMARY"
["table"]=>
NULL
["type"]=>
NULL
["possible_keys"]=>
NULL
["key"]=>
NULL
["key_len"]=>
NULL
["ref"]=>
NULL
["rows"]=>
NULL
["Extra"]=>
string(51) "Impossible WHERE noticed after reading const tables"
}
array(10) {
["id"]=>
string(1) "2"
["select_type"]=>
string(8) "SUBQUERY"
["table"]=>
string(2) "p1"
["type"]=>
string(3) "ref"
["possible_keys"]=>
string(17) "idx_1,idx_2,idx_3"
["key"]=>
string(5) "idx_1"
["key_len"]=>
string(2) "10"
["ref"]=>
string(11) "const,const"
["rows"]=>
string(1) "5"
["Extra"]=>
string(67) "Using index condition; Using where; Using temporary; Using filesort"
}
array(10) {
["id"]=>
string(1) "2"
["select_type"]=>
string(8) "SUBQUERY"
["table"]=>
string(6) "status"
["type"]=>
string(6) "eq_ref"
["possible_keys"]=>
string(7) "PRIMARY"
["key"]=>
string(7) "PRIMARY"
["key_len"]=>
string(1) "4"
["ref"]=>
string(20) "emgraf2.p1.status_id"
["rows"]=>
string(1) "1"
["Extra"]=>
NULL
}
array(10) {
["id"]=>
string(1) "2"
["select_type"]=>
string(8) "SUBQUERY"
["table"]=>
string(11) "pedido_item"
["type"]=>
string(6) "eq_ref"
["possible_keys"]=>
string(7) "PRIMARY"
["key"]=>
string(7) "PRIMARY"
["key_len"]=>
string(1) "4"
["ref"]=>
string(25) "emgraf2.p1.pedido_item_id"
["rows"]=>
string(1) "1"
["Extra"]=>
string(11) "Using index"
}
array(10) {
["id"]=>
string(1) "2"
["select_type"]=>
string(8) "SUBQUERY"
["table"]=>
string(10) "<derived3>"
["type"]=>
string(3) "ref"
["possible_keys"]=>
string(11) "<auto_key0>"
["key"]=>
string(11) "<auto_key0>"
["key_len"]=>
string(1) "5"
["ref"]=>
string(21) "emgraf2.p1.cadastrado"
["rows"]=>
string(2) "10"
["Extra"]=>
NULL
}
array(10) {
["id"]=>
string(1) "3"
["select_type"]=>
string(7) "DERIVED"
["table"]=>
string(2) "pi"
["type"]=>
string(3) "ALL"
["possible_keys"]=>
string(11) "idx_2,idx_3"
["key"]=>
NULL
["key_len"]=>
NULL
["ref"]=>
NULL
["rows"]=>
string(5) "18750"
["Extra"]=>
string(31) "Using temporary; Using filesort"
}
据我从类似问题中了解到的,这与客户端保持连接并在分析整个查询后执行查询有关,而当一个内部where似乎返回0行时,php就停止了。但我不知道如何在我的查询中解决这个问题,因为我没有在sql上设置任何变量,只是进行子查询。
还有一个问题:完全相同的查询在另一个代码块上运行,我不明白为什么会这样。下面是有问题的代码:
$sql_sta = "SELECT pis.pedido_item_status_id, pi.pedido_item_id, pi.status_id, p.pedido_id, psp.pedido_status_pedido_id, sp.stp_ordem
FROM pedido_item_status pis
LEFT JOIN pedido_item pi ON pis.pedido_item_id = pi.pedido_item_id
LEFT JOIN pedido p ON pi.pedido_id = p.pedido_id
LEFT JOIN status_pedido sp ON sp.status_pedido_id = p.status_pedido_id
LEFT JOIN pedido_status_pedido psp ON (psp.status_pedido_id = sp.status_pedido_id AND psp.pedido_id = p.pedido_id)
WHERE pis.pedido_item_status_id = {$_GET['del']}
LIMIT 1";
$res_sta = ClassDb::query($sql_sta);
$status_del = $res_sta->fetch_assoc();
$sql_ped = "SELECT sp.* FROM `status` s
LEFT JOIN status_pedido sp ON sp.status_pedido_id = s.status_pedido_id
WHERE s.status_id = (
SELECT `status`.status_id FROM pedido_item_status p1
LEFT JOIN `status` ON `status`.status_id = p1.status_id
LEFT JOIN pedido_item ON pedido_item.pedido_item_id = p1.pedido_item_id
INNER JOIN ( SELECT pi.pedido_id, MAX( pi.cadastrado ) AS maxcadastro FROM pedido_item_status pi GROUP BY pi.pedido_item_id ) p2
ON ( p1.cadastrado = p2.maxcadastro ) AND p1.excluido IS NULL
WHERE p1.pedido_id = 15720
ORDER BY `status`.sta_ordem ASC
LIMIT 1
)";
$res_ped = ClassDb::query($sql_ped);
以下是工作代码:
$sql_sta = "SELECT sp.* FROM pedido_item pi
LEFT JOIN pedido p ON pi.pedido_id = p.pedido_id
LEFT JOIN status_pedido sp ON sp.status_pedido_id = p.status_pedido_id
WHERE pi.pedido_item_id = {$_POST['pedido_item_id']}";
$res_sta = ClassDb::query($sql_sta);
$status_antigo = $res_sta->fetch_assoc();
$sql_ped = "SELECT sp.* FROM `status` s
LEFT JOIN status_pedido sp ON sp.status_pedido_id = s.status_pedido_id
WHERE s.status_id = (
SELECT `status`.status_id FROM pedido_item_status p1
LEFT JOIN `status` ON `status`.status_id = p1.status_id
LEFT JOIN pedido_item ON pedido_item.pedido_item_id = p1.pedido_item_id
INNER JOIN ( SELECT MAX( pi.cadastrado ) AS maxcadastro FROM pedido_item_status pi GROUP BY pi.pedido_item_id ) p2
ON ( p1.cadastrado = p2.maxcadastro ) AND p1.excluido IS NULL
WHERE p1.pedido_id = {$_POST['pedido_id']}
ORDER BY `status`.sta_ordem ASC
LIMIT 1
)";
$res_ped = ClassDb::query($sql_ped);
$status_atual = $res_ped->fetch_assoc();
它们之间唯一的区别是我首先运行不同的查询来检索 pedido_id
,我尝试将变量更改为静态值并对第一个查询进行注解,输出仍然是相同的,而且,工作代码在表单简单post之后运行,在我插入一行之后运行,而有问题的运行来自ajax get请求,在我更新一行之后软删除它(设置列) excluido
到now(),将其视为 deleted_at
来自拉威尔)。尝试在navicat上手动运行querys,效果很好。
因为post已经太长了,而且表又大又复杂,只要假设结果在那里,所有的列都存在,查询在navicat上执行得很好,并返回一个结果,同时还返回一个使用相同查询的工作代码块上的结果。
edit1:没有php错误或异常,也没有mysqli错误,我已经将它们设置为显示。查询只返回空结果,代码正常流动。
编辑2:尝试直接在mysql shell上运行,结果与navicat或heidi相同:
+------------------+-----------+----------------+-----------+----------+------------+------------+---------+----------+-----------+-----------+
| status_pedido_id | stp_nome | stp_observacao | stp_ordem | excluido | cadastrado | atualizado | stp_cor | stp_icon | stp_alert | status_id |
+------------------+-----------+----------------+-----------+----------+------------+------------+---------+----------+-----------+-----------+
| 7 | Cancelado | NULL | 8 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
+------------------+-----------+----------------+-----------+----------+------------+------------+---------+----------+-----------+-----------+
1 row in set (0.0269 sec)
1条答案
按热度按时间ego6inou1#
嗯,我刚刚发现了“错误”的原因。事实上,这根本不是一个错误,只是我在装傻。
在php代码中,我更新了一行
excluido
至NOW()
,所以其中一个选定的行被软删除,这导致查询返回0行,但是由于autocommit被设置为false,并且在我转储查询时我没有提交查询,所以更新被回滚,并且我在navicat上运行查询时没有任何软删除的行,这就是为什么它可以工作的原因。现在,在调试之前提交查询,行被软删除,navicat/heidi还返回0行。我还没有弄明白为什么它返回0行,但是我认为这个问题属于另一个问题,而不是这个问题,所以如果我不能自己解决这个问题,我将提出一个新问题。我只是回答我自己的问题来结束这条线,不要浪费你宝贵的时间。
感谢所有在评论中帮助我的人。