MySQL子查询在OR运算符后未按预期工作的原因排查

你的问题核心是：两个独立查询结果数（14+227=241）与OR合并查询结果数（249）不符，且超出预期范围。结合你已排除的因素，可能的原因如下：

1. 视图test_view存在重复行记录 #

如果test_view的定义（比如多表连接未添加DISTINCT，或关联逻辑导致同一主键对应多条行）包含重复行，会出现以下情况：

• 单独执行查询1或查询2时，可能因条件限制仅返回部分重复行；
• 执行OR合并查询时，MySQL会返回所有满足任一条件的行（包括重复行），导致结果总数超过两个单独查询的结果之和。

你可以通过以下语句验证：

-- 查看查询1结果的唯一行数
SELECT COUNT(DISTINCT id) FROM testdatabase.test_view AS test
WHERE test.deleted_at IS NULL AND test.a_id IS NULL AND test.lp_id IS NULL;

-- 查看查询2结果的唯一行数
SELECT COUNT(DISTINCT id) FROM testdatabase.test_view AS test
WHERE test.deleted_at IS NULL AND test.a_id IS NOT NULL AND test.a_deleted_at IS NOT NULL
AND test.id NOT IN (
    SELECT id FROM testdatabase.test_view
    WHERE a_deleted_at IS NULL AND deleted_at IS NULL AND id IS NOT NULL
);

如果唯一行数小于原查询的结果数，说明视图存在重复行。

2. MySQL优化器对OR与NOT IN的组合处理逻辑偏差 #

MySQL的查询优化器可能会将合并查询中的独立子查询转换为关联子查询，从而改变NOT IN的判断逻辑：

• 单独查询2中的子查询是独立执行的，返回固定的id集合；
• 合并查询中，优化器可能将子查询与外部表关联，逐行校验test.id NOT IN (...)，导致原本不满足条件的行被错误选中。

你可以通过临时表验证这个猜想：

-- 创建临时表存储子查询结果
CREATE TEMPORARY TABLE temp_ids AS 
SELECT id FROM testdatabase.test_view
WHERE a_deleted_at IS NULL AND deleted_at IS NULL AND id IS NOT NULL;

-- 使用临时表执行合并查询
SELECT * FROM testdatabase.test_view AS test
WHERE 
    ((test.deleted_at IS NULL AND test.a_id IS NULL AND test.lp_id IS NULL)
    OR
        (test.deleted_at IS NULL AND test.a_id IS NOT NULL AND test.a_deleted_at IS NOT NULL
    AND test.id NOT IN (SELECT id FROM temp_ids))
    );

如果结果数变为241，说明是优化器的逻辑转换导致的问题。

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