https://www.cnblogs.com/yjmyzz/p/json-type-and-virtual-column-usage-in-mysql-5-7.html
mysql 5.7中有很多新的特性，但平时可能很少用到，这里列举2个实用的功能：虚拟列及json字段类型
一、先创建一个测试表：
`drop` `table` `if exists t_people;`
`CREATE` `TABLE` `t_people(`
``id` ``INT``(11) ``NOT` `NULL` `AUTO_INCREMENT,`
````name``` ``varchar``(50) ``NOT` `NULL` `DEFAULT` `''``,`
``profile` json ``not` `null` `,`
``created_at` ``TIMESTAMP``(3) ``DEFAULT` `CURRENT_TIMESTAMP``(3) ``ON` `UPDATE` `CURRENT_TIMESTAMP``(3),`
``updated_at` ``TIMESTAMP``(3) ``DEFAULT` `CURRENT_TIMESTAMP``(3) ``ON` `UPDATE` `CURRENT_TIMESTAMP``(3),`
`PRIMARY` `KEY` `(id));`
注：这里profile是一个json类型的字段，另db编码采用utf8mb4
二、生成测试数据
`delimiter //`
`-- 写一段存储过程，方便后面生成测试数据`
`create` `procedure` `batchInsert()`
`begin`
`declare` `i ``int``;`
`declare` `v_name ``varchar``(50);`
`declare` `v_profile ``varchar``(100);`
`set` `i=0;`
`while i<100000 do`
`set` `v_name = concat(``substring``(``'赵钱孙李周吴郑王张杨'``,floor(1+(rand()*10)),1),``substring``(``'菩提树下的杨过'``,floor(1+(rand()*7)),1),``substring``(``'我爱北京天安门'``,floor(1+(rand()*7)),1),i);`
`set` `v_profile = concat(``"{\"phone\":\""``,concat(``'13'``,floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9))) , ``"\",\"age\":"``,i,``"}"``);`
`insert` `into` `t_people(```name```,profile) ``values``(v_name,v_profile);`
`set` `i=i+1;`
`end` `while;`
`end``; //`
注：这段存储过程不是本文重点，看不懂的同学不用深研，大概意思就是name随机生成，profile随机生成一个类似{"phone":"13xxxxxx","age":x}的内容。
调用一下这个存储过程，生成100000条测试数据，数据大致长下面这样：
需求来了，假如我们要查姓“张”的人有多少个？
这显然是一个全表扫描！
三、前缀索引
肯定有同学想到了，在name上建一个前缀索引，只对name的第1个字做索引
`alter` `table` `t_people ``add` `key` `ix_name(``name``(1));`
确实是个好办法，效果也不错
但是需求总是变化的，如果想查第2个字是“杨”的人有多少？
依然会全表扫描。
四、虚拟列
`alter` `table` `t_people ``add` `second_name ``varchar``(3) generated always ``as``(``substring``(``name``,2,1)) stored;`
创建了一个虚拟列second_name，其值是substring(name,2,1)，即name中的第2个字，最后的stored表示，数据写入时这个列的值就会计算（详情可参考最后的参考链接）
注：虚拟列并不是真正的列，insert时也无法指定字段值。
然后在这个列上创建索引：
`alter` `table` `t_people ``add` `index` `ix_second_name(`second_name`);`
再来看下执行计划，索引生效了，扫描行数也明显下降。
当然，sql语句也可以改成：
`explain ``select` `count``(0) ``from` `t_people ``where` `second_name=``'杨'``;`
这样看上去更直观，效果不变。
五、json检索
又来新需求了：要查profile中手机号为13589135467，并且姓“吴”的人
注意：profile->"$.phone"=xxx 就是json字段的检索语法
分析执行计划，可以看到前缀索引“ix_name”生效了，但还有优化空间，仍然可以借助虚拟列，创建2个虚拟列phone、first_name，并创建联合索引。
`alter` `table` `t_people ``add` `first_name ``varchar``(3) generated always ``as``(``substring``(``name``,1,1)) stored;`
`alter` `table` `t_people ``add` `phone ``varchar``(20) generated always ``as``(profile->``"$.phone"``) stored;`
`alter` `table` `t_people ``add` `index` `ix_phone_firstname(phone,first_name);`
加了这2个虚拟列后，数据长这样：
注：phone列提取出来后，前后会带上引号。
刚才的需求，可以改写sql:
`select` `* ``from` `t_people ``where` `phone=``'\"13589135467\"'` `and` `name` `like` `'吴%'``;`
最后看下执行计划：
扫描行数下降到个位数，效果十分明显。
