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。