MySQL使用WITH定义CTE后执行INSERT语句返回语法错误如何解决

问题原因 #

• 你当前的SQL语句只完成了CTE定义、插入目标表的字段声明，缺少了从CTE中读取数据的SELECT查询步骤，这是直接触发语法错误的核心原因。
• 如果你的MySQL版本低于8.0，本身就不支持WITH CTE语法，也会抛出语法错误。
• MySQL中CTE搭配INSERT的语法顺序有两种规范，你可以任选一种适配。

修复后的完整代码 #

-- 写法1：WITH放在INSERT前（需要MySQL 8.0.19及以上版本支持）
WITH origem AS (
    SELECT
        cnpj,
        MAX(nome_empresa)                          AS nome_empresa,
        SUM(qtd_documentos)                        AS qtd_documentos,
        codigo_uf_emitente,
        MAX(nome_uf_emitente)                      AS nome_uf_emitente,
        codigo_cidade_emitente,
        MAX(nome_cidade_emitente)                  AS nome_cidade_emitente,
        codigo_uf_destinatario,
        MAX(nome_uf_destinatario)                  AS nome_uf_destinatario,
        codigo_cidade_destinatario,
        MAX(nome_cidade_destinatario)              AS nome_cidade_destinatario,
        SUM(qtd_produtos)                          AS qtd_produtos,
        SUM(valor_total_frete)                     AS valor_total_frete,
        SUM(valor_total_nfe)                       AS valor_total_nfe,
        _ano                                       AS ano,
        _mes                                       AS mes
    FROM
        dataset_nfe_transportadoras_diario
    WHERE
        ano     = _ano
        AND mes = _mes
    GROUP BY
        cnpj,
        codigo_uf_emitente,
        codigo_cidade_emitente,
        codigo_uf_destinatario,
        codigo_cidade_destinatario
)
INSERT INTO dataset_nfe_transportadoras_mensal(
    cnpj,
    nome_empresa,
    codigo_uf_emitente,
    nome_uf_emitente,
    codigo_cidade_emitente,
    nome_cidade_emitente,
    codigo_uf_destinatario,
    nome_uf_destinatario,
    codigo_cidade_destinatario,
    nome_cidade_destinatario,
    qtd_documentos,
    qtd_produtos,
    valor_total_nfe,
    valor_total_frete,
    mes,
    ano
)
SELECT * FROM origem; -- 补充从CTE取数的查询语句

如果你的MySQL版本低于8.0.19，可以用兼容性更好的写法，把WITH放在INSERT之后的SELECT前面：

INSERT INTO dataset_nfe_transportadoras_mensal(
    cnpj,
    nome_empresa,
    codigo_uf_emitente,
    nome_uf_emitente,
    codigo_cidade_emitente,
    nome_cidade_emitente,
    codigo_uf_destinatario,
    nome_uf_destinatario,
    codigo_cidade_destinatario,
    nome_cidade_destinatario,
    qtd_documentos,
    qtd_produtos,
    valor_total_nfe,
    valor_total_frete,
    mes,
    ano
)
WITH origem AS (
    SELECT
        cnpj,
        MAX(nome_empresa)                          AS nome_empresa,
        SUM(qtd_documentos)                        AS qtd_documentos,
        codigo_uf_emitente,
        MAX(nome_uf_emitente)                      AS nome_uf_emitente,
        codigo_cidade_emitente,
        MAX(nome_cidade_emitente)                  AS nome_cidade_emitente,
        codigo_uf_destinatario,
        MAX(nome_uf_destinatario)                  AS nome_uf_destinatario,
        codigo_cidade_destinatario,
        MAX(nome_cidade_destinatario)              AS nome_cidade_destinatario,
        SUM(qtd_produtos)                          AS qtd_produtos,
        SUM(valor_total_frete)                     AS valor_total_frete,
        SUM(valor_total_nfe)                       AS valor_total_nfe,
        _ano                                       AS ano,
        _mes                                       AS mes
    FROM
        dataset_nfe_transportadoras_diario
    WHERE
        ano     = _ano
        AND mes = _mes
    GROUP BY
        cnpj,
        codigo_uf_emitente,
        codigo_cidade_emitente,
        codigo_uf_destinatario,
        codigo_cidade_destinatario
)
SELECT * FROM origem;

额外注意事项 #

• 若担心CTE返回字段顺序和INSERT字段顺序不匹配，可以把SELECT * FROM origem替换为显式指定字段的写法，比如SELECT cnpj, nome_empresa, codigo_uf_emitente, nome_uf_emitente, codigo_cidade_emitente, nome_cidade_emitente, codigo_uf_destinatario, nome_uf_destinatario, codigo_cidade_destinatario, nome_cidade_destinatario, qtd_documentos, qtd_produtos, valor_total_nfe, valor_total_frete, mes, ano FROM origem，完全避免字段错位问题。
• 如果运行时提示WITH关键字不识别，直接升级MySQL到8.0及以上版本即可。

内容的提问来源于stack exchange，提问作者felipe sales mendes