下面一起來(lái)了解下MySQL5.7新增加的JSON數(shù)據(jù)類型特征,相信大家看完肯定會(huì)受益匪淺,文字在精不在多,希望mysql5.7新增加的JSON數(shù)據(jù)類型特征這篇短內(nèi)容是你想要的。
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供青田網(wǎng)站建設(shè)、青田做網(wǎng)站、青田網(wǎng)站設(shè)計(jì)、青田網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、青田企業(yè)網(wǎng)站模板建站服務(wù),10余年青田做網(wǎng)站經(jīng)驗(yàn),不只是建網(wǎng)站,更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
一、json結(jié)構(gòu)
創(chuàng)建測(cè)試表
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類型
填寫測(cè)試數(shù)據(jù)
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\"]');
總體預(yù)覽
二、json查詢
select id,json_extract(category,'$.name') as name from test.article;#提取json字段里面的信息
# column->path形式 訪問(wèn)json中的元素 category->'$.name'
select id,category->'$.name' as name from test.article;#提取json字段里面的信息(訪問(wèn)json中的元素 category->'$.name')
select id,json_unquote(json_extract(category,'$.name')) as name from test.article;#提取json字段里面的信息,json_unqoute去雙引號(hào)
select id,json_unquote(category->'$.name') as name from test.article;#提取json字段里面的信息,json_unqoute去雙引號(hào)
select id,category->>'$.name' as name from test.article;
select * from test.article where category='{"id": 1, "name": "php"}'; #json不同于字符串,不能當(dāng)作字符串做比較
select * from test.article where category=cast('{"id": 1, "name": "php"}' as JSON); #通過(guò)CAST將字符串轉(zhuǎn)換成JSON形式
select * from test.article where category->'$.name'='java';
select * from test.article where category->>'$.name'='java';
#JSON 中的元素搜索是嚴(yán)格區(qū)分變量類型的,比如說(shuō)整型和字符串是嚴(yán)格區(qū)分的
select * from test.article where category->'$.id'='2';#字符號(hào)串
select * from test.article where category->'$.id'=2;#×××
select * from test.article where category->'$.id'='3';#字符號(hào)串
select * from test.article where json_extract(category,'$.id')='3';#字符號(hào)串
select * from test.article where json_contains(category,'2','$.id');#整數(shù)
select * from test.article where json_contains(category,'"3"','$.id');#字符號(hào)串
select * from test.article where json_contains(tags,'"linux"');#字符號(hào)串
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格式的某個(gè)鍵字段創(chuàng)建索引。首先創(chuàng)建虛擬列,之后在改虛擬列上創(chuàng)建索引。
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、之后通過(guò)虛擬列名對(duì)json特定列進(jìn)行索引查詢:
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更新
如果是整個(gè) json 更新的話,和插入時(shí)類似的。
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
則可能要用到以下幾個(gè)函數(shù)
JSON_INSERT() 插入新值,但不會(huì)覆蓋已經(jīng)存在的值
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 沒(méi)有被修改,但新元素 url 已經(jīng)添加進(jìn)去
JSON_SET() 插入新值,并覆蓋已經(jīng)存在的值
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 已經(jīng)插入,url 已經(jīng)被修改
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 已經(jīng)被替換,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)
更多函數(shù)請(qǐng)參考:http://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html
雖然在 MySQL 是個(gè)JSON 類型,但實(shí)際在 PHP 應(yīng)用中返回的是 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數(shù)據(jù)類型特征這篇文章后,很多讀者朋友肯定會(huì)想要了解更多的相關(guān)內(nèi)容,如需獲取更多的行業(yè)信息,可以關(guān)注我們的行業(yè)資訊欄目。
新聞標(biāo)題:mysql5.7新增加的JSON數(shù)據(jù)類型特征介紹
網(wǎng)頁(yè)URL:http://muchs.cn/article36/ihicsg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供自適應(yīng)網(wǎng)站、全網(wǎng)營(yíng)銷推廣、網(wǎng)站設(shè)計(jì)、Google、App開發(fā)、外貿(mào)網(wǎng)站建設(shè)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)