task 表中有如下数据结构
| id | data_ext |
|---|---|
| 1 | {“name”: “张三”,“gender”: 1,“age”: 20} |
提取data_ext字段
select
id,
cast(JSON_UNQUOTE(JSON_EXTRACT(data_ext, '$.name')) as char) as name,
cast(JSON_UNQUOTE(JSON_EXTRACT(data_ext, '$.gender')) as char) as gender,
cast(JSON_UNQUOTE(JSON_EXTRACT(data_ext, '$.age')) as char) as age,
from task
JSON_EXTRACT：提取字段。
JSON_UNQUOTE：去掉双引号。
cast ：转换字符显示。
参考文档
《MySQL JSON_EXTRACT()、JSON_UNQUOTE() 函数获取JSON串的值》：https://blog.csdn.net/TM_enn/article/details/125786948
