向MySQL行插入数组时出现列数不匹配错误求助

问题原因 #

你遇到的Column count doesn't match value count at row 1错误，是因为直接将JavaScript数组传入MySQL的JSON字段时，数据库驱动会把数组拆分成多个独立的值，导致VALUES子句中的值数量超过了指定的列数量。比如roles是['admin', 'user']时，驱动会把它解析成两个值，原本5个占位符就对应了6个值，自然报错。

解决办法 #

方法1：插入前将数组转为JSON字符串 #

在把roles传入SQL查询前，用JSON.stringify()把数组转换成标准JSON字符串，这样驱动就会把它当作单个值处理：

export async function createUser(firstName, lastName, username, password, roles) {
    // 将数组转为JSON字符串
    const rolesJson = JSON.stringify(roles);
    const [result] = await db.query(`
       INSERT INTO users (firstName, lastName, username, password, roles)
       VALUES (? , ?, ?, ?, ?)
    `, [firstName, lastName, username, password, rolesJson])
    const id = result.insertId;

    return getUser(id);
}

方法2：在SQL中显式转换为JSON类型 #

可以在SQL语句里用CAST(? AS JSON)强制将传入的字符串转为JSON类型，同样需要先把数组转成字符串：

export async function createUser(firstName, lastName, username, password, roles) {
    const [result] = await db.query(`
       INSERT INTO users (firstName, lastName, username, password, roles)
       VALUES (? , ?, ?, ?, CAST(? AS JSON))
    `, [firstName, lastName, username, password, JSON.stringify(roles)])
    const id = result.insertId;

    return getUser(id);
}

补充：查询时转回JS数组 #

从数据库查询用户数据时，MySQL会返回JSON格式的字符串，需要用JSON.parse()把它转回JavaScript数组，比如在getUser函数中处理：

async function getUser(id) {
    const [rows] = await db.query('SELECT * FROM users WHERE id = ?', [id]);
    if (rows.length === 0) return null;
    const user = rows[0];
    // 将JSON字符串转回数组
    user.roles = JSON.parse(user.roles);
    return user;
}

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