MySQL中WHERE子句使用IF实现跨表动态字段匹配报错咨询

odit_docs 表 #

id|name |regNumber|mps_id
1 |test1|123      | 1
2 |test2|124      | NULL

mps 表 #

id|name |mpsRegnomer|
1 |test1|1233       |
2 |test2|1244       |

目前编写的查询语句 #

SELECT * FROM `odit_docs` as od where IF(od.mps_id IS NOT NULL, SELECT mps.mpsRegnomer FROM mps where mps.id = od.mps_id, od.regNumber) = '123'

这个语句执行时会直接报语法错误，拿不到预期结果。跑不通的核心原因是IF函数里嵌入的标量子查询没加闭合括号，不符合MySQL的语法解析规则。

实际需求逻辑 #

要实现的取值规则很明确：

• 当 od.mps_id 字段非空时，通过 od.mps_id <=> mps.id 的等值关联关系，从mps表取对应记录的mpsRegnomer值
• 当 od.mps_id 字段为NULL时，直接取odit_docs表自身的regNumber字段值
  最终要校验上述if-else分支返回的值是不是等于指定值（本例为'123'）。

匹配规则具体示例 #

• odit_docs表id为1的行：因为mps_id非空，需要校验关联取到的mps.mpsRegnomer是不是等于'123'
• odit_docs表id为2的行：因为mps_id为NULL，需要校验自身的od.regNumber是不是等于'123'

可正常运行的实现方案 #

方案1：最小改动修正语法（和原逻辑完全一致） #

只要把IF函数里的子查询用括号完整包裹，就能符合语法规则正常运行：

SELECT * 
FROM `odit_docs` as od 
WHERE IF(
  od.mps_id IS NOT NULL, 
  (SELECT mps.mpsRegnomer FROM mps where mps.id = od.mps_id),
  od.regNumber
) = '123'

方案2：左连接关联实现（性能更优，适合大数据量场景） #

通过左连接一次性关联mps表，不需要对odit_docs的每一行都重复执行子查询扫描，数据量大时性能会好很多：

SELECT od.*
FROM `odit_docs` as od 
LEFT JOIN mps ON od.mps_id = mps.id
WHERE IF(od.mps_id IS NOT NULL, mps.mpsRegnomer, od.regNumber) = '123'

内容的提问来源于stack exchange，提问作者Мирослав Пейчев