mysql json欄位

從 MySQL 5.7.8 開始，MySQL 支持原生的 JSON 數據類型。

mysql> CREATE TABLE lnmp (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `category` JSON,
    `tags` JSON,
    PRIMARY KEY (`id`)
);

mysql> DESC lnmp;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| category | json             | YES  |     | NULL    |                |
| tags     | json             | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

INSERT INTO `lnmp` (category, tags) VALUES ('{"id": 1, "name": "lnmp.cn"}', '[1, 2, 3]');
INSERT INTO `lnmp` (category, tags) VALUES (JSON_OBJECT("id", 2, "name", "php.net"),JSON_ARRAY(1, 3, 5));

查看插入的數據

mysql> SELECT * FROM lnmp;
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
|  2 | {"id": 2, "name": "php.net"} | [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                | "lnmp.cn"          | 1            | 3            |
|  2 | 2                | "php.net"          | 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 | "lnmp.cn"          | lnmp.cn                          | lnmp.cn             |
|  2 | "php.net"          | php.net                          | php.net             |
+----+--------------------+----------------------------------+---------------------+
2 rows in set (0.00 sec)

下麵說下 JSON 作為條件進行搜索。

雖然插入的時候可以用字元串的形式插入,但是實際上json欄位的存儲方式並不等同於字元串,查詢的時候直接用字元串相等是不可以的.

可以通過cast方法將字元串轉為json

mysql> SELECT * FROM lnmp WHERE category = CAST('{"id": 1, "name": "lnmp.cn"}' as JSON);
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)

通過 JSON 中的元素進行查詢, 對象型的查詢同樣可以通過 column->path

mysql> SELECT * FROM lnmp WHERE category->'$.name' = 'lnmp.cn';
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [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": "lnmp.cn"} | [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', 'www.lnmp.cn') WHERE id = 1;
 UPDATE lnmp SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;
 UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') 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 import JSON
from sqlalchemy import func
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值必須為字元串.