MySQL 字符集编码及乱码问题

最常见的问题就是数据库插入查看或导入导出出现了乱码，这应该是所有用过mysql的人都遇到过了吧。所以解决乱码问题，就应该理解其原因：就是客户端连接设置的编码与表编码不一致！

数据库字符集有多种，linux 系统默认是 utf-8 编码，而 windows 默认为 gbk 编码。一般我们常用的系统都是中英文命名和存储数据d系统，所以服务器和mysql数据库设置编码为 utf8。mysql 还有字符集 utf8mb4，兼容 utf8 ，可保持 emoji 表情，但比 utf8 多占用1个字节。

查看linux系统编码

shell> cat /etc/sysconfig/i18n LANG="en_US.UTF-8" SYSFONT="latarcyrheb-sun16" shell> echo $LANG en_US.UTF-8 shell> locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL= 查看数据库可设置的字符集及校对规则

mysql> SHOW CHARACTER SET; mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS ORDER BY CHARACTER_SET_NAME; +--------------------+----------------------+---------------------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+---------------------------------+--------+ | armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 | | ascii | ascii_general_ci | US ASCII | 1 | | big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 | | binary | binary | Binary pseudo charset | 1 | | cp1250 | cp1250_general_ci | Windows Central European | 1 | | cp1251 | cp1251_general_ci | Windows Cyrillic | 1 | | cp1256 | cp1256_general_ci | Windows Arabic | 1 | | cp1257 | cp1257_general_ci | Windows Baltic | 1 | | cp850 | cp850_general_ci | DOS West European | 1 | | cp852 | cp852_general_ci | DOS Central European | 1 | | cp866 | cp866_general_ci | DOS Russian | 1 | | cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 | | dec8 | dec8_swedish_ci | DEC West European | 1 | | eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 | | euckr | euckr_korean_ci | EUC-KR Korean | 2 | | gb18030 | gb18030_chinese_ci | China National Standard GB18030 | 4 | | gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 | | gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 | | geostd8 | geostd8_general_ci | GEOSTD8 Georgian | 1 | | greek | greek_general_ci | ISO 8859-7 Greek | 1 | | hebrew | hebrew_general_ci | ISO 8859-8 Hebrew | 1 | | hp8 | hp8_english_ci | HP West European | 1 | | keybcs2 | keybcs2_general_ci | DOS Kamenicky Czech-Slovak | 1 | | koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 | | koi8u | koi8u_general_ci | KOI8-U Ukrainian | 1 | | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 | | latin5 | latin5_turkish_ci | ISO 8859-9 Turkish | 1 | | latin7 | latin7_general_ci | ISO 8859-13 Baltic | 1 | | macce | macce_general_ci | Mac Central European | 1 | | macroman | macroman_general_ci | Mac West European | 1 | | sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 | | swe7 | swe7_swedish_ci | 7bit Swedish | 1 | | tis620 | tis620_thai_ci | TIS620 Thai | 1 | | ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 | | ujis | ujis_japanese_ci | EUC-JP Japanese | 3 | | utf16 | utf16_general_ci | UTF-16 Unicode | 4 | | utf16le | utf16le_general_ci | UTF-16LE Unicode | 4 | | utf32 | utf32_general_ci | UTF-32 Unicode | 4 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 | +--------------------+----------------------+---------------------------------+--------+ 查看 mysql 数据库系统默认字符集

mysql> SHOW GLOBAL VARIABLES LIKE '%character_set%'; +--------------------------+-------------------------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.7.16-linux-glibc2.5-i686/share/charsets/ | +--------------------------+-------------------------------------------------------------+ 说明：

character_set_client ：客户端使用的字符集 character_set_results ：服务器查询结果到客户端字符集 character_set_connection ：客户端到服务器默认转换的编码 character_set_database ：数据库创建时默认的字符集(MySQL 5.7.6 后不推荐使用) character_set_server ：数据库创建时默认的字符集 character_set_system ：系统默认元数据字符集，总是为 “utf8” character_set_filesystem ：导入导出时文件名字符集，默认binary则保持与client设置一样。 更改系统字符集,改为 “gb2312” 

