使用mysql2的LIKE查询时出现mysqld_stmt_execute参数错误

我用mysql2在MySQL表中通过LIKE语句检索数据，预编译语句的参数数量和查询字符串里的占位符匹配，但控制台还是报"Incorrect arguments to mysqld_stmt_execute"错误。

代码示例 #

import mysql from "mysql2";

export const searchMovies = (phrase: string, limit: number): Promise<Movie[]> => {
    return new Promise((resolve, reject) => {
        const pattern = `%${phrase.replace("!", "!!").replace("%", "!%").replace("_", "!_")}%`;
        const sql = "SELECT * FROM movies WHERE `Name` LIKE ? LIMIT ?";
        const params = [pattern, limit];
        db.execute(sql, params, (err, rows: RowDataPacket[]) => {
            if (err) reject(err);
            if (rows == undefined) return [];
            resolve(rows.map(x => ({
                id: x.ID,
                name: x.Name,
                language: x.Language,
                year: x.Year
            })));
        });
    });
};

控制台错误信息 #

/home/node/app/node_modules/mysql2/lib/packets/packet.js:728
    const err = new Error(message);       
                ^

Error: Incorrect arguments to mysqld_stmt_execute
    at Packet.asError (/home/node/app/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Execute.execute (/home/node/app/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/home/node/app/node_modules/mysql2/lib/connection.js:478:34)
    at PacketParser.onPacket (/home/node/app/node_modules/mysql2/lib/connection.js:97:12)
    at PacketParser.executeStart (/home/node/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/home/node/app/node_modules/mysql2/lib/connection.js:104:25)
    at Socket.emit (node:events:514:28)   
    at addChunk (node:internal/streams/readable:376:12)
    at readableAddChunk (node:internal/streams/readable:349:9)
    at Readable.push (node:internal/streams/readable:286:10) {
  code: 'ER_WRONG_ARGUMENTS',
  errno: 1210,
  sqlState: 'HY000',
  sqlMessage: 'Incorrect arguments to mysqld_stmt_execute',
  sql: 'SELECT * FROM movies WHERE `Name` LIKE ? LIMIT ?'
}

这个错误的核心原因是MySQL原生预编译语句不支持将占位符?用于LIMIT子句的参数，mysql2驱动处理这类参数时会触发类型不匹配的错误。

解决方法1：直接拼接LIMIT参数（安全方式） #

确保limit是数字类型后，直接拼入SQL语句（因为limit是数字，不存在SQL注入风险），修改后的代码：

import mysql from "mysql2";

export const searchMovies = (phrase: string, limit: number): Promise<Movie[]> => {
    return new Promise((resolve, reject) => {
        const pattern = `%${phrase.replace("!", "!!").replace("%", "!%").replace("_", "!_")}%`;
        // 先将limit转为数字再拼接，避免非数字输入
        const sql = `SELECT * FROM movies WHERE \`Name\` LIKE ? LIMIT ${Number(limit)}`;
        const params = [pattern];
        db.execute(sql, params, (err, rows: RowDataPacket[]) => {
            if (err) reject(err);
            // 处理rows为空的情况，直接resolve空数组
            resolve(rows ? rows.map(x => ({
                id: x.ID,
                name: x.Name,
                language: x.Language,
                year: x.Year
            })) : []);
        });
    });
};

补充修复：原代码的Promise逻辑问题 #

原代码中if (rows == undefined) return [];这行直接return不会终止Promise，会导致Promise一直处于pending状态，改成上述代码中的resolve(rows ? ... : [])即可解决。

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