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

);

查看表结构:

这样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规范就会报错。

查询JSON:

查询 json 中的数据用 column->path 的形式,其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]

查询testproject表student字段中json对象id为1的记录:

SELECT * FROM testproject WHERE student->'$.id'= 1;

查询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;

也可以用函数json_extract:

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

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

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

这不是我们想要的,可以用 JSON_UNQUOTE 函数将双引号去掉,从 MySQL 5.7.13 起也可以通过这个操作符 ->> 这个和 JSON_UNQUOTE 是等价的。

因为 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'=  
 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就被移除了。

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

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

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

原文发布于微信公众号 - 我的小碗汤(mysmallsoup)

原文发表时间:2018-06-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java达人

有哪些常用的sql语句

首行当然是最基本的增删查改啦,其中最重要的是查。 还有就是一些要注意的地方,就是SQL语句对大小写不敏感,语句中列名对应的值要用单引号''括起来不是双引号。...

19310
来自专栏PHP在线

mysql

cross join :笛卡尔交集。 Inner join :内连接。 left join :只要满足左边表的需求就可以了,右表有无都可以。 right ...

3688
来自专栏一个会写诗的程序员的博客

mysql 多表关联查询 实现 全文匹配的 模糊搜索接口 SQLmysql 多表关联查询 实现 全文匹配的 模糊搜索接口 SQL

在mysql中,有时我们在做数据库查询时,需要得到某字段中包含某个值的记录,但是它也不是用like能解决的,使用like可能查到我们不想要的记录,它比like更...

1872
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】MySQL进阶五、带or多条件查询

【数据库】MySQL进阶五、or多条件查询 MySQL数据表中带OR的多条件查询 OR关键字可以联合多个条件进行查询。使用OR关键字时: 条件 1) 只要符合这...

5827
来自专栏码农分享

数据库批量删除

2253
来自专栏性能与架构

Mysql group by实现方式(一) - 索引扫描

由于GROUP BY实际上也同样须要进行排序操作,而且与ORDER BY相比,GROUP BY主要只是多了排序之后的分组操作。所以,在GROUP BY的实现过程...

3145
来自专栏全栈工程师成长之路

深入浅出后端开发(SQL指令笔记)

3377
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】MySQL进阶六、模糊查询用法

【数据库】MySQL进阶六、 模糊查询用法 javahelp mysql中模糊查询的四种用法介绍 这篇文章主要介绍了mysql中模糊查询的四种用法,需要的朋友可...

48713
来自专栏DT乱“码”

Oracle存储过程基本语法介绍

Oracle存储过程基本语法 存储过程    1 CREATE OR REPLACE PROCEDURE 存储过程名    2 IS    3 BEGIN...

3635
来自专栏C# 编程

1 - SQL Server 2008 之 使用SQL语句创建具有约束条件的表

约束条件分为以下几种: 1)非空约束,使用NOT NULL关键字; 2)默认值约束,使用DEFAULT关键字; 3)检查约束,使用CHECK关键字; 4)唯一约...

2060

扫码关注云+社区

领取腾讯云代金券