如何排查mysql数据乱码_Mysql数据库乱码问题排查

我们可能遇到：

数据库中保存正确，但从数据库中读取出来的是乱码

写入的原始字符串是正确编码的，写入数据库后变成了乱码且不可恢复

遇到上面的问题，极有可能是某个环节的编码设置不正确导致(常见的可能是：表的默认字符集问题、表中字段设置了不正确的字符集、jdbc链接字符集未设置等)；

数据库相关的字符集设置

首先我们要了解，与数据库交互的环节，有哪些地方是可以设置字符集的：

表的每个字段可以单独设置字符集

每张表可以设置默认字符集

每个数据库可以设置默认字符集

数据库server可以设置默认字符集

连接数据库的client可以设置字符集

连接connection的url可以设置字符集

每次查询的result可以设置字符集

上面每个字符集的部分设置方法如下：

mysql> SET character_set_client = utf8mb4 ;

mysql> SET character_set_connection = utf8mb4 ;

mysql> SET character_set_server = utf8mb4 ;

mysql> SET character_set_results = utf8mb4 ;

mysql> SET character_set_database = utf8mb4 ;

他们对应的含义为：

character_set_server：默认的内部操作字符集

character_set_client：客户端来源数据使用的字符集

character_set_connection：连接层字符集(注意：jdbc链接可以设置字符集)

character_set_results：查询结果字符集

character_set_database：当前选中数据库的默认字符集

character_set_system：系统元数据(字段名等)字符集

还有以collation_开头的同上面对应的变量，用来描述字符序。

字段和表的默认字符集需要在创建时候设置。

注意为了兼容emoji字符，表的默认字符集需要设置为 utf8mb4.

对于常见的设置指令SET NAMES 'utf8' 它相当于下面的三句指令：

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

检测字符集问题的一些手段

• SHOW CHARACTER SET;

• SHOW COLLATION;

• SHOW VARIABLES LIKE ‘character%’;

• SHOW VARIABLES LIKE ‘collation%’;

• SQL函数HEX、LENGTH、CHAR_LENGTH

• SQL函数CHARSET、COLLATION

数据库的字符集转换过程

MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；

进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：

- 使用每个数据字段的CHARACTER SET设定值；

- 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；

- 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；

- 若上述值不存在，则使用character_set_server设定值。

将操作结果从内部操作字符集转换为character_set_results。

乱码问题排查思路举例

向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集，查询时设置连接字符集为utf8

插入时根据MySQL服务器的默认设置，character_set_client、character_set_connection和character_set_results均为latin1；

插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存；

查询时的结果将经过utf8=>utf8的字符集转换过程，将保存的6个字节原封不动返回，产生乱码。

向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8

插入时根据连接字符集设置，character_set_client、character_set_connection和character_set_results均为utf8；

插入数据将经过utf8=>utf8=>latin1的字符集转换，若原始数据中含有\u0000~\u00ff范围以外的Unicode字符，会因为无法在latin1字符集中表示而被转换为“?”(0×3F)符号，以后查询时不管连接字符集设置如何都无法恢复其内容了。

字符集概念科普

常见的字符集：

ASCII: 美国人编码，使用7位来对美国常用的字符进行编码，包含128个字符。

ISO-8859-1：欧洲的编码，使用8位来对欧洲常用的字符进行编码，包含256个字符。

GB2312/GBK: 中国标码，只包含常见的中文，一些特殊的中文是没有，内容并不完全。

Unicode： 万国码，包含了世界上所有的语言和符号。

Unicode编码有多种实现，如：

UTF-8：使用1-4个字节(最常用的是UTF-8)

UTF-16：使用2/4个字节

UTF-32：使用统一的固定4四个字节来表示一个字符

Java语言默认使用的Unicode编码，内存中是使用UTF-16编码，每个char代表一个字符，使用2个byte存储。由于UTF-16编码存在4个字节的情况，而Java中的string底层使用的byte[]，所以遇到emoji字符等特殊字符， string的一些截断类的工具方法substr等会造成乱码。

针对Java中的emoji字符问题，string中提供了codePoint的相关方法，可以规避上述问题.

代码示例：

//第二个字符是emoji字符

String s = "我\uD83D\uDE0D";

//字符长度变为3 因为byte[] 长度是3

System.out.println(s.length());

//产生乱码

System.out.println(s.substring(0,2));

//codePoints 的遍历只有2个字符 结果是正确的

s.codePoints().forEach(System.out::println);

//character可以将codePoint转换为byte[] 128525对应的是一个emoji字符 好色

char[] chars = Character.toChars(128525);

StringBuilder sb =new StringBuilder();

sb.append(chars);

//此时可以正确显式emoji字符

System.out.println(sb.toString());