shell> vi /etc/sysconfig/i18n LANG="zh_CN.GB2312" #LANG="en_US.UTF-8" SYSFONT="latarcyrheb-sun16" 设置生效 shell> . /etc/sysconfig/i18n 改了系统编码后，再访问查看mysql字符集.字符集没有什么变化.

mysql> SHOW GLOBAL VARIABLES LIKE '%character_set%'; +--------------------------+-------------------------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------------------------+ | 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-5.7.16-linux-glibc2.5-i686/share/charsets/ | +--------------------------+-------------------------------------------------------------+ 在linux服务器端的终端访问mysql，再查看当前会话的字符集，发现客户端这几个字符集编码变为 “gb2312”！

mysql> SHOW VARIABLES LIKE '%character_set%'; +--------------------------+-------------------------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------------------------+ | character_set_client | gb2312 | | character_set_connection | gb2312 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | gb2312 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.7.16-linux-glibc2.5-i686/share/charsets/ | +--------------------------+-------------------------------------------------------------+ 数据库默认字符集为 "character_set_database = latin1",所以当我们创建数据库没有指定字符集时，则使用默认设置。

mysql> CREATE DATABASE test; mysql> SHOW CREATE DATABASE test; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 此外，我们创建表的默认字符集为当前数据库的字符集。如下，新创建的表字符集也为 “latin1”

mysql> USE test; mysql> CREATE TABLE tab(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(30) NOT NULL,PRIMARY KEY(iD)); mysql> SHOW CREATE TABLE tab\G; *************************** 1. row *************************** Table: tab Create Table: CREATE TABLE `tab` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 现在创建数据库，并且指定默认字符集为“utf8”，再创建一张未指定字符集的表和一张指定其他字符集的表。发现，未指定字符集的表默认字符集与数据库一致的。

