今天介绍一个MySQL中的数据类型-JSON，相信大家对JSON都不陌生，在日常工作中使用到的频率也很高，话不多说，直接开始。

何谓JSON

看下RFC文档对于JSON的描述

The JavaScript Object Notation (JSON) Data Interchange Format

Abstract

   JavaScript Object Notation (JSON) is a lightweight, text-based,

   language-independent data interchange format.  It was derived from

   the ECMAScript Programming Language Standard.  JSON defines a small

   set of formatting rules for the portable representation of structured

   data.

   This document removes inconsistencies with other specifications of

   JSON, repairs specification errors, and offers experience-based

   interoperability guidance.

1.基于 JavaScript 语言的轻量级的数据交换格式

2.基于文本

3.语言无关

JSON应用场景

我大概使用过以下两类：

1.接口的数据交换，比如ajax请求时的application/json、rpc调用时的JSON序列化\反序列化；

2.以JSON格式存储数据，我接触过以下两种：

    2.1 以Mongodb为代表的文档型数据库，很好的支持JSON格式的数据存储；

    2.2 以MySQL为代表的关系型数据库，5.7.8之前没有JSON这种数据类型，只能以varchar或者text形式变相的支持JSON，存取键值极不方便；5.7.8开始有JSON数据类型，有专门语法支持键值的存取，易用性得到很大提升。

接下来重点聊聊MySQL中如何存取JSON以及存在的一些问题。

MySQL 存储JSON

熟悉关系型数据库的同学都知道，数据存储在表中，得先有表才能插数据，看一条普通的SQL insert语句

insert into user(id,name,age) values(1,'jack',10);

代表的语义是往user表中插入一条数据，这条数据有三个属性，分别是id、name、age，各自对应MySQL user表中的三个列，如果我们向user表中插入一个不存在的列salary，MySQL会报错

Error Code: 1054. Unknown column 'salary' in 'field list'

结论是要往MySQL表中插入数据，必须提前定义好表结构，表结构包括表名、表字符集、表包含的字段、字段名、字段类型等等。

有什么办法能不给表加物理字段就可以为数据增加属性呢？

给表预置一个扩展字段是一种解决思路，比如extdata，里面存储JSON形式的键值对，形如：

extdata
{
"salary":1000,
"sex":'女'
"其他key":'其他值'
}

至于存哪些key完全由使用方决定，key的数量不限，value的类型也不限，是不是有很好的扩展性，不管业务怎么变，底层存储都是支持的。

这也就是为什么要在MySQL中存取JSON的目的，主要是为了追求扩展性。

具体到MySQL中怎么实现，前面提到MySQL 5.7.8之前是不支持JSON的，要支持JSON语义，只能以字符串形式来变相实现，比如要修改extdata中的salary为2000，是没有办法直接修改的，需要先在应用层将extdata读出然后反序列化为JSON对象，通过JSON对象的Api来修改salary的值，修改完以后将新的JSON对象序列化为新JSON串，最后整体修改user表中的extdata字段为新JSON串，用代码实现大体如下：

1.result = db.execute("select extdata from user where id = xxx");

2.JSONObj = JSONUtil.parse(result.get("extdata"));

3.JSONObj.put("salary",2000);

4.extdata_str = JSONObj.toJSONString();

5.db.execute("update user set extdata=extdata_str where id=xxx");

这一套更新操作繁琐且性能低，读取操作也存在类似问题，由于没有原生Api的支持，这一切感觉有点糟糕。

到了MySQL 5.7.8开始，MySQL开始支持JSON这种数据类型，看下官方文档的介绍：

As of MySQL 5.7.8, MySQL supports a native data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The data type provides these advantages over storing JSON-format strings in a string column: JSONJSON

• Automatic validation of JSON documents stored in columns. Invalid documents produce an error. JSON

• Optimized storage format. JSON documents stored in 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

MySQL新增加的原生JSON类型比在字符串列中存储 JSON 格式的字符串相比有两个优点：

    1.自动的数据校验，对于JSON类型的列MySQL会校验其合法性；

    2.提供了更方便的Api用于存取，避免了繁琐的应用层操作。

看下基于MySQL 5.7.8，如何优雅的存取JSON类型中的键值，依然以修改extdata中的salary为例：

update user set extdata = JSON_SET(user.extdata, '$.salary',2000) where id =1;

读取salary的值：

select JSON_EXTRACT(user.extdata, '$.salary') from user where id =1;

借助JSON_SET和JSON_EXTRACT这两个Api，极大的降低了存取的复杂度，想深入了解MySQL JSON用法的请参考文章最后的推荐阅读内容。

说到这儿，借助MySQL的原生JSON类型以及相关的Api存取扩展数据在易用性方面已经没什么问题了，接下来从性能角度思考下是否有待提升。

--找出salary等于2000的user
select * from user where JSON_EXTRACT(user.extdata, '$.salary') =2000;

在我自己的pc机上，user表中共300万条数据，执行这条SQL花费接近3秒，不谈快慢，就论是否有优化空间，贴个执行计划出来

面对大名鼎鼎的全表扫描如何优化呢？

优化JSON查询

按照过往的思路，我们只要设计合理的索引就能避免全表扫描，但这次面对JSON似乎有点黔驴技穷了，别担心，大名鼎鼎的MySQL早已帮你做了既生瑜又生亮的美事，看看官方怎么说。

1. JSON类型列无法直接索引；

2. 可以基于JSON创建一个生成列，然后基于生成列创建索引，从而达到对JSON类型列加索引的效果。

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

1.创建一个生成列v_salary，计算列值表达式为extdata->"$.salary"，代表提取extdata中的salary值
ALTER TABLE `user` ADD COLUMN `v_salary` DECIMAL(10,2) as (extdata->"$.salary") AFTER `extdata`;
2.针对v_salary创建索引
ALTER TABLE `user` ADD INDEX `idx_salary` (`v_salary`) ;
3.替换查询语句中JSON_EXTRACT(user.extdata, '$.salary')为v_salary;