字段 | 解释 |
---|---|
id | 主键 |
name | 姓名 |
age | 年龄 |
address | 地址 |
phone | 电话 |
isDel | 是否删除 |
字段 | 解释 |
---|---|
id | 主键 |
user_json | 用户json {“name”:“翟”,“age”:“26”…} |
isDel | 是否删除 |
如果用户增加删除一个字段,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))