您现在的位置是：首页 > 文章详情

MySQL编码引发的两个问题

日期：2019-05-13点击：416收藏

概述

先讲一下写该文章的原因，首先，工作中又遇到一条很熟悉的MySQL报错信息 Cause: java.sql.SQLException: Incorrect string value:Cause: java.sql.SQLException: Incorrect string value… （emoji表情存储导致），原因是MySQL的字符集导致的；其次，因为一直听说数据库变更可能锁表，但是一直不知道到底哪些操作会导致锁表。所以今天对相关知识做一个系统的整理。

对于mysql的字符集编码已经不陌生了，不过，每次遇到相关问题都是依赖于百度、Google...

今天遇到的emoji表情的存储问题也是司空见惯了，原因多数是因为MySQL使用了utf8字符集（至于公司之前为什么会用utf8我也不清楚，就不过多吐槽了），utf8字符集本身并无可厚非，但是MySQL的这一败笔算是真正的技术打脸，详情可见《永远不要在MySQL中使用UTF-8》。

言归正传，今天整理两个问题：

什么是MySQL编码？
什么操作会导致MySQL锁表？

确定要大刀阔斧的干？

遇到上面关于数据库字符集的问题，想必大家会想到两种办法：一，修改编码字符集为utf8mb4；二，找开发对相应的字段进行处理，然后再存入数据库。当然，第一种方法要简单有效的多，这也是大多数情况会采用的方法，这次我们也是采用的第一种方法，于是有了接下来的问题。

修改字段字符集编码：

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...]; 如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

再次插入数据，发现还是报错！why？Google一下，发现这篇文章彻底解决MySQL字符集问题。

沿着这个思路，查看公司线上和测试服(能正常写入数据)数据库编码相关配置：

如图所示，线上数据库数据表的编码仍然是utf8！问题的原因浮出水面，那么为什么线上线下会不一致呢？刚来公司不久就不追溯了。那么，改线上数据表的编码类型吧。

此时，另一个问题迎面而来，更改数据表编码类型会不会导致数据库锁表呢？(话外音：线上数据库，如果锁表，影响还是比较严重的。。。)在此告诫各位看管，线上任何修改一定要三四而后行！

想要解决上面提到的问题，还得从数据库的原理入手，下面做一个系统的学习和整理。

MySQL编码体系——数据存储编码

MySQL的字符编码结构比较细，它大方向分为两个部分：数据存储编码和数据传输编码。本篇讨论数据存储编码部分，数据传输编码详见 MySQL的字符编码体系（二）——数据存储编码。

编码层次

数据存储的字符编码配置是指定数据库中存储的数据默认采用什么字符编码。默认字符编码的设置分为四个层次：服务器级、数据库级、数据表级和列级。也就是说，可以为服务器设置一个默认字符编码，再为服务器中的每一个数据库设置不同的默认编码，再为同一个数据库中的每一个数据表设置不同的默认编码，再为同一个数据表中的每一个列设置不同的默认编码。

那这四个层次的编码设置到底如何起作用呢？如果新建数据库时没有指定字符编码，就默认设置为服务器的编码；如果新建数据表时没有指定任何编码，就默认设置为数据库的编码；如果向数据表添加新列或新建数据表时没有特别指定某些列的编码，那么这些列就默认设置为数据表的编码。注意这里四个层次的编码都是作为“默认”的存在，用户创建数据库、表或增加列时直接指定的编码是最优先的。

另一方面，直接改变这四个层次的编码并不会改变它们各自所有下层对象的当前编码。比如修改只Server级，那么所有已经存在的数据库的默认编码不变，数据表、表列以及每一行现有数据记录的字符编码都不变，但是如果新建一个数据库且不指定其默认编码，那它的默认编码就会被设置为Server的默认编码；同样即使修改了所有四个层次的编码，但是数据表中每一条现有记录的字符字段仍然是按原来的编码存储的，但是如果向数据表中新插入一条记录，数据库将根据数据表当前各列的默认编码来存储该条记录的各个字符字段。

参考：MySQL的字符编码体系（一）——数据存储编码

设置方法

修改Server以下各级编码的SQL语句如下：

# 库 ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name; # 表 ALTER TABLE dbl_name [DEFAULT] CHARACTER SET [=] charset_name; # 字段 ALTER TABLE dbl_name MODIFY [COLUMN] col_name {CHAR[(length)] | TEXT} CHARACTER SET charset_name;

注意上面第三条修改列字符编码，实际上是通过完全重新定义列属性的方式实现的，语法跟创建新数据表时指定列字段属性一样的。所以如果这里只是想修改列字符编码，那就必须完整地写上创建该列时使用的所有定义修饰。

修改Server默认编码可以通过运行时直接修改变量character_set_server实现，但这样是临时性的，客户端关闭重启后又会自动恢复。要想永久改变Server默认编码需要在my.ini或my.cnf配置文件的“[mysqld]”区域中设定该变量的值，然后重启服务器：

[mysqld] character_set_server=charset_name

锁表问题

然后，突然想到另一个问题，上面操作会不会导致数据库锁表呢？ 什么操作会导致mysql锁表？

mysql在修改表结构时的风险及解决办法

MySQL 5.7 online ddl的一些改进

mysql5.7 DDL 雷区

修改数据库编码的SQL

最后补充一下修改各个层级编码字符集的SQL：

查看数据库各种编码类型：
```
mysql> show variables like 'char%'; 
```

修改字段编码：(不要直接修改，避免锁表，推荐工具：pt-online-schema-change)

mysql> ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...]; 如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改数据表编码：

mysql> ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 [COLLATE ...] ; 如：ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改数据库编码：

mysql> set character_set_server = xxx; mysql> set character_set_database = xxx; 如：set character_set_database = utf8mb4;

这样修改，重启数据库后会被还原，如果要永久生效，需要修改数据库配置：

vim my.cnf

# 对本地的mysql客户端的配置 [client] default-character-set = utf8mb4 # 对其他远程连接的mysql客户端的配置 [mysql] default-character-set = utf8mb4 # 本地mysql服务的配置 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

重启mysql生效。

MySQL几个character_set变量的含义

参数	含义
character_set_client	主要用来设置客户端使用的字符集
character_set_connection	主要用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置
character_set_database	主要用来设置默认创建数据库的编码格式，如果在创建数据库时没有设置编码格式，就按照这个格式设置
character_set_filesystem	文件系统的编码格式，把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem，默认binary是不做任何转换的。
character_set_results	数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式
character_set_server	服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义
character_set_system	数据库系统使用的编码格式，这个值一直是utf8，不需要设置，它是为存储系统元数据的编码格式
character_sets_dir	这个变量是字符集安装的目录