增

删

改

# 更新存在的字段
update t_members set channel_extend = json_set(channel_extend,"$.path","pages/index/index") where id = 5447;

# 更新不存在的字段，是添加这个键值对
update t_members set channel_extend = json_set(channel_extend,"$.time","202008081225") where id = 5447;

# 更新 json 里面还有 json 的
update t_members set channel_extend = json_set(channel_extend,"$.params.channel","CVD") where id = 5447;

查

• json字段名->'$.json属性' 进行查询

单条件查询

# 查询 json 字段（channel_extend）中 scene = 1017 的
SELECT id,channel_extend FROM  t_members WHERE  channel_extend->'$.scene'=1017;

多条件查询

SELECT id,channel_extend FROM  t_members WHERE  channel_extend->'$.scene'=1017 AND  channel_extend->'$.path'='pages/memberCenter/pages/screen/index'

关联表查询