mysql dbcollat_mysql字符集小结

什么是character set和collation

--a set of symbols and encodings

--a set of rules for comparing characters in a character set

--一个character set至少有一个collation/不同的character sets不能共享collation

关于collation

_ci结尾的为case-insentivie; _cs结尾的为case-sensitive；_bin结尾的为binary collation

Collation可以当作普通函数用在各种语法中order by /group by /distinct/max(0

比||优先级高, x || y collate z == x || (y collate z)

Mysql字符集有4个等级：server/database/table/column，另外每个连接也都需设定字符集

SERVER

可以在mysqld启动时通过—character-set-server指定字符集(latin1为默认字符集)，可调用cmake更改默认字符集；

Mysql启动后可动态调整character_set_server/collation_server以修改字符集

创建数据库若不指定字符集则使用服务器默认字符集

Database

可以在create database指定，也可以通过alter database修改,对应参数为character_set_database/collation_database

所有的数据库选项都存放在db.opt文本文件中

创建表时若不指定字符集则使用服务器默认字符集

Load data infile也须使用数据库字符集

Table

可以在create table指定，也可以通过alter table修改

Column

可以在create table时指定，也可以通过alter table modify column修改

Connection character sets and collations

当mysql客户端连接服务器并发送请求时，其字符集会发生如下转换：

Mysql服务器使用character_set_client作为从客户端传来数据的字符集，然后将其转化为character_set_connection(使用introducer的除外)进行处理，完毕后将结果集以character_set_results为字符集发送回给客户端；

客户端也可以通过一系列参数为数据库连接定制字符集

Set names ‘charset_name’ [collate ‘collation_name’]

通过此语句可以为character_set_client/character_set_results/character_set_connection设置为同一个值，这样客户端在和服务器交互的时候就不会发生额外的字符集转换

Set character set charset_name

与set names类似，区别在于会将character_set_connection/collation_connection设置为character_set_database/collation_database；

Set character set x与以下语句等价

Set character_set_client = X

Set character_set_results = X

Set collation_connection = @@collation_database—该语句会隐式执行set character_set_connection = @@character_set_database

常见的Mysql客户端有mysql/mysqladmin/mysqlcheck/mysqlimport/mysqlshow

1缺省情况下，使用默认编译好的字符集(如latin1)

2若OS已设置了locale，则将其作为默认字符集；如LANG为ru_RU.KOI8-R，则使用koi8r作为默认字符集

3这些客户端均支持--default-character-set以覆盖默认选项，当其连接服务器后会执行set names

假设column1定义为char(5) character set latin2，在没有设置set names/set character set的前提下，客户端执行select column1 from t，客户端连接时定义了何种字符集，服务器就会将结果以该字符集返回给客户端；若在运行sql前执行了set names‘latin1’/set character set latin1，服务器会将结果集从latin2转换为latin1然后返回给客户端；

可将character_set_results设为null或binary以避免服务器做任何字符转换

Character string literal

每个字符串都有对应的字符集(introducer)和collation，其语法如下

[_charset_name] ‘string’ [collate collation_name]

_charset_name被称作introducer，决定其后的string使用的字符集，但并不像convert()函数那样改变字符串的实际值

Select‘string’—该字符串的字符集和collation由character_set_connection/collation_connection决定

对于转义字符诸如\n，则还是由character_set_connection参数解析

An introducer indicates the character set for the following string, but does not change now how the parser performs escape processing within the string. Escapes are always interpreted by the parser according to the character set given by character_set_connection

--尽管第二个字符串明确指定了字符集sjis，转义字符\n还是按照latin1解析的

mysql> SET NAMES latin1;

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT HEX('à\n'), HEX(_sjis'à\n');

+------------+-----------------+

| HEX('à\n') | HEX(_sjis'à\n') |

+------------+-----------------+

| E00A| E00A|

+------------+-----------------+

1 row in set (0.00 sec)

National character set

Standard sql规定使用nchar/national char作为char列的预定义字符集，其中mysql 5.5采用的是utf8，也就是说以下定义都是等价的

Char(10) character set utf8

National char(10)

Nchar(10)

也可以使用N'literal'创建一个使用national character set的字符串，如下语句都是等价的

Select N’string’/select n’string’/select _utf8’string’

Metadata character set

统一使用UTF-8为metadata的字符集，可通过参数character_set_system查阅

Mysql 5.5使用UTF-8作为error message的默认字符集，返回给客户端之前将其转换为character_set_results指定的字符集，若该参数为null/binary，则不进行转换

Repertoire

--字符集里的所有字符，分为ASCII和UNICODE

ASCII包含字符从U+0000到U+007F

UNICODE字符范围为U+0000–U+FFFF

ASCII为UNICODE的子集，除了swe7外mysql的所有字符集都是ASCII的超集

repertoire可以使表达式进行正常的字符集转换，否则mysql将会抱错

比如

CREATE TABLE t1 (

c1 CHAR(1) CHARACTER SET latin1,

c2 CHAR(1) CHARACTER SET ascii

);

INSERT INTO t1 VALUES ('a','b');

SELECT CONCAT(c1,c2) FROM t1---若没有repertoire，该操作将会抱错ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (ascii_general_ci,IMPLICIT) for operation 'concat'

Unicode

5.5之前支持两种版本的unicode字符集

Ucs2–每个字符使用16bit

Utf8–每个字符使用1到3个bytes

5.5起新增utf16/utf32/utf8mb4

--usc2/utf16/utf32不适用于set names