Mysql字符集和乱码问题

一、转码失败

在数据写入到表的过程中转码失败,数据库端也没有进行恰当的处理,导致存放在表里的数据乱码。

针对这种情况,前几篇文章介绍过客户端发送请求到服务端。

其中任意一个编码不一致,都会导致表里的数据存入不正确的编码而产生乱码。

比如下面简单一条语句:

set @a = “文本字符串”;

insert into t1 values(@a);

1. 变量 @a 的字符编码是由参数 CHARACTER_SET_CLIENT 决定的,假设此时编码为 A,也就是变量 @a 的编码。

2. 写入语句在发送到 MySQL 服务端之前的编码由 CHARACTER_SET_CONNECTION 决定,假设此时编码为 B。

3. 经过 MySQL 一系列词法,语法解析等处理后,写入到表 t1,表 t1 的编码为 C。

那这里编码 A、编码 B、编码 C 如果不兼容,写入的数据就直接乱码。

二、客户端乱码

表数据正常,但是客户端展示后出现乱码。

这一类场景,指的是从 MySQL 表里拿数据出来返回到客户端,MySQL 里的数据本身没有问题。客户端发送请求到 MySQL,表的编码为 D,从 MySQL 拿到记录结果传输到客户端,此时记录编码为 E(CHARACTER_SET_RESULTS)。

那以上编码 E 和 D 如果不兼容,检索出来的数据就看起来乱码了。但是由于数据本身没有被破坏,所以换个兼容的编码就可以获取正确的结果。

这一类又分为以下三个不同的小类:

1)字段编码和表一致,客户端是不同的编码

比如下面例子, 表数据的编码是 utf8mb4,而 SESSION 1 发起的连接编码为 gbk。那由于编码不兼容,检索出来的数据肯定为乱码。

2)表编码和客户端的编码一致,但是记录之间编码存在不一致的情形

比如表编码是 utf8mb4,应用端编码也是 utf8mb4,但是表里的数据可能一半编码是 utf8mb4,另外一半是 gbk。那么此时表的数据也是正常的,不过此时采用哪种编码都读不到所有完整的数据。这样数据产生的原因很多,比如其中一种可能性就是表编码多次变更而且每次变更不彻底导致(变更不彻底,我之前的篇章里有介绍)。举个例子,表 t3 的编码之前是 utf8mb4,现在是 gbk,而且两次编码期间都被写入了正常的数据。

3)每个字段的编码不一致,导致乱码

和第二点一样的场景。不同的是:非记录间的编码不统一,而是每个字段编码不统一。举个例子,表 c1 字段 a1,a2。a1 编码 gbk,a2 编码是 utf8mb4。那每个字段单独读出来数据是完整的,但是所有字段一起读出来,数据总会有一部分乱码。

三、LATIN1

还有一种情形就是以 LATIN1 的编码存储数据

估计大家都知道字符集 LATIN1,LATIN1 对所有字符都是单字节流处理,遇到不能处理的字节流,保持原样,那么在以上两种存入和检索的过程中都能保证数据一致,所以 MySQL 长期以来默认的编码都是 LATIN1。这种情形,看起来也没啥不对的点,数据也没乱码,那为什么还有选用其他的编码呢?原因就是对字符存储的字节数不一样,比如 emoji 字符 “”,如果用 utf8mb4 存储,占用 3 个字节,那 varchar(12) 就能存放 12 个字符,但是换成 LATIN1,只能存 4 个字符。

总结

通过上面的详细说明,相信对 MySQL 乱码问题已经有一个很好的了解了。那来回顾下本篇的内容。本篇主要列列举了 MySQL 乱码可能出现的场景,并对应给出详细的处理方法以及相关建议,希望以后大家永远不会出现乱码问题。

为什么MYSQL要设定用UTF8MB4编码UTF8MB4

整理 MySQL 8.0 文档时发现一个变更:

默认字符集由 latin1 变为 utf8mb4。想起以前整理过字符集转换文档,升级到 MySQL 8.0 后大概率会有字符集转换的需求,在此正好分享一下。

当时的需求背景是:

部分系统使用的字符集是 utf8,但 utf8 最多只能存 3 字节长度的字符,不能存放 4 字节的生僻字或者表情符号,因此打算迁移到 utf8mb4。

