有人可以解释为什么这两个查询(一个使用 IN 和一个使用 EXISTS)在 MySQL 5.6 中返回不同的结果，而在 MySQL 5.5 中却没有？

使用 EXPLAIN，我可以看到每个不同的执行计划，但我需要帮助来理解发生了什么，以及为什么这个 IN 逻辑在 5.6 中被破坏但没有5.5？

说明问题的 fiddle :http://sqlfiddle.com/#!9/da52b/95

成员可以有两个地址:家庭地址和公司地址。期望的结果是提供区域 X 并获得在该区域具有邮寄地址的所有成员的列表。邮寄地址如果存在则为公司地址，否则为家庭住址。城市可以属于一个或多个地区。

简化的数据库结构和数据:

CREATE TABLE `city` (
  `c_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `c_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`c_id`)
);

INSERT INTO `city`
VALUES
  ('1', 'Hillsdale'),
  ('2', 'Smallville'),
  ('3', 'Oakside'),
  ('4', 'Lakeview');

CREATE TABLE `city_region` (
  `cr_city` int(11) unsigned NOT NULL,
  `cr_region` int(11) NOT NULL,
  PRIMARY KEY (`cr_city`,`cr_region`)
);

INSERT INTO `city_region`
VALUES
  ('1', '3'),
  ('2', '1'),
  ('3', '1'),
  ('3', '2'),
  ('4', '1'),
  ('4', '3');

CREATE TABLE `firm_address` (
  `fa_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fa_member` int(11) NOT NULL,
  `fa_city` int(11) NOT NULL,
  PRIMARY KEY (`fa_id`)
);

INSERT INTO `firm_address`
VALUES
  ('1', '1', '3'),
  ('2', '2', '2'),
  ('3', '3', '1'),
  ('4', '6', '2'),
  ('5', '7', '1');

CREATE TABLE `home_address` (
  `ha_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ha_member` int(11) NOT NULL,
  `ha_city` int(11) NOT NULL,
  PRIMARY KEY (`ha_id`)
);

INSERT INTO `home_address`
VALUES
  ('1', '1', '2'),
  ('2', '2', '3'),
  ('3', '3', '1'),
  ('4', '4', '1'),
  ('5', '5', '2'),
  ('6', '6', '2');

CREATE TABLE `member` (
  `m_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `m_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`m_id`)
);

INSERT INTO `member`
VALUES
  ('1', 'John'),
  ('2', 'Bob'),
  ('3', 'Dave'),
  ('4', 'Jane'),
  ('5', 'Mary'),
  ('6', 'Karen'),
  ('7', 'Christie');

CREATE TABLE `region` (
  `r_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `r_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`r_id`)
);

INSERT INTO `region`
VALUES
  ('1', 'Central'),
  ('2', 'Lake District'),
  ('3', 'Westside');

查询1(错误，缺少一个成员):

SELECT * FROM member

LEFT OUTER JOIN home_address ON m_id = ha_member
LEFT OUTER JOIN city home_city ON ha_city = home_city.c_id
LEFT OUTER JOIN firm_address ON m_id = fa_member
LEFT OUTER JOIN city firm_city ON fa_city = firm_city.c_id

WHERE 1 IN (
    SELECT r_id
    FROM region
    INNER JOIN city_region ON r_id = cr_region
    WHERE cr_city = IF(fa_city IS NULL, ha_city, fa_city)
)

查询 2(返回正确结果):

SELECT * FROM member

LEFT OUTER JOIN home_address ON m_id = ha_member
LEFT OUTER JOIN city home_city ON ha_city = home_city.c_id
LEFT OUTER JOIN firm_address ON m_id = fa_member
LEFT OUTER JOIN city firm_city ON fa_city = firm_city.c_id

WHERE EXISTS (
    SELECT r_id
    FROM region
    INNER JOIN city_region ON r_id = cr_region
    WHERE cr_city = IF(fa_city IS NULL, ha_city, fa_city)
    AND r_id = 1
)

如果您能帮助理解这种不一致，我们将不胜感激。

谢谢。

最佳答案

我今天花了一些时间看这个，它似乎是 MySQL 5.6 中的一个错误。 (我也测试了MySQL 5.6.15，得到了相同的结果。)

MySQL 5.6 在执行此查询时使用了一些新的优化，但它们似乎不对差异负责，因为它无助于设置例如:

set session optimizer_switch="block_nested_loop=off";

使用 IFNULL(fa_city, ha_city) 而不是 IF(fa_city IS NULL, ha_city, fa_city) 确实会产生正确的结果，因此错误似乎在某处IF() 的处理。

关于mysql - 为什么这些查询在 MySQL 5.5 和 5.6 中产生不同的结果？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/21268087/