浅谈MySQL json类型

mysql很早就已经支持原生的json数据类型（从MySQL 5.7.8起），但目前看还是有些开发同学不了解，或者不敢用json类型。下面我们一起来学习下官网mysql的json类型。 {.is-info}

一、官网说明

As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: 从MySQL5.7.8开始，mysql支持RFC 7159定义的原生JSON数据类型，能够有效访问JSON文档中的数据。相比于存储于字符串，json类型存储有以下优点：
Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error. 自动校验json文档是否正确，不合法的值将返回error。
Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document. 优化存储格式。存储在json列中的json文档会被转换成内部格式，使得更快访问文档里面的元素。当服务器需要读取以二进制格式存储的JSON值时，不需要从文本中解析。二进制格式结构化能时服务器直接通过键值或者数组索引查找对应值，而不需要访问之前或之后的所有值。
The space required to store a JSON document is roughly the same as for LONGBLOB or LONGTEXT; see Section 11.7, “Data Type Storage Requirements”, for more information. It is important to keep in mind that the size of any JSON document stored in a JSON column is limited to the value of the max_allowed_packet system variable. JSON文档占用的空间与LONGBLOB或者LONGTEXT基本等同。JSON列的大小受限于max_allowed_packet变量的值。
A JSON column cannot have a non-NULL default value. JSON类型不能有非空默认值。
JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column. JSON字段跟其它二进制类型一样，不能直接创建索引；但是可以通过在生成的列上创建一个索引，生成的列指向JSON里的标量值。

参考文档：https://dev.mysql.com/doc/refman/5.7/en/json.html

总结：

1. mysql5.7.8开始支持json类型（开发、测试、生产MySQL版本5.7.18，支持√）；

2. 相比于使用varchar存储，json类型能自动校验json格式是否正确；

3. 能直接访问json对象里的键值对；

4. 占用空间大，能存储大字段 理论能存储字符数num = max_allowed_packet / 单字符大小(字母1，中文3，emoji4)

5. JSON类型不能有非空默认值；

create table t_json(
  json_wrong json not null default '{}',   --> 这是错的，不能有非空默认值
  json_right json null default null        --> 这是允许的，default null
)

6. json字段不能直接建索引，但可以通过创建虚拟列，指向JSON里的对应key来创建索引。

二、使用说明

-- 创建表
create table t_json(
  id int unsigned not null auto_increment,
  json_data json null default null comment 'json类型字段',
  primary key(id)
)comment '创建json类型表';

-- 插入数据，跟字符串一样
insert into t_json(json_data) values('{"id":1, "name":"abul"}');

-- 查询，使用->或者->>，注意两个区别（附1）
select json_data->"$.name" as name from t_json;
+--------+
| name   |
+--------+
| "abul" |
+--------+

select json_data->>"$.name" as name from t_json;
+------+
| name |
+------+
| abul |
+------+

-- 根据json里的字段模糊搜索，其余操作相同
select * from t_json where json_data->>"$.name" like 'ab%';

-- 变更某个key值
update t_json set json_data=json_replace(json_data,'$.name','xmabul') where id=1;

-- 创建虚拟列和索引
-- 场景：json某个key为高频查询，需要创建key对应索引
create table t_json_index(
  id int unsigned not null auto_increment,
  json_data json null default null comment 'json类型字段',
  name varchar(20) generated always as (json_data->>"$.name") comment 'json.name',  -- 虚拟列
  primary key(id),
  index idx_name(name)   -- 虚拟列索引
)comment '创建json类型表索引';

-- 判断指定值是否在json数组中
where json_contains(clear_rule_json,json_object("typeEnum",'MERCHANT_GET'))


-- json函数
-- 详细文档：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
->										等价于JSON_EXTRACT().		
->>										等价于JSON_UNQUOTE(JSON_EXTRACT())	
JSON_ARRAY()					创建一个数组
JSON_ARRAY_APPEND()		扩展json数组对象		
JSON_ARRAY_INSERT()		在指定数组位置插入值 
JSON_CONTAINS()				判断指定值是否在json文档中		
JSON_CONTAINS_PATH()	判断是否包含路径	
JSON_DEPTH()					返回最深层级		
JSON_EXTRACT()				解析json，返回value值		
JSON_INSERT()					插入值		
JSON_KEYS()						返回所有key		
JSON_LENGTH()					返回json元素个数		
JSON_MERGE()					json合并
JSON_MERGE_PATCH()		合并json，替换重复key值	
JSON_MERGE_PRESERVE()	json合并，相同key合并值	
JSON_OBJECT()					创建一个object对象		
JSON_PRETTY()					美化json对象
JSON_QUOTE()					添加引号		
JSON_REMOVE()					移除json中的某个key		
JSON_REPLACE()				替换json某个key值		
JSON_SEARCH()					查找值对应路径		
JSON_SET()						插入key值，相当于replace into		
JSON_STORAGE_SIZE()		返回存储json文档的二进制的字节数
JSON_TYPE()						返回类型		
JSON_UNQUOTE()				取消引号		
JSON_VALID()					判断json是否合法		

附：

1. ->和->>区别 mysql 5.7.9开始支持-> ;mysql5.7.13开始支持->>
   ->等价于JSON_EXTRACT()，将json拆解，如果是字符串会包含引号 ->>等价于JSON_UNQUOTE()，将json拆解，并且去掉引号

三、建议

1. 如果存储的json数据长度小于1000，并且不需要通过json文档里的key进行查询过滤，建议直接定义varchar类型，减少占用空间。
2. 如果需要根据键值查询，可以定义成json；但是当需要存储大字段时，还是建议存储到mongodb中，专业的存储引擎做专业的事。

原则上json数据存储建议 mongodb > mysql.varchar > mysql.json