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