如何让Knex/MikroORM将SQLite批量插入编译为现代VALUES语法而非UNION ALL？

嘿，我之前也碰到过这个头疼的问题——SQLite早就支持标准的多行INSERT了，但Knex和MikroORM默认居然还在用老的UNION ALL写法，刚好撞在SQLITE_MAX_COMPOUND_SELECT的500条限制上。结合官方文档和实际调试，给你整理了具体的解决办法：

先理清楚背景 #

SQLite从3.7.11（2012年3月）开始就支持标准批量插入语法了：

INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ...;

但更早的版本只能用UNION ALL的 workaround：

INSERT INTO 'tablename' SELECT 'data1' AS 'column1', 'data2' AS 'column2' UNION ALL SELECT ...;

默认情况下SQLite的SQLITE_MAX_COMPOUND_SELECT上限是500，所以用老语法插超过500条就会报错。咱们的目标就是让ORM切换到新语法。

MikroORM 解决办法 #

1. 开启批量插入配置（推荐） #

MikroORM v5及以上版本已经支持SQLite的原生批量插入，但需要手动开启这个配置。你可以在ORM的配置文件里加一行：

// mikro-orm.config.ts
export default {
  // 你的其他配置（实体、数据库连接等）
  driverOptions: {
    sqlite: {
      useBatchInserts: true, // 关键配置：启用原生批量插入
    },
  },
};

然后修改你的插入代码，改用values()方法传递数据数组：

const q = em.createQueryBuilder(MyModel);
q.insert().values([{ data1, data2 }, ...]); // 这里用values()包裹数组
await q.execute('run', false);

这样MikroORM就会生成标准的VALUES多行插入语句，不再触发500条的限制。

2. 原生SQL兜底（如果配置不生效） #

要是配置后还是不行，或者你的MikroORM版本比较旧，可以用参数化的原生SQL来插，还能避免SQL注入：

const data = [{ data1, data2 }, ...];
// 生成占位符和参数数组
const placeholders = data.map(() => '(?, ?)').join(',');
const params = data.flatMap(item => [item.data1, item.data2]);

await em.execute(
  `INSERT INTO tablename (column1, column2) VALUES ${placeholders}`,
  params
);

Knex 解决办法 #

1. 强制启用原生插入 #

Knex默认对SQLite可能会用UNION ALL，但你可以在配置里强制开启原生批量插入：

// knexfile.js
module.exports = {
  client: 'sqlite3',
  connection: {
    filename: './your-db.sqlite',
  },
  useNullAsDefault: true,
  options: {
    useNativeInsert: true, // 开启原生批量插入支持
  },
};

然后正常调用insert()方法就行：

await knex('tablename').insert([{ data1, data2 }, ...]);

如果还是不行，试试在insert里加第二个参数指定batchInsert：

await knex('tablename').insert([{ data1, data2 }, ...], { batchInsert: true });

2. 手动分批次插入（备选） #

要是配置调整没效果，也可以手动把数据分成每500条一批插入，虽然麻烦但稳：

const batchSize = 500;
const data = [{ data1, data2 }, ...];

for (let i = 0; i < data.length; i += batchSize) {
  const batch = data.slice(i, i + batchSize);
  await knex('tablename').insert(batch);
}

不推荐的临时方案：修改SQLite限制 #

如果你只是临时测试，不想改代码，可以临时调高SQLITE_MAX_COMPOUND_SELECT的上限，但这个是会话级别的，重启连接就会复原，而且生产环境不建议这么做：

PRAGMA sql_max_compound_select = 1000; -- 改成你需要的数值

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