玩转mysql的json类型

如果用户增加删除一个字段，json更加灵活，不需要修改表结构，只存业务字段。

 {
     "name":"翟",
     "age":"26",
     "phone": ["13920597981","15122511111"]
     "city": {
         "code":"999",
         "name":"天津"
     }
 }

SELECT JSON_UNQUOTE(JSON_EXTRACT(user_json,'$.name')) FROM table;

SELECT user_json -> '$.name' FROM table;

SELECT user_json ->> '$.name' FROM table;

SELECT user_json -> '$.phone[0]' FROM table;

SELECT user_json -> '$.city.code' FROM table;

[
    {
        "name":"呼呼",
    },{
        "name":"哈哈",
    }
]

SELECT user_json -> '$[0].name' FROM table;

SELECT * FROM table WHERE demo_json -> '$.name' LIKE "%翟%" ;

SELECT * FROM table WHERE demo_json -> '$.name' = "翟胖胖" ;

SELECT * FROM table WHERE demo_json -> '$.name' = "翟胖胖"  AND demo_json -> '$.age' = "26"

SELECT JSON_CONTAINS(user_json,'"26"','$.age') FROM table

update demo set demo_json = json_set(demo_json,"$.age",100) where demo_json -> '$.age' = 10

 update demo set demo_json = json_set(demo_json,"$.phone",JSON_ARRAY(13920597980,15822688571))