MySQL“未进入"查询突然停止返回结果 [英] MySQL "Not IN" query suddenly stopped returning results

问题描述

我正在为一个共享托管环境中的网站做一些工作，该环境有对我们翻转设置的历史.本周，一个更新脚本突然停止工作，其根本原因是用于返回结果的 NOT IN 子句不再这样做了.

I'm doing some work for a site on a shared hosting environment that has a history of flipping settings on us. This week, an update script suddenly stopped working, and the underlying reason is that a NOT IN clause that used to return results is no longer doing so.

查询本质上是这样的:

SELECT  *
FROM db1.entry
where entry_id not in
(
  select entry_id from db2.content
)

我可以通过直接检查两个表来验证确实有应该返回的记录.运行以下两个查询时，第一个返回第二个不返回的 entry_ids:

I can verify that there are indeed records that should be returned by inspecting the two tables directly. When running the below two queries, the first one returns entry_ids that the second does not:

SELECT  *
FROM db1.entry 
order by entry_id desc

SELECT  *
FROM db2.content
order by entry_id desc

再重申一遍，这几个月都正常运行.没有进行任何代码更改，但有可能在此过程中更改了 MySQL 设置.PHP 环境中的某些内容也有可能发生了变化，但这似乎不太可能，因为在从 phpMyAdmin 运行时，有问题的查询以与从实时站点运行时相同的方式失败.

And to re-iterate, this was all working correctly for several months. No code changes were made, but it's possible that a MySQL setting was changed along the way. It's also possible that something changed in the PHP environment, but that seems less likely since the query in question fails in the same way when run from phpMyAdmin as it does when run from the live site.

当然，它仍然可以在我的开发箱上完美运行.

And, of course, it still works perfectly on my dev box.

实时站点运行的是 MySQL 4.1.11 版.我的问题是，有人知道该版本的 MySQL 设置会改变这些 NOT IN 查询的工作方式吗?

The live site is running MySQL version 4.1.11. My question is, does anybody know of a MySQL setting for that version that would change the way these NOT IN queries work?

谢谢.

推荐答案

确保您的内部查询不返回 NULL 的.

Make sure your inner query does not return NULL's.

来自文档:

为了符合 SQL 标准，IN 返回 NULL 不仅在左侧的表达式为 NULL，但如果在列表中找不到匹配项并且列表中的表达式之一是 NULL.

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

这篇关于MySQL“未进入"查询突然停止返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！