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`)

);

查看表結(jié)構(gòu)：

image

這樣JSON的字段就被創(chuàng)建好了

注：JSON類型不能有默認(rèn)值。

插入JSON

插入 json 格式的字符串稍算，可以是對象的形式典尾，也可以是數(shù)組的形式，

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

插入json時糊探，數(shù)據(jù)庫會對json做校驗钾埂，不符合json規(guī)范就會報錯。

image

查詢JSON：

查詢 json 中的數(shù)據(jù)用 column->path 的形式科平，其中對象類型 path 這樣表示 $.path, 而數(shù)組類型則是$ [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

也可以用函數(shù)json_extract：

image

column->path方法有限制褥紫，數(shù)據(jù)源必須是表字段，否則就報錯：

image

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

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

image

這不是我們想要的故源，可以用 JSON_UNQUOTE 函數(shù)將雙引號去掉，從 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 將字符串轉(zhuǎn)成 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 中的元素搜索是嚴(yán)格區(qū)分變量類型的门驾，比如說整型和字符串是嚴(yán)格區(qū)分的：

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 的結(jié)果是不一樣的。

除了用以上 column->path 的形式搜索多柑，還可以用JSON_CONTAINS 函數(shù)奶是，但和 column->path 的形式有點相反的是，JSON_CONTAINS 第二個參數(shù)是不接受整數(shù)的竣灌，無論 json 元素是整型還是字符串聂沙，否則會出現(xiàn)這個錯誤：

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)

對于數(shù)組類型的 JSON 的查詢，比如說 skill 中包含有 3 的數(shù)據(jù)初嘹，同樣要用 JSON_CONTAINS 函數(shù)及汉，同樣第二個參數(shù)也需要是字符串：

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)

更新數(shù)據(jù)

MySQL 并不支持 column->path 的形式進(jìn)行更新操作。

如果是整個 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函數(shù)使用：

json_array_append是在json后面追加坷随；

json_array_insert是在指定下標(biāo)插入房铭。

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函數(shù)用法：

json_replace：只替換已經(jīng)存在的舊值缸匪，不存在則忽略；

json_set：替換舊值类溢，并插入不存在的新值凌蔬；

json_insert：插入新值，但不替換已經(jīng)存在的舊值豌骏；

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就被移除了。

以上只列出了部分函數(shù)的說明窃躲，mysql官方提供的函數(shù)列表如下：

image

最后计贰，csdn資源，收集了海量學(xué)習(xí)資料蒂窒，如果你準(zhǔn)備入IT坑躁倒，勵志成為優(yōu)秀的程序猿，那么這些資源很適合你洒琢，包括java秧秉、go、python衰抑、springcloud象迎、elk、嵌入式呛踊、大數(shù)據(jù)砾淌、面試資料、前端等資源谭网。同時我們組建了一個技術(shù)交流群汪厨，里面有很多大佬，會不定時分享技術(shù)文章愉择，如果你想來一起學(xué)習(xí)提高劫乱，可以關(guān)注以下公眾號后回復(fù)【2】，獲取锥涕。

我是小碗湯衷戈，我們一起學(xué)習(xí)，掃碼關(guān)注层坠，精彩內(nèi)容第一時間推給你

歡迎掃碼關(guān)注

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末殖妇，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子窿春，更是在濱河造成了極大的恐慌拉一，老刑警劉巖，帶你破解...
沈念sama閱讀 221,273評論 6贊 515
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件旧乞，死亡現(xiàn)場離奇詭異蔚润，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)尺栖，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 94,349評論 3贊 398
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門嫡纠，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人延赌，你說我怎么就攤上這事除盏。” “怎么了挫以？”我有些...
開封第一講書人閱讀 167,709評論 0贊 360
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵者蠕，是天一觀的道長。經(jīng)常有香客問我掐松，道長踱侣，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 59,520評論 1贊 296
?港島之戀（遺憾婚禮）
正文為了忘掉前任大磺，我火速辦了婚禮抡句，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘杠愧。我一直安慰自己待榔，他們只是感情好，可當(dāng)我...
茶點故事閱讀 68,515評論 6贊 397
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布流济。她就那樣靜靜地躺著锐锣，像睡著了一般。火紅的嫁衣襯著肌膚如雪袭灯。梳的紋絲不亂的頭發(fā)上刺下，一...
開封第一講書人閱讀 52,158評論 1贊 308
城市分裂傳說
那天，我揣著相機(jī)與錄音稽荧，去河邊找鬼橘茉。笑死，一個胖子當(dāng)著我的面吹牛姨丈，可吹牛的內(nèi)容都是我干的畅卓。我是一名探鬼主播，決...
沈念sama閱讀 40,755評論 3贊 421
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼蟋恬，長吁一口氣：“原來是場噩夢啊……” “哼翁潘！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起歼争，我...
開封第一講書人閱讀 39,660評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤拜马，失蹤者是張志新（化名）和其女友劉穎渗勘，沒想到半個月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體俩莽，經(jīng)...
沈念sama閱讀 46,203評論 1贊 319
?護(hù)林員之死
正文獨居荒郊野嶺守林人離奇死亡旺坠，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 38,287評論 3贊 340
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了扮超。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片取刃。...
茶點故事閱讀 40,427評論 1贊 352
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖出刷，靈堂內(nèi)的尸體忽然破棺而出璧疗，到底是詐尸還是另有隱情，我是刑警寧澤馁龟，帶...
沈念sama閱讀 36,122評論 5贊 349
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布崩侠，位于F島的核電站，受9級特大地震影響坷檩，放射性物質(zhì)發(fā)生泄漏啦膜。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 41,801評論 3贊 333
男人毒藥：我在死后第九天來索命
文/蒙蒙一淌喻、第九天我趴在偏房一處隱蔽的房頂上張望僧家。院中可真熱鬧，春花似錦裸删、人聲如沸八拱。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,272評論 0贊 23
一樁弒父案涯塔，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽肌稻。三九已至，卻和暖如春匕荸，著一層夾襖步出監(jiān)牢的瞬間爹谭，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,393評論 1贊 272
情欲美人皮
我被黑心中介騙來泰國打工榛搔，沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留诺凡，地道東北人。一個月前我還...
沈念sama閱讀 48,808評論 3贊 376
代替公主和親
正文我出身青樓践惑，卻偏偏與公主長得像腹泌，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子尔觉，可洞房花燭夜當(dāng)晚...
茶點故事閱讀 45,440評論 2贊 359

mysql支持原生json使用說明

插入JSON

查詢JSON：

更新數(shù)據(jù)

推薦閱讀更多精彩內(nèi)容