查看插入的数据
mysql> SELECT * FROM lnmp;
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": ""} | [1, 2, 3] |
| 2 | {"id": 2, "name": ""} | [1, 3, 5] |
+----+------------------------------+-----------+
2 rows in set (0.00 sec)
三.查询数据
查询 json 中的数据用column->path的形式,其中对象类型 path 这样表示$.path, 而数组类型则是$[index]
mysql> SELECT id, category->'$.id', category->'$.name', tags->'$[0]', tags->'$[2]' FROM lnmp;
+----+------------------+--------------------+--------------+--------------+
| id | category->'$.id' | category->'$.name' | tags->'$[0]' | tags->'$[2]' |
+----+------------------+--------------------+--------------+--------------+
| 1 | 1 | "" | 1 | 3 |
| 2 | 2 | "" | 1 | 5 |
+----+------------------+--------------------+--------------+--------------+
2 rows in set (0.00 sec)
可以看到对应字符串类型的category->'$.name'中还包含着双引号,这其实并不是想要的结果,可以用 JSON_UNQUOTE 函数将双引号去掉,从 MySQL 5.7.13 起也可以通过这个操作符->>这个和 JSON_UNQUOTE 是等价的
mysql> SELECT id, category->'$.name', JSON_UNQUOTE(category->'$.name'), category->>'$.name' FROM lnmp;
+----+--------------------+----------------------------------+---------------------+
| id | category->'$.name' | JSON_UNQUOTE(category->'$.name') | category->>'$.name' |
+----+--------------------+----------------------------------+---------------------+
| 1 | "" | | |
| 2 | "" | | |
+----+--------------------+----------------------------------+---------------------+
2 rows in set (0.00 sec)
下面说下 JSON 作为条件进行搜索。
虽然插入的时候可以用字符串的形式插入,但是实际上json字段的存储方式并不等同于字符串,查询的时候直接用字符串相等是不可以的.
可以通过cast方法将字符串转为json
mysql> SELECT * FROM lnmp WHERE category = CAST('{"id": 1, "name": ""}' as JSON);
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": ""} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)
通过 JSON 中的元素进行查询, 对象型的查询同样可以通过column->path
mysql> SELECT * FROM lnmp WHERE category->'$.name' = '';
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": ""} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)
要特别注意的是,JSON 中的元素搜索是严格区分变量类型的,比如说整型和字符串是严格区分的
除了用column->path的形式搜索,还可以用JSON_CONTAINS 函数,但和column->path的形式有点相反的是,JSON_CONTAINS 第二个参数是不接受整数的,无论 json 元素是整型还是字符串,否则会出现这个错误
mysql> SELECT * FROM lnmp WHERE JSON_CONTAINS(category, '1', '$.id');
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": ""} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.01 sec)
JSON_CONTAINS通用于字典查询和列表查询
字典:JSON_CONTAINS(column_name, value, key)
列表:JSON_CONTAINS(column, value)
四.更新数据
如果是整个 json 更新的话,和插入时类似的。
UPDATE lnmp SET tags = '[1, 3, 4]' WHERE id = 1;
更新元素:
UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', '') WHERE id = 1;UPDATE lnmp SET category = JSON_SET(category, '$.host', '', '$.url', '') WHERE id = 1;UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', '') WHERE id = 2;UPDATE lnmp SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;
JSON_INSERT: 只会追加新的值,原有的值不会被覆盖;
JSON_SET: 插入新值并可以覆盖原有的值
JSON_REPLACE: 只替换原有的值,新值忽略
JSON_REMOVE: 删除元素.
五. python应用
from sqlalchemy importJSONfrom sqlalchemy importfunc
self.db().query(TrainCourse).filter(func.json_contains(TrainCourse.teacher_id,"21")).all()
self.db().query(TrainClass).filter(func.json_contains(TrainClass.flex_data,"123", "$.HoldDept")).all() #存疑,有时候会报无法理解的错误
与sql原生语句用法类似,可以传三个参数或者两个参数,其中value值必须为字符串.