mysql - MySQL 5.1 和 5.6 之间的复合查询被破坏

Question

我有一个为 MySQL 制作的软件升级脚本。

此升级的一部分涉及在表中为预先存在的实体创建行(新版本的软件为新的“父”实体创建一个额外的关联实体，而旧版本没有，所以我必须创建手动)。子实体 ID 是根据命名约定组成的，而不是自动递增代理键。

所以我正在编写这个匿名查询:

INSERT INTO CHILD_ENTITIES (CHILD_ID, DESCRIPTION, OBJECT_TYPE)
(SELECT 
    CONCAT('PRE_', PARENT_ID, '_SUFF') AS CHILD_ID,
    CONCAT('DESCRIPTION FOR ', PARENT_NAME) AS DESCRIPTION,
    'SYSTEM' AS OBJECT_TYPE
FROM
    PARENT_ENTITIES
WHERE
    CHILD_ID NOT IN (SELECT 
            CHILD_ID
        FROM
            CHILD_ENTITIES));

表结构

CREATE TABLE `CHILD_ENTITIES` (
  `CHILD_ID` varchar(50) NOT NULL,
  `DESCRIPTION` varchar(200) DEFAULT NULL,
  `OBJECT_TYPE` varchar(20) NOT NULL,
  PRIMARY KEY (`CHILD_ID`)
)

该查询应该从父表中选择所有父实体，操作行以获取子实体的唯一三列(请注意，没有 FK 约束，这是由于数据库设计造成的)，然后将它们插入到这些 child 。对于每个 parent ，都应该创建一个新的 child 。 WHERE 子句是一个预防性语句，实际上是为了避免重复而编写的，但即使没有 WHERE，查询在正常设置中仍然不会失败(因为这些 ID 是通过生成的)约定，从不手动插入)。

问题是查询在 MySQL 5.1.69-community 上按预期工作正常，但在 5.6.10 上运行时(使用 Navicat，如果重要的话)它的行为不同:当父实体存在时，不会插入记录。

通过几次尝试分析查询，我发现 WHERE CHILD ID NOT IN ( SELECT CHILD_ID... 被误解，指的是子查询的列而不是父查询的列。如果我稍微改变一下查询

SELECT 
    CONCAT('PRE_', PARENT_ID, '_SUFF') AS CHILD_ID,
    CONCAT('DESCRIPTION FOR ', PARENT_NAME) AS DESCRIPTION,
    'SYSTEM' AS OBJECT_TYPE
FROM
    PARENT_ENTITIES
WHERE
    CHILD_ID NOT IN (SELECT 
            CHILD_ID AS DUMMY
        FROM
            CHILD_ENTITIES);

结果:“IN/ALL/ANY 子查询”中存在未知列“CHILD_ID”

两个版本的 MySQL 都会出现该错误。

那么，我应该如何进行这样的查询以从父级 ID 开始填充子实体？

Answer 1

我建议您在所有列中添加表格前缀，然后重试:

INSERT INTO CHILD_ENTITIES (CHILD_ID, DESCRIPTION, OBJECT_TYPE)
SELECT 
    CONCAT('PRE_', p.PARENT_ID, '_SUFF') AS CHILD_ID,
    CONCAT('DESCRIPTION FOR ', p.PARENT_NAME) AS DESCRIPTION,
    'SYSTEM' AS OBJECT_TYPE
FROM
    PARENT_ENTITIES AS p
WHERE
    p.CHILD_ID NOT IN (SELECT 
            c.CHILD_ID
        FROM
            CHILD_ENTITIES AS c);

如果也因错误而失败，那么它可能是一个错误。升级到最新(5.6.13)版本，看看错误是否仍然存在。

<小时/>

再次阅读查询，我不知道它将如何避免重复。也许你想这样写？

---
WHERE
    CONCAT('PRE_', p.PARENT_ID, '_SUFF') NOT IN (SELECT 
            c.CHILD_ID
        FROM
            CHILD_ENTITIES AS c);