TypeORM queryRunner.query执行多SQL语句报错如何修复

AI 小北

2026-8-29

问题原因

报错由两个语法/逻辑问题直接导致：

CTE语法违规：PostgreSQL中WITH引导的公共表表达式必须依附于单条后续主语句存在，属于同一个SQL语句块。你在inserted_id CTE的闭合括号后直接书写分号，提前终止了语句，数据库解析时发现WITH块没有对应的执行主体，直接抛出syntax error at or near ";"错误。
CTE作用域越界：即使删除上述位置的分号，代码依然无法正常运行。WITH生成的临时结果集仅对紧邻它的单条语句生效，原代码WITH后跟随5条独立INSERT语句，除第一条metadata插入外，剩余4条标签关联插入都无法访问inserted_id临时结果，会触发二次报错。

修复方案

优先选择拆分查询的写法，逻辑清晰易调试，完全规避CTE的语法和作用域限制，适配TypeORM迁移的执行逻辑，同时支持参数化查询、合并批量操作提升效率：

先单独执行collection表的插入语句，拿到返回的主键ID
将拿到的ID作为参数传入后续所有关联插入语句，合并多条同表插入为批量操作减少SQL执行次数

修复后的代码如下：

// 1. 插入主集合数据，获取生成的主键ID
const insertResult = await queryRunner.query(
  `INSERT INTO "public"."collection" ("created_by", "updated_by")
   VALUES ('system') RETURNING id`
);
const collectionId = insertResult[0].id;

// 2. 插入关联元数据
await queryRunner.query(
  `INSERT INTO "public"."metadata" ("name",
                                    "slug",
                                    "description",
                                    "genre_id",
                                    "collection_id",
                                    "splash_src_url",
                                    "logo_src_url",
                                    "video_src_url",
                                    "website_url",
                                    "discord_url",
                                    "telegram_url",
                                    "twitter_url",
                                    "created_by",
                                    "updated_by")
   VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)`,
  [
    'Githubverse',
    'Githubverse',
    'Githubverse is a game based on Github World, the popular anime franchise with over 60+ million fans.',
    3,
    collectionId,
    'https://media.disgaming.com/githubverse/splash.jpg',
    'https://media.disgaming.com/githubverse/logo.png',
    'https://media.disgaming.com/githubverse/video.mp4',
    'https://githubverse.io/',
    'https://discord.gg/githubverse',
    'https://t.me/githubverse',
    'https://twitter.com/githubverse',
    'system',
    'system'
  ]
);

// 3. 批量插入标签关联关系
await queryRunner.query(
  `INSERT INTO "public"."collection_tag_mapping" ("collection_id", "tag_id")
   VALUES ($1, 3), ($1, 4), ($1, 10), ($1, 19)`,
  [collectionId]
);

如果需要使用单条SQL+CTE的写法，必须把所有插入逻辑整合到同一个WITH语句链中，保证所有操作在单条语句内执行，WITH块定义结束后绝对不能加分号：

WITH inserted_id AS (
  INSERT INTO "public"."collection" ("created_by", "updated_by")
  VALUES ('system') RETURNING id
),
insert_metadata AS (
  INSERT INTO "public"."metadata" ("name",
                                    "slug",
                                    "description",
                                    "genre_id",
                                    "collection_id",
                                    "splash_src_url",
                                    "logo_src_url",
                                    "video_src_url",
                                    "website_url",
                                    "discord_url",
                                    "telegram_url",
                                    "twitter_url",
                                    "created_by",
                                    "updated_by")
  VALUES ('Githubverse',
          'Githubverse',
          'Githubverse is a game based on Github World, the popular anime franchise with over 60+ million fans.',
          3,
          (SELECT id FROM inserted_id),
          'https://media.disgaming.com/githubverse/splash.jpg',
          'https://media.disgaming.com/githubverse/logo.png',
          'https://media.disgaming.com/githubverse/video.mp4',
          'https://githubverse.io/',
          'https://discord.gg/githubverse',
          'https://t.me/githubverse',
          'https://twitter.com/githubverse',
          'system',
          'system')
  RETURNING 1
),
insert_tag1 AS (
  INSERT INTO "public"."collection_tag_mapping" ("collection_id", "tag_id")
  VALUES ((SELECT id FROM inserted_id), 3) RETURNING 1
),
insert_tag2 AS (
  INSERT INTO "public"."collection_tag_mapping" ("collection_id", "tag_id")
  VALUES ((SELECT id FROM inserted_id), 4) RETURNING 1
),
insert_tag3 AS (
  INSERT INTO "public"."collection_tag_mapping" ("collection_id", "tag_id")
  VALUES ((SELECT id FROM inserted_id), 10) RETURNING 1
)
INSERT INTO "public"."collection_tag_mapping" ("collection_id", "tag_id")
VALUES ((SELECT id FROM inserted_id), 19);

该写法调试和维护成本更高，迁移脚本场景下不推荐使用。

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