WITH子句结合INSERT...SELECT执行报SQL 1064错误的问题咨询

返回1064语法错误和WITH子句是否支持搭配INSERT...SELECT没有直接关系，是SQL写法存在两处明确问题：

• CTE（WITH定义的临时结果集）内部的UNION写法错误：最后一个SELECT分支末尾多写了一个UNION关键字。UNION的作用是连接两个相邻的查询结果集，末尾悬空的UNION没有后续查询对接，会直接被解析器判定为语法错误。
• 单独选中SELECT部分执行能正常运行，是因为多数数据库客户端执行选中片段时，会自动截断片段末尾无意义的孤立关键字，不会做全量严格校验；但整句提交执行时，解析器会做完整语法检查，这个多余的UNION就会触发报错。

首先移除CTE中最后一个SELECT后的多余UNION，同时可以去掉不必要的子查询嵌套，修正后的语句如下：

WITH insertData (profileId, intData, stringData) AS (
    SELECT up.UserProfileID, 0, 'data1' FROM userprofile up
    UNION
    SELECT up.UserProfileID, 1, 'data2' FROM userprofile up
    UNION
    SELECT up.UserProfileID, 2, 'data3' FROM userprofile up
)
INSERT INTO targetTable (UserProfileID, IntData, StringData)
SELECT profileId, intData, stringData
FROM insertData;

标准SQL语法本身完全支持WITH子句和INSERT...SELECT组合使用，目前主流分布式数据库（包括基于MySQL、PostgreSQL内核的分布式实例）均兼容该写法，不存在原生的语法限制。
如果修正后仍然报错，可以先核对数据库内核版本：

• 如果是MySQL 5.x及更早版本，内核本身不支持WITH（CTE）语法，无论是否搭配INSERT语句都无法运行，需要升级到8.0及以上版本
• 如果是部分对CTE支持不完善的轻量/自研分布式数据库，可以把CTE逻辑改写为内嵌子查询，绕过WITH的兼容问题，写法如下：

INSERT INTO targetTable (UserProfileID, IntData, StringData)
SELECT profileId, intData, stringData
FROM (
    SELECT up.UserProfileID AS profileId, 0 AS intData, 'data1' AS stringData FROM userprofile up
    UNION
    SELECT up.UserProfileID, 1, 'data2' FROM userprofile up
    UNION
    SELECT up.UserProfileID, 2, 'data3' FROM userprofile up
) AS insertData;

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