如何使用JSON_REPLACE和JSON_ARRAY修改MySQL JSON字段的数组类型键值

最优方案：JSON序列化+参数化查询（推荐） #

这个方案适配所有数组长度，同时规避SQL注入风险，是最稳妥的实现方式：

const religionPreferences = ["Buddhism","Christianity","Non religious"]
// 先将JS数组序列化为标准JSON字符串
const religionsJson = JSON.stringify(religionPreferences)
// 使用参数化查询传入参数，不要直接拼接变量到SQL语句中
// 以下为node-mysql2驱动的写法，其他驱动参数化语法可对应调整
const [updateResult] = await connection.execute(
  `UPDATE users 
   SET preferences = JSON_REPLACE(preferences, '$.religions', CAST(? AS JSON)) 
   WHERE id = ? 
   LIMIT 1`,
  [religionsJson, req.params.id]
)

原理说明：JSON.stringify输出的是标准JSON数组格式，通过CAST(? AS JSON)显式转为MySQL的JSON类型后存入字段，不会被识别为普通字符串，0-14个元素的场景都可以直接适配。

备选方案：动态拼接JSON_ARRAY参数 #

如果特殊场景不能用参数化查询，可以动态生成JSON_ARRAY的入参：

const religionPreferences = ["Buddhism","Christianity","Non religious"]
// 对每个数组元素做SQL转义，避免注入风险，示例用node-mysql2的escape方法
const escapedItems = religionPreferences.map(item => connection.escape(item))
// 动态拼接JSON_ARRAY的参数列表
const jsonArrayPart = `JSON_ARRAY(${escapedItems.join(',')})`
// 拼接完整SQL
const sql = `UPDATE users 
SET preferences = JSON_REPLACE(preferences, '$.religions', ${jsonArrayPart})
WHERE id = ${connection.escape(req.params.id)} 
LIMIT 1`

原理说明：动态根据数组长度生成对应数量的转义后参数传入JSON_ARRAY，即使是空数组也会生成JSON_ARRAY()的合法语法。

原写法报错原因 #

直接把JS数组插入模板字符串时，JS会自动调用数组的toString()方法，将["a","b"]转换为a,b的格式，SQL无法识别该格式为数组，因此会出现语法错误或者存入非预期的字符串格式。

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