使用MariaDB Node.js连接器批量插入非重复数据报错排查

我尝试通过连接池执行批量查询，实现“数据存在则不插入”的逻辑，使用带?占位符的SQL语句并构造了批量参数，但执行时出现R_DUP_FIELDNAME错误，提示“Duplicate column name '?'”，疑似占位符未被解析。

相关SQL语句 #

INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
(SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1

TypeScript代码 #

const INSERT_REGION =
  'INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM\
(SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1';

const insertRegionParam = [['ITC1', 'ITA', 'EU', 'ITC1'], ['ITP1', 'ITA', 'EU', 'ITP1']];

private insertRegions$(insertRegionParam: string[][]) {
  return from(this.pool.batch(INSERT_REGION, insertRegionParam)).pipe(
    catchError((err: mdb.SqlError) => throwError(() => err))
  );
}

报错信息 #

R_DUP_FIELDNAME: Duplicate column name '?'
sql: INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
    (SELECT ?, ?, ?) AS tmp WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1
    at /code/dist/resources/regions-sectors/regions-sectors.controller.js:24:22
    at /code/node_modules/rxjs/dist/cjs/internal/operators/catchError.js:13:51
    at OperatorSubscriber._this._error (/code/node_modules/rxjs/dist/cjs/internal/operators/OperatorSubscriber.js:43:21)
    at Subscriber.error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:60:18)
    at Subscriber._error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:84:30)
    at Subscriber.error (/code/node_modules/rxjs/dist/cjs/internal/Subscriber.js:60:18)
    at Observable.init [as _subscribe] (/code/node_modules/rxjs/dist/cjs/internal/observable/throwError.js:8:58)
    at Observable._trySubscribe (/code/node_modules/rxjs/dist/cjs/internal/Observable.js:41:25)
    at /code/node_modules/rxjs/dist/cjs/internal/Observable.js:35:31
    at Object.errorContext (/code/node_modules/rxjs/dist/cjs/internal/util/errorContext.js:22:9)

问题原因 #

错误并非占位符未被解析，而是子查询(SELECT ?, ?, ?)未给选中的字段指定别名，数据库将?直接识别为列名，三个重复的?列名触发了重复列名错误。

解决方法 #

修改SQL语句，为子查询中的每个占位字段添加别名，避免数据库将占位符误判为列名：

INSERT INTO region (region_code, country_code, continent_code) SELECT * FROM
(SELECT ? AS region_code, ? AS country_code, ? AS continent_code) AS tmp 
WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?) LIMIT 1

也可以改用更简洁的写法（部分数据库支持）：

INSERT INTO region (region_code, country_code, continent_code)
SELECT ?, ?, ?
WHERE NOT EXISTS (SELECT region_code FROM region WHERE region_code = ?)

修改后，占位符会被正常解析，批量插入逻辑可正常执行。

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