MySQL5.7 JSON数据类型

MySQL 5.7.8新增JSON数据类型，使用方式如下：

1. 新增字段

ALTER TABLE `表名` ADD COLUMN extended_data json DEFAULT NULL COMMENT '扩展参数' ;

2. 填充数据 点击查看官方示例

UPDATE `表名` SET extended_data = '{\"groupId\": 1235, \"groupName\": \"群组\", \"templeteId\": 1123, \"templeteName\": \"模板\"}'  WHERE `condition` ;

3. 示例查询

--JSONObejct {'key':'value'} 查询
 SELECT * FROM `表名` WHERE JSON_EXTRACT(extended_data, '$.groupId') = #{groupId} ;

--查询数据的指定key的value
 SELECT JSON_EXTRACT(extended_data, '$.groupName') FROM t_qrtz_job WHERE id = #{id};
 
 --特别的，如果该key对应的value是字符串类型，那么查询结果会带双引号，可是使用JSON_UNQUOTE去除（也可使用 ->> 去除，两种方法等效），参见“附 2”
 SELECT JSON_UNQUOTE(JSON_EXTRACT(extended_data, '$.groupName')) FROM t_qrtz_job WHERE id = #{id};
 --等同于
 SELECT extended_data ->> '$.groupName' FROM t_qrtz_job WHERE id = #{id};

--JSONArray Obejct [{'key1':'value1'}...{}] 查询
SELECT * FROM `表名` WHERE JSON_CONTAINS(data->'$[*].groupId', "1111", "$");

4. 创建索引 (JSON列无法创建索引。但是可以对JSON列中所存储的key进行创建虚拟列，然后对虚拟列创建索引)

示例：
#为JSON字段的key ->> groupId创建虚拟列
ALTER TABLE `表名` ADD extended_data_groupId_virtual BIGINT(32) GENERATED ALWAYS AS (json_extract(extended_data, '$.groupId' )) VIRTUAL ;
#在虚拟列上创建索引
CREATE INDEX extended_data_groupId_virtual_index ON `表名`(extended_data_groupId_virtual) USING BTREE COMMENT '扩展参数extended_data的key groupId索引' ;
#查看列的属性
SHOW COLUMNS FROM `表名` ;
#可以查看到创建的虚拟列有明显标识 VIRTUAL GENERATED
MySQL只是在表里保存字段的元数据，并未储存字段的值，这样表的大小并未增加。我们在此虚拟字段创建索引，便能大大提高搜索JSON 数据的效率

MySQL的JSON数据类型的优点和限制：

1. 优点

• 存储时自动验证，验证不通过将报错
• 更好的存储结构。

2. 限制

• JSON列不可有默认值
• JSON文本的最大长度取决于MySQL系统常量：max_allowed_packet（该值的查询和设置参见：https://blog.csdn.net/sunny05296/article/details/80446944
• JSON列无法创建索引。但是可以对JSON列中所存储的key进行创建虚拟列，然后对虚拟列创建索引。

---

特别的：

1. 使用JOSN数据类型存储中文的时候，JAVA通过JDBC连MySQLMySQL 5.7.8读取JSON类型字段会出现乱码

• 可以检查使用的MySQL驱动程序的版本，目前使用mysql-connector-java 5.1.40之前的版本会出现该BUG，解决方案较简单，直接升级驱动版本到5.1.40之后即可。

示例：
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.40</version>
</dependency>

2. MySQL JSON数据类型其他方法参见：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html