如何使用Prisma原生查询批量插入多字段整数数据？

表包含两个整数类型列，需通过Prisma原生查询批量插入多行数据。单行插入可正常运行，代码如下：

const testArr = [1,3]
return await this.prisma.$executeRaw`
    INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES (${Prisma.join(testArr)})
    ON CONFLICT DO NOTHING
;`

但尝试批量插入时出现语法错误，错误日志如下：

// Exception:
INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES $1
    ON CONFLICT DO NOTHING
  ; ["(1,2),(2,2)"]
Duration: 0ms
[Nest] 1226  - 10/08/2022, 11:41:45 AM   ERROR [ExceptionsHandler] 
Invalid `prisma.$executeRaw()` invocation:
    
Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near "$1"`

已尝试以下两种写法，均失败：

写法一 #

const testArr = ['(1,2)', '(2,2)']
return await this.prisma.$executeRaw`
    INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES (${Prisma.join(testArr)})
    ON CONFLICT DO NOTHING
;`

写法二 #

const testArr = ['(1,2)', '(2,2)']
await this.prisma.$executeRaw`
    INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES ${testArr.join(',')}
    ON CONFLICT DO NOTHING
;`

解决方法 #

问题根源是之前的写法将整行值作为字符串传入，Prisma会把它当作单个字符串参数，导致SQL语法错误。正确做法是将每行的整数拆分为单独参数，让Prisma生成正确的参数化查询：

方案1：使用Prisma.join处理每行参数 #

// 每行数据以整数数组形式存储
const rows = [[1, 2], [2, 2]];
// 为每行生成带参数占位符的字符串，再拼接所有行
const valueRows = rows.map(row => `(${Prisma.join(row)})`);

return await this.prisma.$executeRaw`
    INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES ${Prisma.join(valueRows)}
    ON CONFLICT DO NOTHING
;`

方案2：使用$executeRawUnsafe（需注意参数安全） #

如果需要更灵活的控制，可使用$executeRawUnsafe，但要确保参数已正确处理避免SQL注入：

const rows = [[1, 2], [2, 2]];
// 生成每行的占位符模板
const placeholders = rows.map(() => '($,$)').join(',');
// 扁平化参数数组
const params = rows.flat();

return await this.prisma.$executeRawUnsafe(
    `INSERT INTO public."_CategoryToItem" ("B", "A") VALUES ${placeholders} ON CONFLICT DO NOTHING;`,
    ...params
);

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