mysql> CREATE DATABASE test2 CHARACTER SET utf8; mysql> USE test2; mysql> CREATE TABLE tab(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(30) NOT NULL,PRIMARY KEY(iD)); mysql> CREATE TABLE tab1(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(30) NOT NULL,PRIMARY KEY(iD)) DEFAULT CHARSET=utf8mb4; mysql> SHOW CREATE TABLE tab\G; *************************** 1. row *************************** Table: tab Create Table: CREATE TABLE `tab` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> SHOW CREATE TABLE tab1\G; *************************** 1. row *************************** Table: tab1 Create Table: CREATE TABLE `tab1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 查看所有数据库字符集及排序规则

mysql> SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA; +--------------------+----------------------------+------------------------+ | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +--------------------+----------------------------+------------------------+ | information_schema | utf8 | utf8_general_ci | | mysql | latin1 | latin1_swedish_ci | | performance_schema | utf8 | utf8_general_ci | | sys | utf8 | utf8_general_ci | | test | latin1 | latin1_swedish_ci | | test2 | utf8 | utf8_general_ci | +--------------------+----------------------------+------------------------+ 查看所有表的排序规则(字符集)

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN('test','test2'); +--------------+------------+--------------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | +--------------+------------+--------------------+ | test | tab | latin1_swedish_ci | | test2 | tab | utf8_general_ci | | test2 | tab1 | utf8mb4_general_ci | +--------------+------------+--------------------+ [navicat 测试] 现在开始插入表数据，我们插入数据到数据库表 test.tab，该数据库及表字符集都是 “latin1”。 当前我用 navicat 连接，该客户端默认设置的相关连接编码为： character_set_client=utf8 character_set_connection=utf8 character_set_results=utf8

--插入英文字符，正常 mysql> use test; mysql> insert into tab values(1,'123'),(2,'abc'); --插入中外字符，失败 mysql> insert into tab values(3,'文档'); 1366 - Incorrect string value: '\xE6\x96\x87\xE6\xA1\xA3' for column 'name' at row 1 --怎么插入中文呢？？现在将客户端会话编码设置为latin1，保持与数据库一致，再执行插入中文。 mysql> SET names latin1; mysql> SHOW VARIABLES LIKE '%character_set%'; mysql> insert into tab values(3,'文档'); mysql> select * from tab; +----+------+ | id | name | +----+------+ | 1 | 123 | | 2 | abc | | 3 | 文档 | +----+------+ 这会儿就没有报错了！查询也正常！现在将连接会话改为 utf8 ，再次查询。 mysql> SET names utf8; mysql> SHOW VARIABLES LIKE '%character_set%'; mysql> select * from tab; +----+--------+ | id | name | +----+--------+ | 1 | 123 | | 2 | abc | | 3 | ?–???￡ | +----+--------+ 因为表字符编码（latin1）与当前客户端会话的字符编码（utf8）不一样，所以查询出来了乱码！！ 为了防止乱码出现，插入数据时客户端要与表字符集一样，查询数据时，客户端也要与表编码一样。 以下更详细一步进行测试，再次确认，系统当前默认配置的字符编码如下：

+--------------------------+-------------------------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------------------------+ | 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 | +--------------------------+-------------------------------------------------------------+

-- 现将客户端字符集 utf8 改为 latin1 ，插入时中文不乱码（下一步有测试），查询时乱码！ mysql> set character_set_client = latin1; mysql> insert into tab values(4,'测试'); mysql> select * from tab; +----+--------+ | id | name | +----+--------+ | 1 | 123 | | 2 | abc | | 3 | ?–???￡ | | 4 | ?μ?èˉ? | +----+--------+ -- 单独将服务器查询结果字符集 utf8 改为 latin1，则查询结果正常；不改则乱码。 mysql> set character_set_results = latin1; mysql> select * from tab; +----+--------+ | id | name | +----+--------+ | 1 | 123 | | 2 | abc | | 3 | 文档 | | 4 | 测试 | +----+--------+ 客户端插入和查询都正常了，那么 character_set_connection 是干嘛用的呢？干嘛用的呢？ character_set_connection 只作为中间转换，如果字符串未设置字符集且设置了排序规则，默认字符集为character_set_connection设置的字符集，且会判断字符串显式设置的排序规则是否准确，不正确则出错。

+--------------------------------------------------------------------------------------+ | | |character_set_client <------> character_set_connection <------> character_set_results | | | +--------------------------------------------------------------------------------------+

--设置 connection 字符集为 utf8， mysql> set character_set_connection = utf8; --只设置排序规则，但非当前 connection 对应的排序规则。错误！ mysql> select 'Müller' COLLATE latin1_german1_ci; 1253 - COLLATION 'latin1_german1_ci' is not valid for CHARACTER SET 'utf8' --只设置排序规则，为当前connection对应的排序规则。正确！ mysql> select 'Müller' COLLATE utf8_general_ci; --如果字符前面强制字符集 _latin1或_utf8 ，则不会进行connection转换。 【导出导入数据乱码问题】

数据库表字符集为 latin1 ，客户端字符集默认与linux操作系统设置一致为 utf8.导出数据时乱码！！

shell> mysqldump -uroot -p --opt -t test > test.sql shell> egrep -v "#|\*|--|^$" test.sql LOCK TABLES `tab` WRITE; INSERT INTO `tab` VALUES (1,'123'),(2,'abc'),(3,'?–???￡'),(4,'?μ?èˉ?'); UNLOCK TABLES; 现在设置导出时字符集为 latin1 ，与表一样。结果正常！！ shell> mysqldump -uroot -p --default-character-set=latin1 --opt -t test > test.sql shell> egrep -v "#|\*|--|^$" test.sql LOCK TABLES `tab` WRITE; INSERT INTO `tab` VALUES (1,'123'),(2,'abc'),(3,'文档'),(4,'测试'); UNLOCK TABLES;导入同理，若导入导出出现了乱码，那只要设置相同的字符集即可。

【更改字符集】

客户端的都设置，为会话级别： mysql> SET names latin1; 客户端单个设置，为会话级别： mysql> set character_set_connection = utf8; mysql> set @@session.character_set_connection = utf8; 客户端单个设置，为全局设置： mysql> set global character_set_client = gb2312; mysql> set @@global.character_set_client = latin1; 永久设置，在配置文件中更改： shell> vi /etc/my.cnf [client] default_character_set = utf8 [mysql] #客户端设置 default_character_set = utf8 [mysqld] #服务器设置 init-connect='SET NAMES utf8' character-set-server = utf8 collation-server = utf8_general_ci #default-character-set = utf8 skip-character-set-client-handshake#忽略客户端字符集,使用服务器设置

其他参考：MySQL字符集编码解析