MySQL5.7.7 labs版本开始在InnoDB存储引擎上提供原生JSON
类型的支持,JSON
值将不再以字符串的形式存储,而是采用一种允许快速读取文本元素(document elements)的内部二进制(internal binary)格式。在JSON
列插入或更新的时候将自动验证JSON
文本,未通过验证的文本将产生一个错误信息,JSON
文本采用标准的创建方式,可使用大多数的比较操作符进行比较操作。
# 查看MySQL版本
$ mysql -V
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper
# 查看MySQL版本
$ mysql --version
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper
-- 查看MySQL版本
SELECT VERSION();
5.7.20-log
在此之前,我们通常会使用varchar
或text
数据类型来存储JSON
格式的数据。以前的方式是需要先从MySQL中读出来,然后在代码中修改后再存入,非常麻烦而且不科学。虽然MySQL有了原生的JSON
数据类型后,但不知道性能如何呢?
优势特点
JSON
类型与varchar
和text
类型相比,有什么好处呢?
-
JSON
数据类型的列会自动校验数据是否为JSON
格式,若不是则会报错。 - MySQL提供了一组操作
JSON
数据的内置函数 - 存储在
JSON
列中的数据被转换成内部的存储格式,优化了存储格式,允许快速读取。 -
JSON
数据有效性检查,Blob
类型无法在数据库层做这样的约束性检查。 -
JSON
数据查询是不需要遍历所有字符串,提升了查询性能。 -
JSON
数据支持索引,可以通过虚拟列对JSON
部分数据进行索引。
JSON数据格式
# 连接数据库
$ mysql -u username -p
# 查看所有数据库
$ show databases;
# 创建数据库
$ create database test;
# 选择数据库
$ use test;
如果不是十分熟悉数据库操作,可参见《MySQL命令》。
-- 创建数据表
DROP TABLE IF EXISTS `game_config`;
CREATE TABLE IF NOT EXISTS `game_config`(
`id` int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(32) NOT NULL DEFAULT "",
`title` varchar(32) NOT NULL DEFAULT "",
`config` text DEFAULT NULL,
`remark` varchar(255) NOT NULL DEFAULT ""
)ENGINE=Innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 修改数据列的数据类型
ALTER TABLE `game_config` MODIFY `config` json;
-- 插入数据记录
INSERT INTO `game_config`(`name`,`title`,`config`)
VALUE('version','版本','{"app_version":"1.0.0", "res_version":"1.0.0"}');
INSERT INTO `game_config`(`name`,`title`,`config`)
VALUE('game','游戏','{"game_id":"10000", "game_type":1, "game_kind":2}');
INSERT INTO `game_config`(`name`,`title`,`config`)
VALUE('login','登录','{"login_ip":"192.168.50.25", "login_port":9001, "api_url":"/api/game/login"}');
注意:
-
JSON
列存储的必须是JSON
格式数据,否则会报错。 -
JSON
数据类型是没有默认值的
JOSN函数
MySQL中的JSON
分为json array
和json object
两种类型,$
表示整个json
对象,在索引json array
数据时使用下标,在索引json object
数据时使用键值。
JSON_ARRAY(value1, value2, value3,...)
生成一个包含指定元素的JSON
数组
mysql> SELECT JSON_ARRAY(1, "junchow", null, true, CURTIME());
+-------------------------------------------------+
| JSON_ARRAY(1, "junchow", null, true, CURTIME()) |
+-------------------------------------------------+
| [1, "junchow", null, true, "22:32:33.000000"] |
+-------------------------------------------------+
1 row in set (0.12 sec)
JSON_OBJECT(key1, value1, key2, value2,...)
生成一个包含KV键值对的json object
,如果key
为NULL
或参数个数为奇数则抛错。
mysql> SELECT JSON_OBJECT("id", 1, "name", "alice");
+---------------------------------------+
| JSON_OBJECT("id", 1, "name", "alice") |
+---------------------------------------+
| {"id": 1, "name": "alice"} |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OBJECT("id", 1, "name", "alice", "nick");
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'
mysql> SELECT JSON_OBJECT("id", 1, "name", "alice", null, "error");
ERROR 3158 (22032): JSON documents may not contain NULL member names.
CONVERT(json_string, JSON)
将JSON
字符串转换为JSON
对象格式
mysql> SELECT CONVERT('{"id":1, "name":"junchow"}', JSON);
+---------------------------------------------+
| CONVERT('{"id":1, "name":"junchow"}', JSON) |
+---------------------------------------------+
| {"id": 1, "name": "junchow"} |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONVERT('{id:1, name:"junchow"}', JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Missing a name for object member." at position 1.
JSON_QUOTE(json_val)
将JSON
对象转换为JSON
字符串,通过双引号字符包装并转义内部引号和其他字符,然后将结果作为utf8mb4
字符串返回,并将字符串引用为JSON
值。若参数为NULL
则返回NULL
。
mysql> SELECT JSON_QUOTE('{id:1, name:"junchow"}');
+--------------------------------------+
| JSON_QUOTE('{id:1, name:"junchow"}') |
+--------------------------------------+
| "{id:1, name:\"junchow\"}" |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_QUOTE('NULL');
+--------------------+
| JSON_QUOTE('NULL') |
+--------------------+
| "NULL" |
+--------------------+
1 row in set (0.00 sec)
JSON_CONTAINS(json_doc, val[, path])
查询json
文档中是否在指定path
上包含指定的数据,如果包含则返回1,否则返回0。如果有参数为NULL
或者path
不存在则返回NULL
。
# 从game_config表的config字段中判断game_kind为2的记录是否存在
mysql> SELECT JSON_CONTAINS(`config`, '2', '$.game_kind') FROM `game_config` WHERE `name`='game';
心得体会
其实我个人第一次看到有人用MySQL去存储JSON
类型的数据时,是一头的火,非常窝火,因为我要取出来然后在代码中解析然后再这样倒腾来倒腾去,非常麻烦。因为有NoSQL,存JSON
干嘛不直接用MongoDB。不过有了原生的JSON函数支持,也逐渐开始学习适应。避开好恶,RDB和NoSQL应用场景是不同的,相煎何太急。同事很喜欢用MySQL去存配置,到我这里做数据统计时,又是一头的抓狂。哎,为了省事,其实很多时候并不省事儿。既然使用到RDB,具有关系属性的字段是可以提取出来的,变化的字段和简单的配置也是可以使用的。文章的内容也是用到学到,学到用到,慢慢增加积累,一味的为了知识而学习,是一种不良作风,实践是需要场景而非模拟,才能体会到它的价值所在。
网友评论