MySQL的Json类型个人用法详解
最新回答
割了动脉喝脉动~
2025-02-25 17:10:42
从goods表的price字段里取出price键的值，使用json_extract函数，如下示例：select json_extract(price, "$.price") as de from goods where id = 159540
查询字符串类型的数字键
取字符串类型的数字键名为sku的"45453"键algorithm的值，使用json_extract函数如下：select json_extract(price, "$.sku.\"45453\".algorithm") as de from price where id = 159540
查询数组类的Json指定值
数组类的取值通过指定索引，示例如下：select JSON_EXTRACT(`crumbs`, $[1]) as one_crumbs from comment where id = 4565。根据json的某值作为查询条件：select * from comment where JSON_EXTRACT(`crumbs` ,'$[1]') = 256
查询Json里是否包含某个值
使用json_contains函数查询包含指定值：select * from goods_item where goods_id=10263 and JSON_CONTAINS(item_value->'$', concat(43318,'')) 或者 select * from goods_item where goods_id=10263 and JSON_CONTAINS(item_value, concat(43318,'')) 或者 select * from goods_item where goods_id=10263 and JSON_CONTAINS(item_value, concat(43318,''),'$');
查询Json长度
查询goods_img字段的长度，通过长度作为SQL的查询条件：select id, stock_no, goods_img from goods_item where state = 1 and JSON_LENGTH(goods_img) < 3
更新Json字段下指定键的值
更新price表中id为171314的记录，修改price字段下指定键值为300：update price set price = json_set(price, "$.attr.\"1280\".price_old", 300) where id in (171314)
热门标签
