mysql支援原生json使用說明

轉載自（我的小碗湯）

MySQL在5.7.8開始對json原生支援，本文將對MySQL中json型別的用法簡單說明，希望對你有用。

CREATE TABLE testproject (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`skill` JSON NOT NULL,

`student` JSON NOT NULL,

PRIMARY KEY (`id`)

);

檢視錶結構：

image

這樣JSON的欄位就被建立好了

注：JSON型別不能有預設值。

插入JSON

插入 json 格式的字串，可以是物件的形式，也可以是陣列的形式，

INSERT INTO `testproject` (student, skill) VALUES ('{"id": 1, "name": "ggjg"}', '["java", "go", "vue"]');

INSERT INTO `testproject` (student, skill) VALUES ('{"id": 5, "name": "guogege"}', '[]');

插入json時，資料庫會對json做校驗，不符合json規範就會報錯。

image

查詢JSON：

查詢 json 中的資料用 column->path 的形式，其中物件型別 path 這樣表示 .path, 而陣列型別則是[index]

查詢testproject表student欄位中json物件id為1的記錄：

SELECT * FROM testproject WHERE student->'$.id'= 1;

image

查詢testproject表student欄位中json物件id為1或者5的記錄：

SELECT * FROM testproject WHERE student->'$.id' in (1,5);

SELECT * FROM testproject WHERE student->'$.id' = 1 or student->'$.id' = 5;

image

也可以用函式json_extract：

image

column->path方法有限制，資料來源必須是表欄位，否則就報錯：

image

以下這樣查詢，查出來student->'$.name'包含雙引號：

SELECT id, student->'$.id', student->'$.name', skill->'$[0]', skill->'$[2]' FROM testproject;

image

這不是我們想要的，可以用 JSON_UNQUOTE 函式將雙引號去掉，從 MySQL 5.7.13 起也可以通過這個操作符 ->> 這個和 JSON_UNQUOTE 是等價的。

image

因為 JSON 不同於字串，所以如果用字串和 JSON 欄位比較，是不會相等的：

mysql> SELECT * FROM testproject WHERE student = '{"id": 1, "name": "ggjg"}';

Empty set (0.00 sec)

此時可以通過 CAST 將字串轉成 JSON 的形式：

mysql> SELECT * FROM testproject WHERE student = CAST('{"id": 1, "name": "ggjg"}' as JSON);

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.01 sec)

要特別注意的是，JSON 中的元素搜尋是嚴格區分變數型別的，比如說整型和字串是嚴格區分的：

mysql> SELECT * FROM testproject WHERE student->'$.id' = '1';

Empty set (0.00 sec)

mysql>

mysql> SELECT * FROM testproject WHERE student->'$.id' = 1;

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.00 sec)

可以看到搜尋字串 1 和整型 1 的結果是不一樣的。

除了用以上 column->path 的形式搜尋，還可以用JSON_CONTAINS 函式，但和 column->path 的形式有點相反的是，JSON_CONTAINS 第二個引數是不接受整數的，無論 json 元素是整型還是字串，否則會出現這個錯誤：

mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(student, 1, '$.id');

ERROR 3146 (22032): Invalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required.

mysql>

這裡必須要使用字串：

mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(student, '1', '$.id');

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.00 sec)

對於陣列型別的 JSON 的查詢，比如說 skill 中包含有 3 的資料，同樣要用 JSON_CONTAINS 函式，同樣第二個引數也需要是字串：

mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(skill, '"go"');

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.00 sec)

mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(skill, '1');

+----+-----------+------------------------------+

| id | skill | student |

+----+-----------+------------------------------+

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+-----------+------------------------------+

1 row in set (0.00 sec)

更新資料

MySQL 並不支援 column->path 的形式進行更新操作。

如果是整個 json 更新的話，和插入時類似的：

mysql> select * from testproject where id = 10;

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.00 sec)

mysql> UPDATE testproject SET skill = '["js", "java"]' WHERE id = 10;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject where id = 10;

+----+----------------+---------------------------+

| id | skill | student |

+----+----------------+---------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "ggjg"} |

+----+----------------+---------------------------+

1 row in set (0.00 sec)

json_array_append和json_array_insert函式使用：