迁移方案一1. 准备新的数据库实例,修改以下参数:[mysqld]## Character Settingsinit_connect=’SET NAMES utf8mb4’#连接建立时执行设置的语句,对super权限用户无效character-set-server = utf8mb4collation-server = utf8mb4_general_ci#设置服务端校验规则,如果字符串需要区分大小写,设置为utf8mb4_binskip-character-set-client-handshake#忽略应用连接自己设置的字符编码,保持与全局设置一致## Innodb Settingsinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_file_per_table = 1innodb_large_prefix = ON#允许索引的最大字节数为3072(不开启则最大为767字节,对于类似varchar(255)字段的索引会有问题,因为255*4大于767)

2. 停止应用,观察,确认不再有数据写入

可通过 show master status 观察 GTID 或者 binlog position,没有变化则没有写入。

3. 导出数据

先导出表结构:mysqldump -u -p –no-data –default-character-set=utf8mb4 –single-transaction –set-gtid-purged=OFF –databases testdb > /backup/testdb.sql

后导出数据:mysqldump -u -p –no-create-info –master-data=2 –flush-logs –routines –events –triggers –default-character-set=utf8mb4 –single-transaction –set-gtid-purged=OFF –database testdb > /backup/testdata.sql

4. 修改建表语句

修改导出的表结构文件,将表、列定义中的 utf8 改为 utf8mb4

5. 导入数据

先导入表结构:mysql -u -p testdb < /backup/testdb.sql

后导入数据:mysql -u -p testdb < /backup/testdata.sql

6. 建用户

查出旧环境的数据库用户,在新数据库中创建

7. 修改新数据库端口,启动应用进行测试

关闭旧数据库,修改新数据库端口重启,启动应用

安装mysql,字符编码为什么要选择UTF8?急。。。

因为UTF8能支持包括中文在内的其他几乎所有语言的字符, 又是Unicode中最省空间的编码. 兼容性也还不错, 除了和Windows不太一致, 其他系统的默认编码一般是UTF-8. 其他编码达不到这个效果.

mysql编码数据库,数据表,字段各用什么编码

1. ASCII

用途:用来映射简单的单字节字符,比如大小写英文字母、阿拉伯数字、常用的标点符、运算符、控制字符等。

编码范围:U+0000 – U+007F

注意:对于用这类字符的场景够用了,但是却无法表达比如汉字,日文等编码。

2. UNICODE

用途:用来映射包含 ASCII 以内的其他的所有字符。

编码范围:U+0000 – U+10FFFF

注意:ASCII 是 UNICODE 的子集,ASCII 编码的字符可以无损转换为 UNICODE 编码的字符。

MySQL 常用字符集

1. Latin1

Latin1 是 cp1252 或者 ISO-8859-1 的别名。ISO-8859-1 编码是单字节编码,向下兼容 ASCII。

编码范围:U+0000 – U+00FF

ISO-8859-1 收录的字符除 ASCII 收录的字符外,还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。

单字节内的空间都被 ISO-8859-1 编码占用,所以能够用 ISO-8859-1 编码存储、传输其他任何编码的字节流。

比如把一个 Utf8mb4 的编码或者 GBK 的编码存入 Latin1,不会有任何问题。因为 Latin1 保留了原始的字节流,这也就是 MySQL 长期以来把 Latin1 做默认字符集的原因。

但是由于 Latin1 对任何字符都存放字节流,造成了字符个数的浪费。

比如:

CHAR(10) CHARACTER SET LATIN1;CHAR(10) CHARACTER SET UTF8;

该字段中存储字符个数 UTF8 是 Latin1 的三倍!!!

2. GB18030

GB18030 是中国官方标准字符集,向前兼容 GBK、GB2312,是这两个的超集。用 1、2、4 个字节分别表示一个符号。比如对一般中文字符,默认是用两个字节编码存储。Windows 系统,默认用的就是 GB18030。

若只是存储中文字符,那 GB18030 最佳。

原因有两点:

1)占用空间小,比如比 UTF8 小。

2)存储的汉字根据拼音来排序,检索快。

3. UTF8

UTF8 是 Unicode 的编码实现,可以存储 UNICODE 编码对应的任何字符, 这也是使用最多的一种编码。最大的特点就是变长的编码方式,用 1 到 4 个字节表示一个符号,可以根据不同的符号编码字节长度。

字母或数字用 1 字节,汉字用 3 字节,emoji 表情符号用 4 字节。UTF8 字符集目前是使用最广泛的。

注意!MySQL 里常说的 UTF8 是 UTF8MB3 的别名,UTF8MB3 是 UTF8MB4 的子集,UTF8MB4 才是真正的 4 字节 UTF8 字符集!

UTF8MB3 表示最大支持 3 个字节存储字符,UTF8MB4 表示最大 4 个字节存储字符。根据实际需要和未来展望,MySQL 8.0 已经默认用 UTF8MB4 基础字符集。