MySQL里面的JSON特性

在我们梳理的开发规范里面，明确规定对于lob类型的使用原则只有一个，那就是尽量不要使用。但是很明显，开发同学走到了我们前面，如果你碰到开发同学使用JSON数据类型该怎么建议呢，至少在建议前我们也得了解下JSON类型的使用要领吧。

在说JSON类型之前，我们来说下在没有JSON数据类型之前我们是怎么处理一些复杂的数据映射的。

对于开发语言还是数据库技术来说，字符串处理总是很有魅力的一个特性，所以我会花更多的精力在这个上面。比如之前做了一个简单的测试。里面用到了一些看起来复杂的字符串处理函数find_in_set,substring_index等。

问题的背景是我们为一个表创建了两个列col1,col2,然后插入一些属性值。即col1里面的属性值和col2里面的属性值是对应的。或者换句话来说，col1里面存放的是key,col2存放的是value.

create table test1 ( col1 varchar(100),col2 varchar(100));

insert test1 select

'26,59,6', '1502.5,1690,2276.77' union all select

'59,33,6', '3502.1,1020,2276.77' union all select

'22,8,59', '1332.6,2900,1520.77';

写入数据之后，表里的数据分布是这样的：

mysql> select *from test1;

+---------+---------------------+

| col1 | col2 |

+---------+---------------------+

| 26,59,6 | 1502.5,1690,2276.77 |

| 59,33,6 | 3502.1,1020,2276.77 |

| 22,8,59 | 1332.6,2900,1520.77 |

+---------+---------------------+

3 rows in set (0.00 sec)

现在我们如果要做一个数据查询，把key是59的value值查出来，然后需要value值小于2000.

如果使用SQL，可能会是这样的解决方法。

mysql> select col1,col2

-> from (select *,find_in_set('59',col1) as rn from test1) k

-> where substring_index(concat(',',substring_index(col2,',',rn)),',',-1)

-> <'2000';

+---------+---------------------+

| col1 | col2 |

+---------+---------------------+

| 26,59,6 | 1502.5,1690,2276.77 |

| 22,8,59 | 1332.6,2900,1520.77 |

+---------+---------------------+

2 rows in set (0.00 sec)

当然可能你会有更好的解决方案，但是看起来似乎也不是一个很好的解决方法，比如这种设计中，如果要加一个字段，那简直就是灾难性的。

在这种模式下，使用JSON其实也是一种改进思路，当然这是在MySQL 5.7之后了。

我们创建的表为json_test,然后插入两行记录。

create table json_test ( uid int auto_increment,data json,primary key(uid))engine=innodb;

insert into json_test values (NULL,'{"name":"jeanron","mobile":"1500010002","location":"beijing"}');

insert into json_test values (NULL,'{"name":"jianrong","mobile":"15100020003","location":"gansu"}');

现在到了JSON发挥作用的时候了，如果要查询出数据，我们可以使用类似引用的语法"->"即可。所以我们可以把数据很方便的解析出来。

mysql> select data->"$.name" as name,(data->"$.location") from json_test group by name;

+------------+----------------------+

| name | (data->"$.location") |

+------------+----------------------+

| "jeanron" | "beijing" |

| "jianrong" | "gansu" |

+------------+----------------------+

2 rows in set (0.00 sec)

在这种模式下，上面的第一个难题其实就完全可以使用这种方式来解决了。

在这个基础上我们更近一步，在5.7里面还有辅助的特性虚拟列和相关的索引，可以提高我们查询的效率。我们添加一个虚拟列user_name.

ALTER TABLE json_test ADD user_name varchar(128) GENERATED ALWAYS AS(json_extract(data,'$.name')) VIRTUAL;

使用desc查看，其实可以看到user_name的属性是相对特殊的。

mysql> desc json_test;

+-----------+--------------+------+-----+---------+-------------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+--------------+------+-----+---------+-------------------+

| uid | int(11) | NO | PRI | NULL | auto_increment |

| data | json | YES | | NULL | |

| user_name | varchar(128) | YES | MUL | NULL | VIRTUAL GENERATED |

+-----------+--------------+------+-----+---------+-------------------+

3 rows in set (0.00 sec)

然后在这个基础上添加一个索引。

alter table json_test add index idx_username(user_name);

使用show create table的方式查看建表DDL可以清晰的看到是有一个辅助索引。

CREATE TABLE `json_test` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`data` json DEFAULT NULL,

`user_name` varchar(128) GENERATED ALWAYS AS (json_extract(`data`,'$.name')) VIRTUAL,

PRIMARY KEY (`uid`),

KEY `idx_username` (`user_name`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |

然后我们再次查询，注意在这里的user_name使用了双引号单引号混合的方式。

mysql> select user_name,(data->"$.location") from json_test where user_name = '"jianrong"';

+------------+----------------------+

| user_name | (data->"$.location") |

+------------+----------------------+

| "jianrong" | "gansu" |

+------------+----------------------+

1 row in set (0.00 sec)

如果带有疑惑，我们只有单引号是否可以，答案会让你失望。

mysql> select user_name,(data->"$.location") from json_test where user_name = 'jianrong';

Empty set (0.00 sec)

所以不是严格意义上100%的兼容性，至少在各式统一上我们还是需要一些额外的工作。

然后来看下执行计划的情况，可以看到语句明显使用到了索引，对于后期的数据分析和处理还是大有帮助的。

mysql> explain select user_name,(data->"$.location")from json_test where user_name = '"jeanron"';

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | json_test | NULL | ref | idx_username | idx_username | 387 | const | 1 | 100.00 | NULL |

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

在这个基础上如果做更多的分析，其实explain format=json也是一种改进方式，对于执行计划，我们可以得到属性值，通过解析的方式能够把执行计划做得更好。

JSON的新特性对于MySQL来说确实是一个不错的特性，如果数据量巨大，还是需要考虑通过空间换时间的思路来改进。如果大家了解Oracle,PostgreSQL等数据库，其实这些特性也是有的，Oracle 12c里面明确有这个特性，postgreSQL也有这个特性，还区分为json和jsonb,对于NoSQL来说，那更是它们擅长的，所以MySQL实现这个是一种辅助，绝对不是做了颠覆性的改进。

历史的车轮要前进，我们的方案只能是折中之后的方案。就好比早年的时候Oracle全面支持XML，结果到现在这类需求明显有些凉了。

JSON也不是万金油，推荐大家参考这一篇。

为什么说JSON不适合做配置文件？