Mysql JSON数据类型
简介
在MySQL5.7.8之后开始支持一种可高效获取JSON文本中数据的原生JSON类型，该类型具有以下优点:
- JSON数据有效性检查：BLOB等类型无法在数据库层做这样的约束性检查，
- 保证了JSON数据类型的强校验，JSON数据列会自动校验存入此列的内容是否符合JSON格式，非正常格式则报错，而varchar类型和text等类型本身是不存在这种机制的。
- 查询性能的提升：查询不需要遍历所有字符串才能找到数据
- 支持索引：通过虚拟列的功能可以对JSON中的部分数据进行索引
另外，系统对JSON格式做了一些限制：
- JSON文本的最大长度取决有系统常量：max_allowed_packet。该值仅在服务器进行存储的时候进行限制，在内存中进行计算的时候是允许超过该值的。
- JSON列不可有默认值（声明时"DEFAULT NULL"）。
- JSON列与其他二进制类型列一样是无法创建索引。但是可以从JSON列中所存储的文本中某些表列值进行创建索引。MySQL最优控制器同样在通过JSON表达创建的索引中进行查询。
- 可以基于JSON格式的特征支持修改特定的键值。（即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去，MySQL内置的函数允许你通过一条SQL语句就能搞定）。
如何使用
1. 创建表
create table `test`( `id` INT AUTO_INCREMENT PRIMARY KEY, `content` JSON ) CHARSET = utf8;
2. 插入两条数据
INSERT INTO test (content) VALUES ('{"name": "baidu","host": "www.baidu.com"}'); INSERT INTO test (content) VALUES ('{"name": "alibaba","host": "www.alibaba.com"}');
注意：
JSON列存储的必须是JSON格式数据，否则会报错。ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'test.content'.
3. 查询
这里主要将关于JSON的查询。
对于表中JSON数据的查询，可以根据JSON中的key值进行查询，看下面SQL语句
select JSON_EXTRACT(content,'$.name'),JSON_EXTRACT(content,'$.host') from test;
select JSON_EXTRACT(content,'$.name'),JSON_EXTRACT(content,'$.host') from test where JSON_EXTRACT(content,'$.name') = "baidu";
JSON函数支持
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
JSON中虚拟列的使用
对于索引，JSON字段无法对其中的一个key值进行索引，但是虚拟列可以，我们可以建立一个虚拟列和JSON中key值建立联系。
1. 增加虚拟列v_name,v_host
注意：养成加前缀的好习惯， 例如这里使用"v_"来标记该字段是一个虚拟字段，在团队开发时，共同遵守一个约定， 相互配合起来会非常顺利。
ALTER TABLE test ADD COLUMN v_name CHAR(10) AS (content->'$.name'); ALTER TABLE test ADD COLUMN v_host CHAR(30) AS (content->'$.host');
下面对虚拟列建立索引
alter table test add index virtual_index(v_name);
然后查看基于v_name的查找的执行计划：
explain select content from test where v_name = "baidu";
从结果来看，查找已经走索引了。
上表中字段信息解释：
| 字段名 | 解释 |
|---|---|
| id | 选择标识符 |
| select_type | 查询的类型 |
| table | 输出结果的表，也就是被查询的表 |
| partions | 表示匹配的分区 |
| type | 表示表的连接类型 |
| possible_keys | 表示查询时，可能使用的索引 |
| key | 表示实际使用的索引 |
| key_len | 表示字段的长度 |
| ref | 列与索引的比较 |
| rows | 扫描出的行数（估算的行数） |
| filtered | 按查询条件过滤的行百分比 |
| Extra | 执行情况的描述和说明 |
对虚拟列的简介
在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。
注意：
在更新和插入数据时，不要给虚拟列设定值，否则会引发错误
ERROR 3105 (HY000): The value specified for generated column 'v_name' in table 'test' is not allowed.
Mybatis-plus处理json格式数据
在表映射实体类加上@TableName(autoResultMap = true);
在JSON字段映射的属性加上@TableField(typeHandler = FastjsonTypeHandler.class)；或 FastjsonTypeHandler.class
注：选择对应的 JSON 处理器也必须存在对应依赖包
示例图：
说明：
@TableName(value ="tablename",autoResultMap = true) public class DictData{ }
属性含义
value : 表名
autoResultMap ：是否自动构建 resultMap 并使用(如果设置 resultMap 则不会进行 resultMap 的自动构建并注入)(@since 3.1.2)
在对应实体的属性值上添加：
@TableField(typeHandler = JacksonTypeHandler.class) private Map<String,Object> otherInfoObj;
该注解对应了XML 中的写法
<result column="xx" property="实体属性名" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler" />
<result property="workPlan" column="work_plan" javaType="com.xx.module.xx.dto.ReportWorkPlanDto" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/> <result property="reportObject" column="report_object" javaType="java.util.ArrayList" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/> <result property="copyObject" column="copy_object" javaType="java.util.ArrayList" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
参考文章：https://www.cnblogs.com/youpeng/p/12409390.html
