嵌套SQL SELECT返回null或子查询超过一行错误该如何解决？

问题根因分析 #

你遇到的报错对应三个核心问题：

• 子查询返回多行：new_database.User、new_database.Location表中存在NAME字段重复的记录，单个operator/lokalizacja值匹配到了多条ID记录，导致子查询返回值不唯一报错。
• 字段不能为null：要么是存在OldDoc的operator/lokalizacja值在新库关联表中找不到匹配项，要么是上面的多行报错导致返回值被判定为无效null。
• 语法错误：你后续修改的子查询逻辑错误，在子查询中再次关联了全量OldDoc表，没有和外层的ol行绑定，直接返回全表匹配的所有ID，触发语法报错。

修复步骤 #

1. 先排查处理脏数据（必须优先操作） #

执行以下4条查询确认数据问题，处理完成后再执行迁移：

• 排查用户表重名：

SELECT NAME, COUNT(*) as cnt FROM new_database.User GROUP BY NAME HAVING cnt > 1;

• 排查地点表重名：

SELECT NAME, COUNT(*) as cnt FROM new_database.Location GROUP BY NAME HAVING cnt > 1;

• 排查找不到匹配的操作员：

SELECT DISTINCT ol.operator FROM old_database.OldDoc ol 
LEFT JOIN new_database.User u ON u.NAME = ol.operator 
WHERE u.ID IS NULL;

• 排查找不到匹配的地点：

SELECT DISTINCT ol.lokalizacja FROM old_database.OldDoc ol 
LEFT JOIN new_database.Location loc ON loc.NAME = ol.lokalizacja 
WHERE loc.ID IS NULL;

将查询到的重名记录合并、缺失的关联数据补充到新库对应表后，再执行迁移SQL。

2. 替换为JOIN写法的迁移SQL（更稳定） #

用JOIN逻辑替换嵌套子查询，避免子查询的各类不稳定问题，推荐写法如下：

INSERT INTO new_database.NewDoc (ID, NR_DOC, NR_HANDLE, DOC_DATE, PLANNING_RELEASE, QUANTITY, CASES, VOLUME, ZAPIS, USER_ID, LOCATION_ID, TYPE_ID)
SELECT 
    ol.id,
    ol.nrWZ,
    ol.nrHandle,
    ol.dataWZ,
    ol.planowaneWydanie,
    ol.qty,
    ol.cart,
    ol.volume,
    ol.Zapis,
    u.ID AS USER_ID,
    loc.ID AS LOCATION_ID,
    1
FROM  old_database.OldDoc ol
INNER JOIN new_database.User u ON u.NAME = ol.operator
INNER JOIN new_database.Location loc ON loc.NAME = ol.lokalizacja;

如果需要保留所有OldDoc记录，允许未匹配的ID填默认值，可以把INNER JOIN改成LEFT JOIN，同时用COALESCE设置默认值：

COALESCE(u.ID, 0) AS USER_ID,
COALESCE(loc.ID, 0) AS LOCATION_ID,

如果暂时不想处理重名问题，要强制取第一个匹配的ID，也可以在原写法的子查询中加LIMIT 1（不推荐，优先处理脏数据）：

(SELECT u.ID FROM new_database.User u WHERE u.NAME = ol.operator LIMIT 1),
(SELECT loc.ID FROM new_database.Location loc WHERE loc.NAME = ol.lokalizacja LIMIT 1),

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