MySQL中如何用SELECT结果作为值通过JSON_INSERT插入JSON路径？

问题分析 #

你需要在custom_fields JSON字段中插入$.sorting路径，值为同字段$.product_attr4转换为整数的结果，但原操作存在两个核心问题：

1. 子查询逻辑错误：原UPDATE语句中使用SELECT ... FROM product_translation会返回全表结果，而非当前行的product_attr4值，导致语法与逻辑错误。
2. 字符串转整数的警告/错误：product_attr4的值如'15b'包含非数字字符，直接CAST会触发Truncated incorrect INTEGER value警告（1292错误），虽能得到部分结果，但不符合严格执行要求。

解决方案 #

1. 修正UPDATE语句的基本写法（解决子查询错误） #

无需使用子查询，直接引用当前行的custom_fields字段处理，结合JSON_UNQUOTE（或JSON_VALUE）提取字符串后转换：

UPDATE product_translation
SET custom_fields = JSON_INSERT(
    custom_fields,
    '$.sorting',
    CAST(JSON_UNQUOTE(JSON_EXTRACT(custom_fields, '$.product_attr4')) AS SIGNED)
)
WHERE JSON_CONTAINS_PATH(custom_fields, 'one', '$.product_attr4');

或使用更简洁的JSON_VALUE（MySQL 8.0+支持）：

UPDATE product_translation
SET custom_fields = JSON_INSERT(
    custom_fields,
    '$.sorting',
    CAST(JSON_VALUE(custom_fields, '$.product_attr4') AS SIGNED)
)
WHERE JSON_CONTAINS_PATH(custom_fields, 'one', '$.product_attr4');

2. 消除字符串转整数的警告（处理'15b'这类非纯数字值） #

若要彻底避免截断警告，可先提取product_attr4中的数字部分，再转换为整数。使用REGEXP_SUBSTR匹配开头的连续数字序列：

UPDATE product_translation
SET custom_fields = JSON_INSERT(
    custom_fields,
    '$.sorting',
    CAST(
        REGEXP_SUBSTR(JSON_VALUE(custom_fields, '$.product_attr4'), '^[0-9]+')
        AS SIGNED
    )
)
WHERE JSON_CONTAINS_PATH(custom_fields, 'one', '$.product_attr4')
  AND REGEXP_SUBSTR(JSON_VALUE(custom_fields, '$.product_attr4'), '^[0-9]+') IS NOT NULL;

该语句会从'15b'这类值中提取出开头的纯数字'15'再转换，完全避免截断警告。

3. 验证转换逻辑 #

可先执行查询验证转换结果是否符合预期：

SELECT
    JSON_VALUE(custom_fields, '$.product_attr4') AS attr4,
    CAST(REGEXP_SUBSTR(JSON_VALUE(custom_fields, '$.product_attr4'), '^[0-9]+') AS SIGNED) AS attr4int
FROM product_translation
WHERE JSON_SEARCH(custom_fields, 'one', '15b');

关键说明 #

• JSON_UNQUOTE的作用：JSON_EXTRACT返回带引号的JSON字符串（如"15b"），必须去除引号后才能正确转换为整数；JSON_VALUE直接返回无引号的字符串，用法更简洁。
• 为何migration_AQ-SW5_product_attr4转换无警告：该字段的值本身是纯数字类型的JSON值（而非字符串类型），因此CAST时不会触发截断警告。

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