mysql-charset

Posted on In

在MySQL的使用过程中遇到的“乱码”问题，很有可能就是因为对字符集与字符序的理解不到位、设置错误造成的。

  1. 字符集和字符序概念
  2. Mysql中的字符集配置
  3. 一个特殊的🌰

字符集和字符序

Charset

字符集就是字符编码，常见编码有utf8 gbk gb2312 gb18030 ascii等。就是一个字符和二进制码的一个对应表。

Collation

字符序就是字符的比较，很多情况下我们不光数字会有大小比较，程序里可以直接对字符进行比较操作，字符序就定义了这个比较规则，通常使用二进制的编码比较，当然也可以定义A==a。

查看Mysql支持的字符集

1
2
3
4
5
6
7
8
9
10
11
MySQL [charon]> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |

查看Mysql支持的字符序

1
2
3
4
5
6
7
8
9
10
11
12
13
MySQL [charon]> SHOW COLLATION WHERE Charset = 'utf8';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |

Mysql中的编码设置

MySQL提供了不同级别的设置，包括server级、database级、table级、column级，可以提供非常精准的设置。通常情况下当不指定db的字符集字符序时，db将默认使用server的编码配置，db和table，table和column也是一样。

可以通过下面命令来查看当前db的字符集配置。

1
2
3
4
5
6
7
8
9
10
11
12
13
MySQL [charon]> SHOW VARIABLES LIKE "%CHAR%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | gb2312                     |
| character_set_filesystem | utf8mb4                    |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
  • character_set_server 就是mysql默认的字符集
  • character_set_database 就是当前db使用字符集
  • character_set_system 是系统元数据使用的字符集，如字段名

除此之外我们看到还有其他的一些charset配置，往往这些配置才是导致中文乱码的罪魁祸首。

SET NAMES

我们通常会使用 set names utf8; 这个命令实际上就是修改了以下3个配置。

1
2
3
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
  • character_set_client 表示客户端发来语句的编码
  • character_set_connection 表示mysqld接收到客户端语句后要转换的编码
  • character_set_results 表示返回给客户端的数据的编码

最后还有一个character_set_filesystem 这是文件系统字符集编码,主要用于解析用于文件名称的字符串字面值,如LOAD DATA INFILE和SELECT …INTO OUTFILE等语句以及LOAD_FILE()函数.在打开文件之前,文件名称会从character_set_client转换为character_set_filesystem指定的编码. 实际中很少用到

解决中文乱码问题

统一UTF8

最简单的方式是统一所有的编码设置为utf8。这里需要注意的是如果使用终端进行查询的话，还需要设置终端使用的文件编码，通过locale可以来查看。通常设置成zh_CH.utf8。具体的设置可以自行百度locale。

非utf8编码数据

但有时候一些历史的项目使用了其他的编码方式，这个时候乱码问题就需要随机应变来解决了。

比较特殊的一个编码方式是CHARSET=latin1；

Latin1是ISO-8859-1的别名，有些环境下写作Latin-1。

ISO-8859-1编码是单字节编码，向下兼容ASCII，其编码范围是0x00-0xFF，0x00-0x7F之间完全和ASCII一致，0x80-0x9F之间是控制字符，0xA0-0xFF之间是文字符号。

ISO-8859-1收录的字符除ASCII收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。欧元符号出现的比较晚，没有被收录在ISO-8859-1当中。

因为ISO-8859-1编码范围使用了单字节内的所有空间，在支持ISO-8859-1的系统中传输和存储其他任何编码的字节流都不会被抛弃。换言之，把其他任何编码的字节流当作ISO-8859-1编码看待都没有问题。这是个很重要的特性，MySQL数据库默认编码是Latin1就是利用了这个特性。ASCII编码是一个7位的容器，ISO-8859-1编码是一个8位的容器。

所以理论上latin1字符集可以存各种编码，重要的是你需要知道当时写入的是何种编码。当读取出来以后，如果在终端下，就会理解成locale类型（如果locale系gbk，当时写入的gbk中文串可正常回显）。

此外还有另外一种方法就是使用mysql的内置函数去进行编码的修改convert(unhex(hex(convert(server_name using latin1))) using gb2312)也可以解决乱码问题。