mysql5.7及之后的版本支持json列形式存储数据
建表
CREATE TABLE `test` ( `id` varchar(50) primary key NOT NULL, `jsondata` json NOT NULL, `category` char(4) GENERATED ALWAYS AS (json_extract(`jsondata`,'$.category')) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
添加json列映射出的字段
alter table test add column name varchar(50) generated always as (json_unquote(json_extract(`jsondata`,'$.name'))) virtual;
查询json字段中指定内容
select `jsondata`->'$.name' from test where `jsondata`->'$.name' = 'li';
修改json字段中指定内容
update test set `jsondata` = json_set(`jsondata`,'$.name','li')