MySQL“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.

我正在为共享主机环境中的某个站点做一些工作,该环境有翻转我们设置的历史记录。本周,更新脚本突然停止工作,其根本原因是用于返回结果的NOT IN子句不再这样做。

The query, in its essence, is this:

查询的本质是:

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

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:

我可以通过直接检查这两个表来验证是否确实存在应该返回的记录。运行以下两个查询时,第一个返回entry_ids,第二个不返回:

SELECT  *
FROM db1.entry 
order by entry_id desc

SELECT  *
FROM db2.content
order by entry_id desc

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.

为了重新进行迭代,这几个月都正常工作。没有进行任何代码更改,但是MySQL设置可能会在此过程中发生变化。 PHP环境中也可能发生了某些变化,但这似乎不太可能,因为当从phpMyAdmin运行时,有问题的查询以与从实时站点运行时相同的方式失败。

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

当然,它仍然可以在我的开发盒上完美运行。

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?

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

Thanks.

2 个解决方案

#1

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

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

From documentation:

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.

为了符合SQL标准,IN不仅在左侧的表达式为NULL时返回NULL,而且如果在列表中找不到匹配且列表中的一个表达式为NULL,则返回NULL。

#2

you probably have a NULL value in at least one row for entry_id use a LEFT JOIN or NOT EXISTS instead

你可能在entry_id的至少一行中有一个NULL值,而是使用LEFT JOIN或NOT EXISTS

#1