一、问题描述#
昨天晚上，测试小妹妹突然发现，保存企业信息的时候突然报错，我在查看日志之后，发现在添加企业管理员的时候保存企业和用户关系时执行sql报错，原因是user_name字段保存不了特殊字符，因为使用的是utf-8的编码格式，因此决定设置为utf8mb4编码格式，来存储用户名称。ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;执行了这条sql语句设置了表的字符集为utf8mb4，两分钟后，有人提出，日常环境查询巨慢，才发现问题。通过企业信息表和企业用户关系表查询时速度巨慢无比。sql如下：
select e.* from pub_yht_enterprise e , pub_yht_enterprise_user eu where e.id=eu.enterid and eu.userid= '1b65884d-49e9-4a42-b192-bd9cba095f67'
查看执行计划：惊讶的发现 pub_yht_enterprise竟然没有走主键索引，进行了全表扫描，怀疑是设置编码格式导致的。
通过show full columns from pub_yht_enterprise_user;查看表字段的编码格式，如下图：
两张表的企业id字段编码不同，更改列的编码格式ALTER TABLE pub_yht_enterprise_user CHANGE enterid enterid VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;再次查看执行计划，走了索引：如图：
问题解决。
二、问题总结#
（1）表字符集不同时，可能导致join的SQL使用不到索引，引起严重的性能问题；
（2）SQL上线前要做好SQL Review工作，尽量在和生产环境一样的环境下Review；
（3）改字符集的alter table操作会阻塞写，尽量在业务低峰操作，建议用pt-online-schema-change;
（4）表结构字符集要保持一致，发布时要做好审核工作；
（5）如果要大批量修改表的字符集，同样做好SQL的Review工作，关联的表的字符集一起做修改。
三、Mysql修改数据库、表、字段字符集说明
#
修改表字符集：
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE ...]; 把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...] 如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
只是修改表的默认字符集：
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
修改字段的字符集：
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
查看数据库编码：
SHOW CREATE DATABASE db_name;
查看表编码：
SHOW CREATE TABLE tbl_name;
查看字段编码：
SHOW FULL COLUMNS FROM tbl_name;
查看系统的编码字符
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
四、升级utf8到utf8mb4#
1、备份#
安全第一，备份所有需要升级字符编码的数据库
-
可以将库dump出来。
-
如果是虚拟机，可以给整个主机做快照。
2、升级数据库#
utf8mb4是MySQL5.5.3版本之后支持的字符集，so，如果你需要使用这个字符集，前提条件是你的MySQL版本必须 >= 5.5.3
3、修改编码集#
在MySQL中，可以为一个database设置字符编码，可以为一张表设置字符编码，也以为某一个字段设置字符编码。
-
查看当前系统默认的字符集设置
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
-
查看database的字符编码
show create database polarsnow;
-
查看table的字符编码
show create table ps;
-
查看column的字符编码
show full columns from ps;
修改database默认的字符集#
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci
虽然修改了database的字符集为utf8mb4，但是实际只是修改了database新创建的表，默认使用utf8mb4，原来已经存在的表，字符集并没有跟着改变，需要手动为每张表设置字符集。
修改table的字符集#
-
只修改表默认的字符集
ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -
修改表默认的字符集和所有字符列的字符集
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
单独修改column默认的字符集#
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
注：VARCHAR(191) 根据字段实例的类型填写
4、检查字段的最大长度和索引列#
-
字段长度
由于从utf8升级到了utf8mb4，一个字符所占用的空间也由3个字节增长到4个字节，但是我们当初创建表时，设置的字段类型以及最大的长度没有改变。例如，你在utf8下设置某一字段的类型为TINYTEXT, 这中字段类型最大可以容纳255字节，三个字节一个字符的情况下可以容纳85个字符，四个字节一个字符的情况下只能容纳63个字符，如果原表中的这个字段的值有一个或多个超过了63个字符，那么转换成utf8mb4字符编码时将转换失败，你必须先将TINYTEXT更改为TEXT等更高容量的类型之后才能继续转换字符编码
-
索引
在InnoDB引擎中，最大的索引长度为767字节，三个字节一个字符的情况下，索引列的字符长度最大可以达到255，四个字节一个字符的情况下，索引的字符长度最大只能到191。如果你已经存在的表中的索引列的类型为VARCHAR(255)那么转换utf8mb4时同样会转换失败。你需要先将VARCHAR(255)更改为VARCHAR(191)才能继续转换字符编码
5、修改配置文件#
SET NAMES utf8 COLLATE utf8_unicode_ci becomes SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci
> vim /etc/my.cnf # 对本地的mysql客户端的配置[client]default-character-set = utf8mb4 # 对其他远程连接的mysql客户端的配置[mysql]default-character-set = utf8mb4 # 本地mysql服务的配置[mysqld]character-set-client-handshake = FALSEcharacter-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci> service mysqld restart
检查修改
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';+--------------------------+--------------------+| Variable_name | Value |+--------------------------+--------------------+| character_set_client | utf8mb4 || character_set_connection | utf8mb4 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | utf8mb4 || character_set_server | utf8mb4 || character_set_system | utf8 || collation_connection | utf8mb4_unicode_ci || collation_database | utf8mb4_unicode_ci || collation_server | utf8mb4_unicode_ci |+--------------------------+--------------------+10 rows in set (0.00 sec)
注：character_set_system 一直都会是 utf8，不能被更改
6. 修复&优化所有数据表#
mysqlcheck -u root -p –auto-repair –optimize –all-databases
五、总结#
就一句话，上生产环境一定要评估sql，大家一起看，要执行的sql带来的影响，慎重再慎重，防止生产环境事故的发生
作者：Esofar
出处：https://www.cnblogs.com/ChenBingJie123/p/14153809.html
版权：本作品采用「署名-非商业性使用-相同方式共享 4.0 国际」许可协议进行许可。