MySQL 处理 JSON

MySQL修改 json数据

-- 如下几个函数，都是对字段 columns 中的 json 进行修改，key对应的是json中的key值，value是要设置的新值
UPDATE tablename SET jsoncolumn = JSON_SET(columns, '$.key', 'value') WHERE id=1;
UPDATE tablename SET jsoncolumn = JSON_INSERT(columns, '$.key', 'value') WHERE id=1;
UPDATE tablename SET jsoncolumn = JSON_REPLACE(columns, '$.key', 'value') WHERE id=1;
-- JSON_UNQUOTE可以解析json数据中的双引号，双引号需要在单引号内
UPDATE tablename SET jsoncolumn = JSON_SET(columns, '$.key', JSON_UNQUOTE('new"value')) WHERE id=1;

MySQL 字符串转 List 集合

-- 可以将 str 当做字符串存储在字段中，然后使用以下 SQL 将查询到的字符串转为 List 集合，结果将字符串根据‘，’拆分，返回多行结果集
SET @str = '1694491969,1694500061,1694500072,1694500076,1694500172,1694500221,1694500258,1694510752,1694510843';

SELECT DISTINCT
     SUBSTRING_INDEX(SUBSTRING_INDEX(a.your_column, ',', b.help_topic_id + 1), ',',-1) AS fj_id, b.help_topic_id
FROM your_table a
    JOIN mysql.help_topic b ON b.help_topic_id < (LENGTH(a.your_column) - LENGTH(REPLACE(a.your_column, ',', '')) + 1);

MySQL 提取 json 中的数组

-- 表中字段值类型：[10, 20, 30, 40]，可以拆分为多行返回结果集
SELECT CAST(VALUE AS CHAR)
FROM json_tables,
JSON_TABLE(json_array_num, "$[*]" COLUMNS (VALUE VARCHAR(255) PATH '$')) AS extracted_values;