MySQL中如何将JSON字段的字符串类型id转换为int整数类型

你的更新语句没效果是两个写法规避不了的硬错：

• 路径匹配错误：你存的JSON根节点是数组，不是普通对象，直接写$.id根本定位不到数组内部元素的id属性。单个数组元素的正确路径格式是$[数组索引].属性名，如果数组存了多个元素，写死单一路径没法覆盖所有项。
• 取值逻辑完全错误：你写的REPLACE("$.id", '"', '')是对字面量字符串"$.id"做引号替换，根本没读取到字段里实际存的id值；就算你替换对了值，直接塞回去的还是字符串类型，不会自动转成整数。

单元素数组场景（你的示例就是这种情况） #

直接用JSON函数取值、转类型、写回即可，语句如下：

UPDATE products 
SET category_ids = JSON_SET(
  category_ids,
  "$[0].id",
  CAST(JSON_UNQUOTE(JSON_EXTRACT(category_ids, "$[0].id")) AS SIGNED INT)
)
WHERE id = any_row_id;

逻辑很简单：先用JSON_EXTRACT取到数组第一个元素的id值，JSON_UNQUOTE剥掉值外层的引号，再用CAST把字符串转成整数，最后写回原路径，执行完结果就是你要的[{"id":5,"position":1}]。

执行更新前务必先SELECT校验结果，避免误改：

SELECT category_ids 原始值, JSON_SET(category_ids, "$[0].id", CAST(JSON_UNQUOTE(JSON_EXTRACT(category_ids, "$[0].id")) AS SIGNED INT)) 转换后值 FROM products WHERE id = any_row_id;

确认转换后值符合预期再跑UPDATE。

多元素数组批量转换场景 #

如果你的JSON数组里有多个对象，需要把所有对象的id都从字符串转整数，MySQL 8.0+可以用JSON_TABLE拆分数组逐行转换，再重新组装成数组写回：

UPDATE products p
INNER JOIN (
  SELECT
    id,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'id', CAST(jt.id AS SIGNED INT),
        'position', jt.position
      )
    ) AS converted_category_ids
  FROM products,
  JSON_TABLE(
    category_ids,
    '$[*]' COLUMNS(
      id VARCHAR(32) PATH '$.id',
      position INT PATH '$.position'
    )
  ) jt
  -- 要全表更新就删掉下面这行条件
  WHERE id = any_row_id
  GROUP BY id
) tmp ON p.id = tmp.id
SET p.category_ids = tmp.converted_category_ids;

如果是MySQL 5.7版本没有JSON_TABLE函数，多元素场景不建议硬写JSON函数处理，把数据查出来在业务代码里转完再批量更新更稳妥，不容易出脏数据。

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