json_array_append是在json後面追加；

json_array_insert是在指定下標插入。

mysql> select * from testproject;

+----+----------------+------------------------------+

| id | skill | student |

+----+----------------+------------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "ggjg"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+------------------------------+

3 rows in set (0.00 sec)

mysql> SELECT json_array_append(skill, '$', 'c') from testproject;

+------------------------------------+

| json_array_append(skill, '$', 'c') |

+------------------------------------+

| ["js", "java", "c"] |

| ["c"] |

| [1, 2, 3, "c"] |

+------------------------------------+

3 rows in set (0.00 sec)

mysql> SELECT json_array_insert(skill, '$[1]', 'php') from testproject;

+-----------------------------------------+

| json_array_insert(skill, '$[1]', 'php') |

+-----------------------------------------+

| ["js", "php", "java"] |

| ["php"] |

| [1, "php", 2, 3] |

+-----------------------------------------+

3 rows in set (0.00 sec)

mysql>

json_replace、json_set、json_insert和json_remove函式用法：

json_replace：只替換已經存在的舊值，不存在則忽略；

json_set：替換舊值，並插入不存在的新值；

json_insert：插入新值，但不替換已經存在的舊值；

json_remove() 刪除元素。

json_replace：

mysql> select * from testproject;

+----+----------------+--------------------------------+

| id | skill | student |

+----+----------------+--------------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "smallsoup"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+--------------------------------+

3 rows in set (0.00 sec)

mysql>

mysql> UPDATE testproject SET student->'$.name' = 'smallsoup' where student->'$.id' = 1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio

n for the right syntax to use near '->'$.name' = 'smallsoup' where student->'$.id' = 1' at line 1

mysql>

mysql> UPDATE testproject SET student = json_replace(student, '$.name', 'soup') WHERE student->'$.id' = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject;

+----+----------------+------------------------------+

| id | skill | student |

+----+----------------+------------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "soup"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+------------------------------+

3 rows in set (0.00 sec)

json_set：

mysql> select * from testproject;

+----+----------------+------------------------------+

| id | skill | student |

+----+----------------+------------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "soup"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+------------------------------+

3 rows in set (0.00 sec)

mysql> UPDATE testproject SET student = json_set(student, '$.name', 'small', '$.age', 22) WHERE student->'$.id'= 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject;

+----+----------------+---------------------------------------+

| id | skill | student |

+----+----------------+---------------------------------------+

| 10 | ["js", "java"] | {"id": 1, "age": 22, "name": "small"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+---------------------------------------+

3 rows in set (0.00 sec)

json_insert：

mysql> select * from testproject;

+----+----------------+---------------------------------------+

| id | skill | student |

+----+----------------+---------------------------------------+

| 10 | ["js", "java"] | {"id": 1, "age": 22, "name": "small"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+---------------------------------------+

3 rows in set (0.00 sec)

mysql> UPDATE testproject SET student = json_insert(student, '$.name', 'soup', '$.addr', '蘇州') WHERE student->'$.id'=

1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject;

+----+----------------+---------------------------------------------------------+

| id | skill | student |

+----+----------------+---------------------------------------------------------+

| 10 | ["js", "java"] | {"id": 1, "age": 22, "addr": "蘇州", "name": "small"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+---------------------------------------------------------+

3 rows in set (0.00 sec)

json_remove() :

mysql> select * from testproject;

+----+----------------+---------------------------------------------------------+

| id | skill | student |

+----+----------------+---------------------------------------------------------+

| 10 | ["js", "java"] | {"id": 1, "age": 22, "addr": "蘇州", "name": "small"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+---------------------------------------------------------+

3 rows in set (0.00 sec)

mysql> UPDATE testproject SET student = json_remove(student, '$.name', '$.age') WHERE student->'$.id' = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject;

+----+----------------+------------------------------+

| id | skill | student |

+----+----------------+------------------------------+

| 10 | ["js", "java"] | {"id": 1, "addr": "蘇州"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+------------------------------+

3 rows in set (0.00 sec)

可以看到name和age就被移除了。

以上只列出了部分函式的說明，mysql官方提供的函式列表如下：

image

更多用法請檢視官方文件：

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

mysql支援原生json使用說明

相關文章