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

);

查看表结构：

image

这样JSON的字段就被创建好了

注：JSON类型不能有默认值。

插入JSON

插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式，

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

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

插入json时，数据库会对json做校验，不符合json规范就会报错。

image

查询JSON：

查询 json 中的数据用 column->path 的形式，其中对象类型 path 这样表示 .path, 而数组类型则是[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

也可以用函数json_extract：

image

column->path方法有限制，数据源必须是表字段，否则就报错：

image

以下这样查询，查出来student->'$.name'包含双引号：

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

image

这不是我们想要的，可以用 JSON_UNQUOTE 函数将双引号去掉，从 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 将字符串转成 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 中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的：

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 的结果是不一样的。

除了用以上 column->path 的形式搜索，还可以用JSON_CONTAINS 函数，但和 column->path 的形式有点相反的是，JSON_CONTAINS 第二个参数是不接受整数的，无论 json 元素是整型还是字符串，否则会出现这个错误：

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)

对于数组类型的 JSON 的查询，比如说 skill 中包含有 3 的数据，同样要用 JSON_CONTAINS 函数，同样第二个参数也需要是字符串：

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)

更新数据

MySQL 并不支持 column->path 的形式进行更新操作。

如果是整个 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函数使用：

json_array_append是在json后面追加；

json_array_insert是在指定下标插入。

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函数用法：

json_replace：只替换已经存在的旧值，不存在则忽略；

json_set：替换旧值，并插入不存在的新值；

json_insert：插入新值，但不替换已经存在的旧值；

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'=

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

以上只列出了部分函数的说明，mysql官方提供的函数列表如下：

image

更多用法请查看官方文档：

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 215,076评论 6赞 497
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,658评论 3赞 389
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 160,732评论 0赞 350
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,493评论 1赞 288
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,591评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,598评论 1赞 293
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,601评论 3赞 415
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,348评论 0赞 270
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,797评论 1赞 307
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,114评论 2赞 330
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,278评论 1赞 344
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,953评论 5赞 339
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,585评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,202评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,442评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,180评论 2赞 367
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,139评论 2赞 352

mysql支持原生json使用说明

推荐阅读更多精彩内容