MySQL從5.7版本開始就支持JSON格式的數(shù)據(jù)，操作用起來挺方便的。

建表
在新建表時字段類型可以直接設(shè)置為json類型，比如我們創(chuàng)建一張表：

mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info` JSON);

json類型字段可以為NULL

插入數(shù)據(jù)：

mysql> INSERT INTO test_user(`name`, `info`) VALUES("xiaoming","{"sex": 1, "age": 18, "nick_name": "小萌"}");

json類型的字段必須時一個有效的json字符串

可以使用JSON_OBJECT()函數(shù)構(gòu)造json對象：

mysql> INSERT INTO test_user(`name`, `info`) VALUES("xiaohua", JSON_OBJECT("sex", 0, "age", 17));

使用JSON_ARRAY()函數(shù)構(gòu)造json數(shù)組：

mysql> INSERT INTO test_user(`name`, `info`) VALUES("xiaozhang", JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));

現(xiàn)在查看test_user表中的數(shù)據(jù)：

mysql> select * from test_user; 
+----+-----------+--------------------------------------------+ 
| id | name      | info                                       |
+----+-----------+--------------------------------------------+ 
|  1 | xiaoming  | {"age": 18, "sex": 1, "nick_name": "小萌"} | 
|  2 | xiaohua   | {"age": 17, "sex": 0}                      |
|  3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90]}   | 
+----+-----------+--------------------------------------------+
3 rows in set (0.04 sec)

查詢
表達(dá)式： 對象為json列->"$.鍵", 數(shù)組為json列->"$.鍵[index]"

mysql> select name, info->"$.nick_name", info->"$.sex", info->"$.tag[0]" from test_user; 
+-----------+---------------------+---------------+------------------+ 
| name      | info->"$.nick_name" | info->"$.sex" | info->"$.tag[0]" | 
+-----------+---------------------+---------------+------------------+ 
| xiaoming  | "小萌"              | 1             | NULL             | 
| xiaohua   | NULL                | 0             | NULL             | 
| xiaozhang | NULL                | 1             | 3                | 
+-----------+---------------------+---------------+------------------+ 
3 rows in set (0.04 sec)

等價于：對象為JSON_EXTRACT(json列 , "$.鍵")，數(shù)組為JSON_EXTRACT(json列 , "$.鍵[index]")

mysql> select name, JSON_EXTRACT(info, "$.nick_name"), JSON_EXTRACT(info, "$.sex"), JSON_EXTRACT(info, "$.tag[0]")  from test_user;
 +-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| name      | JSON_EXTRACT(info, "$.nick_name") | JSON_EXTRACT(info, "$.sex") | JSON_EXTRACT(info, "$.tag[0]") 
| +-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| xiaoming  | "小萌"                            | 1                           | NULL                           |
| xiaohua   | NULL                              | 0                           | NULL                           | 
| xiaozhang | NULL                              | 1                           | 3                              | 
+-----------+-----------------------------------+-----------------------------+--------------------------------+ 
3 rows in set (0.04 sec)

不過看到上面"小萌"是帶雙引號的，這不是我們想要的，可以用JSON_UNQUOTE函數(shù)將雙引號去掉

mysql> select name, JSON_UNQUOTE(info->"$.nick_name") from test_user where name="xiaoming"; 
+----------+-----------------------------------+ 
| name     | JSON_UNQUOTE(info->"$.nick_name") | 
+----------+-----------------------------------+ 
| xiaoming | 小萌                              | 
+----------+-----------------------------------+ 
1 row in set (0.05 sec)

也可以直接使用操作符->>

mysql> select name, info->>"$.nick_name" from test_user where name="xiaoming";
+----------+----------------------+ 
| name     | info->>"$.nick_name" | 
+----------+----------------------+ 
| xiaoming | 小萌                 | 
+----------+----------------------+ 
1 row in set (0.06 sec)

當(dāng)然屬性也可以作為查詢條件

mysql> select name, info->>"$.nick_name" from test_user where info->"$.nick_name"="小萌"; 
+----------+----------------------+ 
| name     | info->>"$.nick_name" | 
+----------+----------------------+ 
| xiaoming | 小萌                 | 
+----------+----------------------+ 
1 row in set (0.05 sec)

值得一提的是，可以通過虛擬列對JSON類型的指定屬性進(jìn)行快速查詢。

創(chuàng)建虛擬列:

mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info->>"$.nick_name") VIRTUAL;

注意用操作符->>

使用時和普通類型的列查詢是一樣:

mysql> select name,nick_name from test_user where nick_name="小萌"; 
+----------+-----------+ 
| name     | nick_name | 
+----------+-----------+ 
| xiaoming | 小萌      | 
+----------+-----------+ 
1 row in set (0.05 sec)

更新
使用JSON_INSERT()插入新值，但不會覆蓋已經(jīng)存在的值

mysql> UPDATE test_user SET info = JSON_INSERT(info, "$.sex", 1, "$.nick_name", "小花") where id=2;

看下結(jié)果

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name    | info                                       | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
|  2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | 小花      | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

使用JSON_SET()插入新值，并覆蓋已經(jīng)存在的值

mysql> UPDATE test_user SET info = JSON_INSERT(info, "$.sex", 0, "$.nick_name", "小張") where id=3;

看下結(jié)果

mysql> select * from test_user where id=3; 
+----+-----------+---------------------------------------------------------------+-----------+ 
| id | name      | info                                                          | nick_name | 
+----+-----------+---------------------------------------------------------------+-----------+ 
|  3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90], "nick_name": "小張"} | 小張      | 
+----+-----------+---------------------------------------------------------------+-----------+ 
1 row in set (0.06 sec)

使用JSON_REPLACE()只替換存在的值

mysql> UPDATE test_user SET info = JSON_REPLACE(info, "$.sex", 1, "$.tag", "[1,2,3]") where id=2;

看下結(jié)果

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name    | info                                       | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
|  2 | xiaohua | {"age": 17, "sex": 1, "nick_name": "小花"} | 小花      | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

可以看到tag沒有更新進(jìn)去

刪除

使用JSON_REMOVE()刪除JSON元素

mysql> UPDATE test_user SET info = JSON_REMOVE(info, "$.sex", "$.tag") where id=1;

看下結(jié)果

mysql> select * from test_user where id=1; 
+----+----------+----------------------------------+-----------+ 
| id | name     | info                             | nick_name | 
+----+----------+----------------------------------+-----------+ 
|  1 | xiaoming | {"age": 18, "nick_name": "小萌"} | 小萌      | 
+----+----------+----------------------------------+-----------+ 
1 row in set (0.05 sec)