MySQL5.7中的JSON基本操作指南

更新時(shí)間：2019年03月22日 11:10:18 作者：南丞_

這篇文章主要給大家介紹了關(guān)于MySQL5.7中JSON的基本操作，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用Mysql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

前言

因?yàn)轫?xiàng)目需要，存儲(chǔ)字段存儲(chǔ)成了JSON格式，在項(xiàng)目中是將查詢出來(lái)的值通過(guò)jackson轉(zhuǎn)成相應(yīng)的bean進(jìn)行處理的，覺得不夠簡(jiǎn)單方便。

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

建表

在新建表時(shí)字段類型可以直接設(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類型的字段必須時(shí)一個(gè)有效的json字符串

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

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á)式：對(duì)象為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)

等價(jià)于：對(duì)象為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)

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

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)

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

創(chuàng)建虛擬列:

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

注意用操作符->>

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

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

更新

使用JSON_INSERT()插入新值，但不會(huì)覆蓋已經(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)

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章: