MySQL数据库字符集

一、MySQL支持的字符集介绍

MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段都可以指定不同的字符集，相比oracle和其他类数据库，在同一个数据库下只能使用一种字符集，MySQL更具有灵活性。

查看所有可用的字符集的命令为：

mysql> 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 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

mysql> 

或者查看系统表information_schema.character_set，可以显示所有的字符集和该字符集默认的校对规则。

MySQL的字符集包括**字符集（character）和校对规则（collation）**两个概念。其中字符集用来定义MySQL存储字符串的方式，校对规则用来定义比较字符串的方式。字符集和校对规则是一对多的关系，MySQL支持30多种字符集的70多种的校对规则。
每个字符集至少对应一个校对规则。可以用 “show collation like ‘gbk%’ ” 命令或者通过表information_schema.COLLATIONS来查看相关字符集的校对规则。

mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation      | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk     | 28 | Yes     | Yes      |       1 |
| gbk_bin        | gbk     | 87 |         | Yes      |       1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)

mysql> 

校对规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以 “_ci” （大小写不敏感）、“_cs”（大小写敏感）或“_bin”（二元，即比较是基于字符编码的值而与language无关）结束。

例如，上面例子中GBK的校对规则，其中 gbk_chinese_ci 是默认的校对规则，对大小写不敏感；而 gbk_bin 按照编码的值进行比较，对大小写敏感。

例如，如果指定的“A”和“a”按照 gbk_chinese_ci 校对规则进行比较，则认为两个字符是相同的，如果按照 gbk_bin 校对规则进行比较，则认为两个字符是不同的。我们事先需要确认应用的虚修，是按照什么样的排序方式，是否需要区分大小写，以确认校对规则的选择。

mysql> select case when 'A' collate utf8_general_ci = 'a' collate utf8_general_ci then 1 else 0 end;
+---------------------------------------------------------------------------------------+
| case when 'A' collate utf8_general_ci = 'a' collate utf8_general_ci then 1 else 0 end |
+---------------------------------------------------------------------------------------+
|                                                                                     1 |
+---------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select case when 'A' collate utf8_bin = 'a' collate utf8_bin then 1 else 0 end;
+-------------------------------------------------------------------------+
| case when 'A' collate utf8_bin = 'a' collate utf8_bin then 1 else 0 end |
+-------------------------------------------------------------------------+
|                                                                       0 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

二、MySQL字符集的设置

MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级、字段级。分别在不同的地方设置，作用也不同。

1.数据库字符集和校对规则
服务器字符集和校对规则，可以在MySQL服务启动的时候确定。
（1）my.cnf中：

[mysqld]
character-set-server = utf8

（2）在启动选项中指定：

[root@local ~]# mysqld --character-set-server=utf8 ...

（3）在编译时指定：

[root@local ~]# cmake . -DEFAULT_CHARSET=utf8 ...

以上三种设置的方式都只是指定了字符集，数据库字符集规则：
（1）指定了字符集和校对规则，则使用指定的。
（2）指定了字符集没有执行校对规则，则使用字符集默认的校对规则。
（3）指定了校对规则没有指定字符集，字符集则使用与改校对规则关联的字符集。
（4）没有指定字符集和校对规则，则使用服务器字符集和校对规则。

可以用一下命令查看当前服务器的字符集和校对规则：

mysql> show variables like '%character_set_server%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like '%collation_server%';
+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| collation_server | utf8_general_ci |
+------------------+-----------------+
1 row in set (0.01 sec)

mysql> 

数据库的字符集和校对格则可以在创建数据库时候指定，也可在创建完成后通过 “alter database” 命令修改。需要注意的是，如果数据库已有数据，修改字符集并不能将已有的数据按照新的字符集进行存放。

2.表字符集和校对规则

表的字符集和校对规则在床架表时指定，可以通过“alter table” 命令进行修改。同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新字符集进行存放。

设置表的字符集的规则与数据库规则类似。

建议创建表时明确指定字符集和校对规则，避免受到默认值的影响。显示表的字符集和校对规则，可以使用 “show create table” 命令查看：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

3.列字符集和校对规则

MySQL可以定义列级别的字符集和校对规则，主要是针对相同表不同字段需要使用不同的字符集的情况。同样列字符集可以在表创建时定义，修改表时调整。

三、连接字符集和校对规则

字符集的设置方式，确定的是数据保存的字符集和校对规则，对实际应用访问来说，还存在客户端和服务器之间交互的字符集和校对规则的设置。

对于交互操作，MySQL有3个参数：character_set_client、character_set_connection和character_set_results，分别代表客户端、连接和返回结果的字符集。通常情况下，这三个参数的字符集是相同的，保证用户写入的数据可以正确的读，尤其是中文字符的数据。

一般会同时设置3个参数，通过一下命令：

set NAMES ***;

命令可以同时修改3个参数的值，且相同。使用这个方法修改连接的字符集和校对规则，但是需要应用每次连接数据库后都执行这个命令。

另外一个简单方法是在my.cnf中设置：

[mysql]
default-character-set = gbk

重启服务后，所有连接默认使用的就是GBK字符集进行的。

四、字符集的修改

在应用研发阶段没有正确的设置字符集，就会在后期数据库运行一段时间后需要调整字符集。字符集的修改不能直接通过 “alter database character set *** ” 或者 “alter table tablename character set ** ” 命令进行。这两个命令不会更新已有数据记录的字符集，只对新创建的表或者和记录生效。

已有数据记录的字符集调整，需要先将数据导出，经过释放调整重新导入。
例如：将latinl字符集的数据库修改成GBK字符集的数据库过程。
1.导出表结构：

mysqldump -uroot -p --default-character-set=gbk -d <dbname> > createtab.sql

其中–default-character-set=gbk表示设置什么字符集连接，-d 表示只导出表结构，不导出数据。

2.手工修改createtab.sql中表结构定义中的字符集为新的字符集。

3.确保不会在录入和更新数据，导出所有记录。

mysqldump -uroot -p --quick --no-create-info --extendex-insert --default-character-set=latinl <dbname> > data.sql

–quick：该选项用于转储大的表。
–extended-insert：使用包括几个values列表的多行insert语法，是转储文件更小。
–no-create-info：不导出每个转储表的create table语句。
–default-character-set=latinl：按照原有的字符集导出数据，这样防止中文导出成乱码。

4.打开data.sql，修改SET NAMES latinl 为 SET NAMES gbk。

5.使用新字符集创建新的数据库。

create database <newdbname> default charset gbk;

6.创建表，执行createtab.sql。

mysql -uroot -p newdbname < createtab.sql

7.导入数据，执行data.sql

mysql -uroot -p newdbname < data.sql

注意：
在选择目标字符集的时候，要注意最好是源字符集的超集，或者确定比源字符集的字库更大，否则如果目标字符集的字库小于源字符集，那么目标字符集中不支持的字符导入后会变成乱码，丢失部分数据。