mysql5.7新增加的JSON數據類型特征介紹

下面一起來了解下mysql5.7新增加的JSON數據類型特征，相信大家看完肯定會受益匪淺，文字在精不在多，希望mysql5.7新增加的JSON數據類型特征這篇短內容是你想要的。 

一、json結構

創建測試表

CREATE TABLE `article` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `category` json NOT NULL,
  `tags` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

分析：article表中的字段category與tags均為json類型

填寫測試數據

INSERT INTO `article` VALUES 
(1,'{\"id\": 1, \"name\": \"php\"}','[\"php\", \"mysql\", \"linux\", \"nginx\", \"redis\", \"memcache\", \"mongodb\"]'),
(2,'{\"id\": 2, \"name\": \"java\"}','[\"java\", \"mysql\", \"oracel\", \"linux\", \"nginx\", \"redis\", \"memcache\", \"mongodb\"]'),
(3,'{\"id\": \"3\", \"name\": \"c#\"}','[\"c\", \"c++\", \"OS\", \"linux\", \"unix\", \"IBM\"]');

總體預覽

二、json查詢

select id,json_extract(category,'$.name') as name from test.article;#提取json字段里面的信息

# column->path形式 訪問json中的元素 category->'$.name'
select id,category->'$.name' as name from test.article;#提取json字段里面的信息(訪問json中的元素 category->'$.name')
select id,json_unquote(json_extract(category,'$.name')) as name from test.article;#提取json字段里面的信息,json_unqoute去雙引號
select id,json_unquote(category->'$.name') as name from test.article;#提取json字段里面的信息，json_unqoute去雙引號
select id,category->>'$.name' as name from test.article;

select * from test.article where category='{"id": 1, "name": "php"}'; #json不同于字符串，不能當作字符串做比較

select * from test.article where category=cast('{"id": 1, "name": "php"}' as JSON); #通過CAST將字符串轉換成JSON形式


select * from test.article where category->'$.name'='java';

select * from test.article where category->>'$.name'='java';

#JSON 中的元素搜索是嚴格區分變量類型的，比如說整型和字符串是嚴格區分的

select * from test.article where category->'$.id'='2';#字符號串

select * from test.article where category->'$.id'=2;#×××

select * from test.article where category->'$.id'='3';#字符號串


select * from test.article where json_extract(category,'$.id')='3';#字符號串

select * from test.article where json_contains(category,'2','$.id');#整數

select * from test.article where json_contains(category,'"3"','$.id');#字符號串


select * from test.article where json_contains(tags,'"linux"');#字符號串

2、查詢json格式的字段
mysql> select jsn_extract(data, '.name′),jsnextract(data,′.address') from user;  
+-----------------------------+-------------------------------+  
| jsn_extract(data, '.name′)|jsnextract(data,′.address') |  
+-----------------------------+-------------------------------+  
| "David" | "Shangahai" |  
| "Amy" | NULL |  
+-----------------------------+-------------------------------+  
2 rows in set (0.00 sec) 
3、給json格式的某個鍵字段創建索引。首先創建虛擬列，之后在改虛擬列上創建索引。
mysql> ALTER TABLE user ADD user_name varchar(128)  
    -> GENERATED ALWAYS AS (jsn_extract(data,'$.name')) VIRTUAL;  
Query OK, 0 rows affected (0.01 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
   
mysql> select user_name from user;  
+-----------+  
| user_name |  
+-----------+  
| "Amy"     |  
| "David"   |  
+-----------+  
2 rows in set (0.00 sec)  
   
mysql> alter table user add index idx_username (user_name);  
Query OK, 2 rows affected (0.01 sec)  
Records: 2  Duplicates: 0  Warnings: 0  
4、之后通過虛擬列名對json特定列進行索引查詢：
mysql> explain select * from user where user_name='"Amy"'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: idx_username
          key: idx_username
      key_len: 131
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

三、json更新

更新 JSON

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

mysql> UPDATE lnmp SET tags = '[1, 3, 4]' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

但如果要更新 JSON 下的元素，MySQL 并不支持 column->path 的形式

mysql> UPDATE lnmp SET category->'$.name' = 'lnmp', tags->'$[0]' = 2 WHERE id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->'$.name' = 'lnmp', tags->'$[0]' = 2 WHERE id = 1' at line 1

則可能要用到以下幾個函數

JSON_INSERT() 插入新值，但不會覆蓋已經存在的值

mysql> UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------+-----------+
| id | category                                           | tags      |
+----+----------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php.net"}                       | [1, 3, 5] |
+----+----------------------------------------------------+-----------+
2 rows in set (0.00 sec)

可以看到 name 沒有被修改，但新元素 url 已經添加進去

JSON_SET() 插入新值，并覆蓋已經存在的值

mysql> UPDATE lnmp SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------------------------------------+-----------+
| id | category                                                                         | tags      |
+----+----------------------------------------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php.net"}                                                     | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)

可以看到 host 已經插入，url 已經被修改

JSON_REPLACE() 只替換存在的值

mysql> UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------------------------------------+-----------+
| id | category                                                                         | tags      |
+----+----------------------------------------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php"}                                                         | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)

可以看到 name 已經被替換，url 不存在被忽略。

JSON_REMOVE() 刪除 JSON 元素

mysql> UPDATE lnmp SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

更多函數請參考：http://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html

MySQL JSON 在 PHP 中的表現

雖然在 MySQL 是個JSON 類型，但實際在 PHP 應用中返回的是 JSON 格式的字符串

array(2) {
  [0]=>
  array(3) {
    ["id"]=>
    string(1) "1"
    ["category"]=>
    string(28) "{"id": 1, "name": "lnmp.cn"}"
    ["tags"]=>
    string(9) "[1, 3, 4]"
  }
  [1]=>
  array(3) {
    ["id"]=>
    string(1) "2"
    ["category"]=>
    string(24) "{"id": 2, "name": "php"}"
    ["tags"]=>
    string(9) "[1, 3, 5]"
  }
}

看完mysql5.7新增加的JSON數據類型特征這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。