mysql在2015年的5.7以后，InnoDB存储引擎已经开始对json数据进行支持，对json支持有如下优势：

  • JSON数据有效性检查：BLOB类型无法在数据库层做这样的约束性检查，如不合法会报错：
  • 查询性能的提升：查询不需要遍历所有字符串才能找到数据
  • 支持索引：通过虚拟列的功能可以对JSON中的部分数据进行索引
  • 存储的容量大，近似于LongBlob与LongText（4G）

建表语句：

CREATE TABLE `employe` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(18) DEFAULT NULL,
  `sex` int DEFAULT NULL,
  `others` json DEFAULT NULL,
  `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '录入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=42334 DEFAULT CHARSET=utf8mb4;

mysql json 对应java类型 mysql jsonb_JSON

 获取JSON中的某个字段的风格与alibaba中fastjson中的JSONPath类似，但是功能与之相差甚远：

  • .    属性访问，例如$.name
  • $   根对象，即当前json
  • *   对象的所有属性，如：$.* ，当为JSONArray时，$.* 无效

  

其中支持json的主要函数如下表：或见https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

mysql json 对应java类型 mysql jsonb_JSON_02

 简单列一下常用操作，便于记忆：

  •  JSON_EXTRACT(column_name,'$.name'）返回该json列column中的name中的值
  • JSON_SEARCH(others, 'one', "露露")   若该字段存在，返回路径，若不存在返回null：select * from employe where JSON_SEARCH(others, 'one', "露露") is not null;//查找存在“露露”的记录；该方法中的中间参数有“one”与“all”两种，one返回该json中第一次找到的记录路径，all返回该json中所有找到的路径，即若存在多个“露露”则返回多个露露的路径
  • JSON_TYPE(others) 判断alias的json类型（ARRAY或者OBJECT）
  • JSON_VALID(others) 判断alias 是否是一个合法的json
  • JSON_CONTAINS(others, '露露', '$.alias');
  • mysql5.7.9开始增加了一种简写方式：column->path； select others->'$.address'  from employe   查询json中的address列 类似：select JSON_EXTRACT(others, '$.address') from employe where id = 42332;
  • select json_contains('{"a":1,"b":4}','{"a":1}')  结果1
    select json_contains('{"a":2,"b":1}','{"a":1}')  结果0
    select json_contains('{"a":[1,2,3],"b":1}','[1,2]','$.a')  结果1。数组包含则需要所有元素都存在。
    select json_contains('{"a":[1,2,3],"b":1}','1','$.a')   
  • select json_contains(others, '\"露露\"','$.alias') from employe；         使用json_contains的时候，如果是字符串要用双引号包括，如果去掉双引号则报错。

虚拟列：虚拟列类似于视图，将others字段中的address提取出来作为一个虚拟列address，当others字段中的address发生改变时，虚拟列会同时改变。

ALTER TABLE employe ADD address varchar(128) GENERATED ALWAYS AS (json_extract(others,'$.address')) VIRTUAL;

mysql json 对应java类型 mysql jsonb_JSON_03

若去掉address列的引号可用replace替换：

ALTER TABLE employe ADD address varchar(128) GENERATED ALWAYS AS (REPLACE(json_extract(others,'$.address'),"\"",'')) VIRTUAL;

可对该虚拟字段进行查询：

select * from employe where address="北京昌平";

数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将generated column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与virtual column相比并没有优势，因此，MySQL 5.7中，不指定generated column的类型，默认是virtual column。

 

 虚拟索引：虚拟列与普通列类似可创建索引，虚拟索引列也是通过传统的B+树索引即可实现对JSON格式部分属性的快速查询。使用方法是首先创建该虚拟列，然后在该虚拟列上创建索引：

alter table employe add INDEX add_index(address);

 在创建索引后执行语句select * from employe where address="北京昌平";如下图，发现使用到了索引： 

mysql json 对应java类型 mysql jsonb_mysql json 对应java类型_04