一、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
注意:
在选择目标字符集的时候,要注意最好是源字符集的超集,或者确定比源字符集的字库更大,否则如果目标字符集的字库小于源字符集,那么目标字符集中不支持的字符导入后会变成乱码,丢失部分数据。