mysql5.7虚拟列的实际应用

1、背景/原因

请教一下 mysql 字符集为utf8mb4的表，如果需求要把其中一列含有中英文数字符号的varchar列按照中文首字母排序，直接把校对集改成gbk_chinese_ci可以么，会不会有什么问题？

还是说直接在sql用order by convert(colname using gbk) asc？

2、问题分析

通过上述问题的描述及进一步的沟通，开发人员是想完成对昵称列中文排序的功能。

3、解决方案

3.1、由于数据库默认设置的字符集为utf8，这也就是为什么开发人员，直观想将字段改成gbk来完成中文排序的功能了。
3.2、确实可以通过sql语句来进行转换字段编码，来完成中文排序功能，但想过去转换+排序这效率应该高不到哪里去（用不上索引）
3.3、也可以新建一个字段，用于存储所有昵称列的拼音（牺牲存储空间，提升了程序的复杂度）

4、MySQL5.7的新做法
与开发人员沟通后，目前MySQL实例已经用上了5.7版本（这也归功于DBA运维不断推荐新业务尝试新版本），可以使用上MySQL5.7提供的虚拟列功能
以下为测试虚拟列功能：
4.1、新增虚拟列

root@localhost Wed Dec 13 10:46:14 2017 10:46:14 [liufofu]> alter table users add vir_nickname varchar(26) character set gbk generated always as (convert(nickname using gbk));
Query OK, 0 rows affected (0.13 sec)
查看虚拟列

root@localhost Wed Dec 13 10:46:26 2017 10:46:26 [liufofu]> show create table users;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------
| users | CREATE TABLE `users` (
.....

`vir_nickname` varchar(26) CHARACTER SET gbk GENERATED ALWAYS AS (convert(`nickname` using gbk)) VIRTUAL,
.......
4.2、使用虚拟列：

root@localhost Wed Dec 13 10:47:06 2017 10:47:06 [liufofu]> select nickname from users ORDER BY vir_nickname ASC; 

| 阿布在雨林 |
| 毕打天下 |
| 毕打天下 |
| 冰凉火焰_pyan |
| 的的喀喀_fdwi |
| 的的喀喀_ng36 |
| 该昵称已被占用 |
| 獦獦獦 |
| 哈哈哈 |
| 寒天 |
| 和气生财_ajv5 |
| 杰兰特 |
| 咖啡的秋 |
| 卡农来泽_173397702 |
| 凯类视觉_byhp |

通过以上排序来看，基本达到了需求。

5、参考资料：

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
虚拟列方式：virtual(默认),stored
1、If a generated column is indexed, the optimizer recognizes query expressions that match the column definition and uses indexes from the column as appropriate during query execution
2、Stored generated columns can be used as a materialized cache for complicated conditions that are costly to calculate on the fly
https://dev.mysql.com/doc/refman/5.7/en/generated-column-index-optimizations.html

转载于:https://www.cnblogs.com/liufofu/p/8032661.html