MySQL JSON

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，具有关系属性的字段是可以提取出来的，变化的字段和简单的配置也是可以使用的。文章的内容也是用到学到，学到用到，慢慢增加积累，一味的为了知识而学习，是一种不良作风，实践是需要场景而非模拟，才能体会到它的价值所在。