utf8mysql乱码_MySQL乱码的原因和设置UTF8数据格式

MySQL使用时&＃xff0c;有一件很痛苦的事情肯定是结果乱码。将编码格式都设置为UTF8可以解决这个问题&＃xff0c;我们今天来说下为什么要这么设置&＃xff0c;以及怎么设置。

MySQL字符格式

字符集

在编程语言中&＃xff0c;我们为了防止中文乱码&＃xff0c;会使用unicode对中文字符做处理&＃xff0c;而为了降低网络带宽和节省存储空间&＃xff0c;我们使用UTF8进行编码。对这两者有什么不同不够了解的同学&＃xff0c;可以参考Unicode字符集和UTF8编码编码的前世今生这篇文章。

同样在MySQL中&＃xff0c;我们也会有这样的处理&＃xff0c;我们可以查看当前数据库设置的编码方式(字符集)&＃xff1a;

mysql> show variables like &＃39;%char%&＃39;;

&＃43;--------------------------&＃43;----------------------------------&＃43;

| Variable_name | Value |

&＃43;--------------------------&＃43;----------------------------------&＃43;

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

&＃43;--------------------------&＃43;----------------------------------&＃43;

8 rows in set (0.00 sec)

表中就是当前设置的字符集&＃xff0c;先看不用关注的几个值&＃xff1a;

character_set_filesystem | binary&＃xff1a;文件系统上的存储格式&＃xff0c;默认为binary(二进制)

character_set_system | utf8&＃xff1a;系统的存储格式&＃xff0c;默认为utf8

character_sets_dir | /usr/local/mysql/share/charsets/&＃xff1a;可以使用的字符集的文件路径

剩下的几个就是日常影响读写乱码的参数了&＃xff1a;

- character_set_client&＃xff1a;客户端请求数据的字符集

- character_set_connection&＃xff1a;从客户端接收到数据&＃xff0c;然后传输的字符集

- character_set_database&＃xff1a;默认数据库的字符集&＃xff1b;如果没有默认数据库&＃xff0c;使用character_set_server字段

- character_set_results&＃xff1a;结果集的字符集

- character_set_server&＃xff1a;数据库服务器的默认字符集

字符集的转换流程分为3步&＃xff1a;

客户端请求数据库数据&＃xff0c;发送的数据使用character_set_client字符集

MySQL实例收到客户端发送的数据后&＃xff0c;将其转换为character_set_connection字符集

进行内部操作时&＃xff0c;将数据字符集转换为内部操作字符集&＃xff1a;

使用每个数据字段的character set设定值

若不存在&＃xff0c;使用对应数据表的default character set设定值

若不存在&＃xff0c;使用对应数据库的default character set设定值

若不存在&＃xff0c;使用character_set_server设定值

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

字符序

说字符序之前&＃xff0c;我们需要了解一点基础知识&＃xff1a;

字符(Character)是指人类语言中最小的表义符号。例如’A’、’B’等&＃xff1b;

给定一系列字符&＃xff0c;对每个字符赋予一个数值&＃xff0c;用数值来代表对应的字符&＃xff0c;这一数值就是字符的编码(Encoding)。例如&＃xff0c;我们给字符’A’赋予数值0&＃xff0c;给字符’B’赋予数值1&＃xff0c;则0就是字符’A’的编码&＃xff1b;

给定一系列字符并赋予对应的编码后&＃xff0c;所有这些字符和编码对组成的集合就是字符集(Character Set)。例如&＃xff0c;给定字符列表为{‘A’,’B’}时&＃xff0c;{‘A’&＃61;>0, ‘B’&＃61;>1}就是一个字符集&＃xff1b;

字符序(Collation)是指在同一字符集内字符之间的比较规则&＃xff1b;

确定字符序后&＃xff0c;才能在一个字符集上定义什么是等价的字符&＃xff0c;以及字符之间的大小关系&＃xff1b;

每个字符序唯一对应一种字符集&＃xff0c;但一个字符集可以对应多种字符序&＃xff0c;其中有一个是默认字符序(Default Collation)&＃xff1b;

MySQL中的字符序名称遵从命名惯例&＃xff1a;以字符序对应的字符集名称开头&＃xff1b;以_ci(表示大小写不敏感&＃xff0c;case insensitive)、_cs(表示大小写敏感&＃xff0c;case sensitive)或_bin(表示按编码值比较&＃xff0c;binary)结尾。例如&＃xff1a;在字符序“utf8_general_ci”下&＃xff0c;字符“a”和“A”是等价的&＃xff1b;

