使用mysql2查询MySQL JSON数组中匹配名称的宝可梦对象

你当前的写入逻辑存在缺陷：调用JSON_ARRAY_APPEND时传入了JSON.stringify(pokemon)生成的字符串，导致pokemons JSON数组中存储的是转义后的JSON字符串，而非结构化的嵌套JSON对象，这是直接使用JSON查询函数无法匹配到结果的根本原因。

写入时不要手动序列化宝可梦对象，直接通过参数化查询传入JS对象，mysql2会自动处理JSON类型转换，同时彻底避免SQL注入和转义错误：

await con.query(
  `UPDATE profileSchema SET pokemons = JSON_ARRAY_APPEND(pokemons, '$', ?) WHERE id = ?`,
  [pokemon, interaction.user.id]
);

如果库中已经存在历史的字符串格式数据，可以执行以下一次性SQL完成修复：

UPDATE profileSchema
SET pokemons = (
  SELECT JSON_ARRAYAGG(JSON_EXTRACT(item, '$'))
  FROM JSON_TABLE(pokemons, '$[*]' COLUMNS(item JSON PATH '$')) t
);

MySQL 8.0+ 推荐方案（直接返回匹配对象） #

使用JSON_TABLE将JSON数组拆为临时表结构，直接匹配名称返回对应的宝可梦对象，SQL如下：

SELECT p.pokemon_data
FROM profileSchema,
JSON_TABLE(
  pokemons,
  '$[*]' COLUMNS (
    pokemon_data JSON PATH '$',
    pokemon_name VARCHAR(100) PATH '$.name'
  )
) p
WHERE id = ? AND p.pokemon_name = ?
LIMIT 1;

对应的Node.js调用代码：

const [result] = await con.query(
  `SELECT p.pokemon_data
  FROM profileSchema,
  JSON_TABLE(
    pokemons,
    '$[*]' COLUMNS (
      pokemon_data JSON PATH '$',
      pokemon_name VARCHAR(100) PATH '$.name'
    )
  ) p
  WHERE id = ? AND p.pokemon_name = ?
  LIMIT 1`,
  [targetUserId, targetPokemonName]
);
// result[0].pokemon_data 即为匹配到的宝可梦对象，mysql2会自动解析为JS可直接使用的对象

如果存在同名宝可梦需要全部返回，去掉SQL末尾的LIMIT 1即可。

MySQL 5.7 兼容方案 #

如果你的MySQL版本低于8.0不支持JSON_TABLE函数，可以先查询出对应用户的整段pokemons数组，在代码层做过滤：

const [result] = await con.query(
  `SELECT pokemons FROM profileSchema WHERE id = ?`,
  [targetUserId]
);
const targetPokemon = result.length ? result[0].pokemons.find(p => p.name === targetPokemonName) : null;

• 禁止使用字符串拼接的方式生成SQL语句，必须使用?占位符的参数化查询，否则存在SQL注入风险，特殊字符也会导致语句执行报错
• JSON类型字段中要存储结构化的JSON值，不要存储序列化后的字符串，否则所有JSON查询函数都无法正常工作

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