LEFT JOIN关联表为空时FIELD(joined_table.column, NULL)=0无匹配结果的技术疑问

这是个挺让人困惑的问题，核心原因出在MySQL 5.7优化器对FIELD()函数的错误等价转换，以及NULL在SQL中的特殊处理逻辑，咱们一步步拆解：

1. 先明确FIELD()函数在MySQL 5.7中的行为 #

根据官方文档，FIELD(str, str1, str2...)的规则是：

• 如果所有参数都是NULL（比如FIELD(NULL, NULL)），返回0；
• 如果str是NULL，但列表里有非NULL值（比如FIELD(NULL, 'a', NULL)），返回第一个NULL在列表中的位置（这里是3）；
• 如果str是NULL，列表里没有NULL，返回0；
• 如果str非NULL，列表里有NULL，返回0（因为非NULL和NULL不相等）。

你的场景里，LEFT JOIN task tj ON FALSE意味着tj的所有列都是NULL，所以FIELD(tj.id, NULL)本质就是FIELD(NULL, NULL)，理论上应该返回0——这也和你移除WHERE子句后看到的field_zero=0结果一致。

2. 为什么WHERE子句里的FIELD(tj.id, NULL)=0没返回结果？ #

问题出在MySQL 5.7的优化器上：它会把FIELD(x, NULL)=0这个条件错误地等价转换为x IS NOT NULL。

当你加上WHERE子句后，优化器会偷偷修改你的查询逻辑：原本应该判断FIELD(tj.id, NULL)=0（结果为true），却被转换成了判断tj.id IS NOT NULL（结果为false，因为tj.id都是NULL）。同时，这个条件会把LEFT JOIN隐式转换成INNER JOIN，自然就没有结果返回了。

你可以用EXPLAIN EXTENDED查看优化后的查询语句，就能看到这个转换痕迹——WHERE子句里的FIELD条件已经被替换成了tj.id IS NOT NULL。

3. 为什么直接写FIELD(NULL, NULL)=0或左表列的情况能正常工作？ #

当参数是常量NULL或者左表的列时，优化器不会触发这个错误的等价转换：

• 对于FIELD(NULL, NULL)=0，优化器能直接计算出结果为true，所以返回行；
• 对于左表的column_is_null（即使它是NULL），因为左表在LEFT JOIN中是保留所有行的，优化器不会把条件转换成column_is_null IS NOT NULL，所以条件能正确计算。

4. 这是已知bug吗？ #

是的，这属于MySQL 5.7优化器处理FIELD()函数与NULL交互时的逻辑错误。这个问题在MySQL 8.0及以上版本中已经被修复，优化器不会再做这种错误的等价转换。

临时解决方案（针对MySQL 5.7） #

如果暂时无法升级版本，可以用以下两种方式绕过这个问题：

• 用COALESCE包装右表列，避免优化器误判：

  SELECT t.id, FIELD(tj.id, NULL) AS field_zero 
  FROM task t LEFT JOIN task tj ON FALSE 
  WHERE FIELD(COALESCE(tj.id, 'dummy'), NULL) = 0 
  LIMIT 10
  

• 直接替换为等价的IS NULL条件（因为tj.id为NULL时FIELD(tj.id, NULL)=0成立）：

  SELECT t.id, FIELD(tj.id, NULL) AS field_zero 
  FROM task t LEFT JOIN task tj ON FALSE 
  WHERE tj.id IS NULL 
  LIMIT 10
  

内容的提问来源于stack exchange，提问作者goulashsoup