因此字符序不同于字符集&＃xff0c;用于数据库字段的相等或大小比较。我们查看MySQL实例设置的字符序&＃xff1a;

mysql> show variables like &＃39;collation%&＃39;;

&＃43;----------------------&＃43;-------------------&＃43;

| Variable_name | Value |

&＃43;----------------------&＃43;-------------------&＃43;

| collation_connection | latin1_swedish_ci |

| collation_database | latin1_swedish_ci |

| collation_server | latin1_swedish_ci |

&＃43;----------------------&＃43;-------------------&＃43;

3 rows in set (0.00 sec)

跟utf8对应的常用字符序是&＃xff1a;utf8_unicode_ci/utf8_general_ci和utf8_bin等&＃xff0c;那么他们的区别是什么呢&＃xff1f;

_bin是用二进制存储并比较&＃xff0c;区别大小写&＃xff0c;存储二进制内容时使用

utf8_general_ci&＃xff1a;校对速度快&＃xff0c;但准确度稍差&＃xff0c;使用中英文时使用

utf8_unicode_ci&＃xff1a;准确度高&＃xff0c;但校对速度稍慢&＃xff0c;使用德法俄等外语时使用

修改字符集和字符序

如果在MySQL连接时&＃xff0c;出现了乱码的问题&＃xff0c;那么基本可以确定是各个字符集/序设置不统一的原因。MySQL默认的latin1格式不支持中文&＃xff0c;由于我们在中国&＃xff0c;所以选择对中文和各语言支持都非常完善的utf8格式。所以&＃xff0c;我们需要将需要关注的字符集和字符序都修改为utf8格式。

你也可以选择utf8mb4格式&＃xff0c;这个格式支持保存emoji&＃x1f608;表情。

我们需要修改Mysql的配置文件&＃xff0c;查看配置文件的位置有两种方式&＃xff1a;

1. $ mysql --help | grep &＃39;my.cnf&＃39;

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

2. ps aux | grep mysql

在(1)中&＃xff0c;/etc/my.cnf, /etc/mysql/my.cnf, ~/.my.cnf 这些就是mysql默认会搜寻my.cnf的目录&＃xff0c;优先级依次升高。可以在各个配置文件里都使用long_query_time 来测试一下。

然后&＃xff0c;我们修改或新增下面的配置项&＃xff1a;

# 下面注释的几行可以不设置&＃xff0c;但如果你的没有生效&＃xff0c;也可以试试看

[mysqld]

character_set_server&＃61;utf8

collation-server&＃61;utf8_general_ci

skip-character-set-client-handshake

#init_connect&＃61;&＃39;SET NAMES utf8&＃39;

#[client]

#default-character-set&＃61;utf8

这三行配置就可以解决问题&＃xff0c;最关键的是最后一行&＃xff0c;参考mysql文档&＃xff0c;使用该参数会忽略客户端传递的字符集信息&＃xff0c;而直接使用服务端的设定&＃xff1b;再加上我们设定服务端的字符集和字符序均为utf8&＃xff0c;这样就保证了字符格式的统一&＃xff0c;解决乱码的问题。

重启mysql服务&＃xff0c;如果提示找不到服务&＃xff0c;请参考用service命令管理mysql启停&＃xff1a;

$ service mysqld restart

Shutting down MySQL.. [ OK ]

Starting MySQL. [ OK ]

连接到mysql&＃xff0c;查看当前编码&＃xff1a;

mysql> show variables like &＃39;%char%&＃39;;

&＃43;--------------------------&＃43;----------------------------------&＃43;

| Variable_name | Value |

&＃43;--------------------------&＃43;----------------------------------&＃43;

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

&＃43;--------------------------&＃43;----------------------------------&＃43;

8 rows in set (0.01 sec)

mysql> show variables like &＃39;collation%&＃39;;

&＃43;----------------------&＃43;-----------------&＃43;

| Variable_name | Value |

&＃43;----------------------&＃43;-----------------&＃43;

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | utf8_general_ci |

&＃43;----------------------&＃43;-----------------&＃43;

3 rows in set (0.01 sec)

可以看到一切都符合预期&＃xff0c;请求和存储的数据也不再是乱码了。

遇到的问题

unknown variable &＃39;default-character-set&＃61;utf8&＃39;

参考官方文档&＃xff0c;该参数自5.5.3版本废弃&＃xff0c;改为了character-set-server&＃xff0c;改为这个参数即可。

但这个是在[mysqld]下的配置&＃xff0c;在[client]下的配置依然使用default-character-set参数。

参考资料