MySQL是否支持WITH子句结合INSERT语句？对比PostgreSQL可行用法

好问题！MySQL对WITH ... INSERT ...这种语法的支持分版本来看，我来帮你理清楚：

首先看版本支持情况 #

MySQL从8.0版本开始正式支持CTE（公共表表达式），也就是WITH子句。如果你的MySQL版本低于8.0，执行这类语句肯定会报语法错误——这是最常见的原因。你可以执行SELECT VERSION();来确认自己的版本。

如果是MySQL 8.0及以上版本 #

这种WITH ... INSERT ...的写法是完全可行的，但你的原SQL有几个可以优化（避免潜在歧义）的地方：

1. 你在u CTE里用了隐式交叉连接（FROM unit_type AS utyp, pr），虽然逻辑没问题，但显式写出CROSS JOIN会让代码更清晰，降低后续维护的理解成本；
2. 两次SELECT加UNION ALL的写法可以合并成一个SELECT，用CASE语句生成不同的unt_number，代码更简洁，执行效率也更高。

修正后的代码示例

WITH pr AS (
    SELECT pr_id 
    FROM property AS p 
    WHERE p.pr_name = 'Royal Oaks Plaza'
),
u AS (
    SELECT 
        pr.pr_id, 
        utyp.utyp_name, 
        utyp.utyp_id 
    FROM unit_type AS utyp
    CROSS JOIN pr -- 显式标注交叉连接，逻辑更直观
)
INSERT INTO unit (pr_id, unt_number, utyp_id, unt_size)
SELECT 
    pr_id,
    CASE utyp_name 
        WHEN 'BEAUTY' THEN '100'
        WHEN 'RESTAURANT' THEN '110'
    END AS unt_number,
    utyp_id,
    FLOOR(500 + RAND() * 2000)
FROM u
WHERE utyp_name IN ('BEAUTY', 'RESTAURANT');

当然，如果你坚持用原有的UNION ALL写法，只要版本符合要求，调整后也能正常运行：

WITH pr AS (SELECT pr_id from property AS p WHERE p.pr_name = 'Royal Oaks Plaza'),
u AS (SELECT pr.pr_id AS pr_id, utyp.utyp_name AS utyp_name, utyp.utyp_id AS utyp_id FROM unit_type AS utyp CROSS JOIN pr)
INSERT INTO unit (pr_id, unt_number, utyp_id, unt_size)
SELECT pr_id, '100', utyp_id, FLOOR(500 + RAND() * 2000) FROM u WHERE utyp_name = 'BEAUTY' UNION ALL
SELECT pr_id, '110', utyp_id, FLOOR(500 + RAND() * 2000) FROM u WHERE utyp_name = 'RESTAURANT';

如果是MySQL 8.0以下版本（如5.7） #

这类版本不支持CTE，你可以用子查询替代，同样能实现批量插入带外键引用记录的需求：

INSERT INTO unit (pr_id, unt_number, utyp_id, unt_size)
SELECT 
    pr.pr_id,
    '100' AS unt_number,
    utyp.utyp_id,
    FLOOR(500 + RAND() * 2000)
FROM unit_type AS utyp
CROSS JOIN (SELECT pr_id FROM property WHERE pr_name = 'Royal Oaks Plaza') AS pr
WHERE utyp.utyp_name = 'BEAUTY'
UNION ALL
SELECT 
    pr.pr_id,
    '110' AS unt_number,
    utyp.utyp_id,
    FLOOR(500 + RAND() * 2000)
FROM unit_type AS utyp
CROSS JOIN (SELECT pr_id FROM property WHERE pr_name = 'Royal Oaks Plaza') AS pr
WHERE utyp.utyp_name = 'RESTAURANT';

总结 #

• 8.0+版本：支持WITH ... INSERT ...语法，调整细节即可正常运行；
• 低于8.0版本：用子查询替代CTE，同样能达成你的批量插入需求。

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