MySQL 8插入JSON列报错ER_INVALID_JSON_CHARSET求助

上周六因AWS弃用MySQL 5.7，我们将数据库升级至MySQL 8。初期运行正常，但目前系统出现异常：使用Node.js + Express + Sequelize框架，前端通过POST请求发送数据，调用.create方法向包含JSON列的表插入数据时，触发以下报错：

original: Error: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
    at Packet.asError (/Users/test/code/root/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Execute.execute (/Users/test/code/root/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/Users/test/code/root/node_modules/mysql2/lib/connection.js:481:34)
    at PacketParser.onPacket (/Users/test/code/root/node_modules/mysql2/lib/connection.js:97:12)
    at PacketParser.executeStart (/Users/test/code/root/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/Users/test/code/root/node_modules/mysql2/lib/connection.js:104:25)
    at Socket.emit (node:events:517:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  code: 'ER_INVALID_JSON_CHARSET',
  errno: 3144,
  sqlState: '22032',
  sqlMessage: "Cannot create a JSON value from a string with CHARACTER SET 'binary'.",
  sql: 'INSERT INTO `guest_item` (`id`,`parentId`,`parentBookItemId`,`order`,`itemType`,`itemId`,`itemData`,`createdBy`,`updatedBy`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?);',
  parameters: [
    2002,
    null,
    0,
    'contact',
    '5',
    '{"id":1,"name":"JANE DOE","email":"email@example.com","jobTitle":"Director of Sales","primaryPhoneNumber":"1112223333","image":null,"propertyName":""}',
    3,
    3,
    '2024-02-28 10:11:02',
    '2024-02-28 10:11:02'
  ]
}

相关代码与表结构 #

Sequelize模型定义（JSON列部分） #

itemData: {
  type: DataTypes.JSON,
  allowNull: true
}

插入数据的业务代码 #

await GuestItem.create({
  // ...其他字段
  itemData: chapterItem.itemData, // chapterItem.itemData为JS对象
  // ...其他字段
});

直接执行对应SQL语句可成功插入数据，但通过Sequelize调用则失败。已尝试以下方案均无效：

• 设置连接字符集
• 确保POST请求使用Content-Type: application/json且编码为utf8
• 使用JSON.stringify和JSON.parse处理JSON数据
• 通过脚本移除非utf8字符

目前仅可行方案是将JSON列改为TEXT类型并使用存取器，但担心影响其他依赖JSON函数的列，需明确报错原因与有效解决方案。

报错原因 #

MySQL 8对JSON列的字符集校验比5.7更严格。Sequelize在处理JSON类型字段时，可能将对象序列化为字符串后，以binary字符集传递给MySQL，而MySQL 8不允许从binary字符集的字符串创建JSON值。

有效解决方案 #

1. 明确Sequelize连接的字符集配置
   在Sequelize连接初始化时，指定charset为utf8mb4、collate为utf8mb4_general_ci，确保所有字符串参数以正确字符集传递：

   const sequelize = new Sequelize('database', 'username', 'password', {
     host: 'localhost',
     dialect: 'mysql',
     charset: 'utf8mb4',
     collate: 'utf8mb4_general_ci',
     // ...其他配置项
   });
   

2. 强制JSON字段以对象形式传递
   确保传入create方法的itemData是标准JS对象，而非字符串。若存在字符串化情况，手动转换：

   await GuestItem.create({
     // ...其他字段
     itemData: typeof chapterItem.itemData === 'string' 
       ? JSON.parse(chapterItem.itemData) 
       : chapterItem.itemData,
     // ...其他字段
   });
   

3. 升级mysql2驱动版本
   旧版mysql2驱动可能存在与MySQL 8兼容的字符集处理问题，执行命令更新至最新稳定版：

   npm update mysql2
   

4. 临时修改表列字符集（应急方案）
   若上述方法无效，可临时修改JSON列的字符集为utf8mb4（MySQL JSON列本身无需字符集，但此操作可强制接受对应字符集的字符串）：

   ALTER TABLE guest_item MODIFY itemData JSON CHARACTER SET utf8mb4;
   

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