MySQL的字符集

#1， 字符集相關(guān)的參數(shù)名和概念? ?

MySQL的字符集設(shè)置比較自由?？梢栽O(shè)置很多種組合，相關(guān)的變量和參數(shù)有： ? ? ?

(root@localhost)[sample3]>?show?global?variables?like?'%cha%';
+-------------------------------+----------------------------+
|?Variable_name?????????????????|?Value??????????????????????|
+-------------------------------+----------------------------+
|?character_set_client??????????|?utf8???????????????????????|
|?character_set_connection??????|?utf8???????????????????????|
|?character_set_database????????|?utf8???????????????????????|
|?character_set_filesystem??????|?binary?????????????????????|
|?character_set_results?????????|?utf8???????????????????????|
|?character_set_server??????????|?utf8???????????????????????|
|?character_set_system??????????|?utf8???????????????????????|
|?character_sets_dir????????????|?/opt/mysql/share/charsets/?|
|?innodb_change_buffer_max_size?|?25?????????????????????????|
|?innodb_change_buffering???????|?all????????????????????????|
+-------------------------------+----------------------------+
10?rows?in?set?(0.00?sec)
(root@localhost)[sample3]>?show?variables?like?'%cha%';
+-------------------------------+----------------------------+
|?Variable_name?????????????????|?Value??????????????????????|
+-------------------------------+----------------------------+
|?character_set_client??????????|?latin1?????????????????????|
|?character_set_connection??????|?latin1?????????????????????|
|?character_set_database????????|?latin1?????????????????????|
|?character_set_filesystem??????|?binary?????????????????????|
|?character_set_results?????????|?latin1?????????????????????|
|?character_set_server??????????|?latin1?????????????????????|
|?character_set_system??????????|?utf8???????????????????????|
|?character_sets_dir????????????|?/opt/mysql/share/charsets/?|
|?innodb_change_buffer_max_size?|?25?????????????????????????|
|?innodb_change_buffering???????|?all????????????????????????|
+-------------------------------+----------------------------+
10?rows?in?set?(0.00?sec)

其中g(shù)lobal variables表示全局變量。也就是默認(rèn)情況下，新建立的數(shù)據(jù)庫如果不顯式的指定字符集相關(guān)參數(shù)，將使用這些參數(shù)。也叫做全局字符集變量

其中variables沒帶global，表示當(dāng)前session生效的的參數(shù)。所謂當(dāng)前session,表示如果更改過相關(guān)的參數(shù)，離開這個session以后，就會恢復(fù)默認(rèn)的參數(shù)。也叫做連接時字符集變量

各個變量的概念：

1，character_set_client:客戶端字符集，即數(shù)據(jù)在client端時字符集狀態(tài)。

2，character_set_connection:連接時轉(zhuǎn)換字符集，即客戶端和服務(wù)端連接時，字符集裝換成的字符集

3，character_set_server:服務(wù)端處理時候使用的字符集

4，character_set_database:數(shù)據(jù)庫層面存儲默認(rèn)使用的字符集

5，character_set_results:數(shù)據(jù)返回時所用的字符集

指定字符集參數(shù)有多種方式，

1，在編譯時指定，主要是：

????-DDEFAULT_CHARSET=utf8 \

????-DDEFAULT_COLLATION=utf8-general_ci \

其中CHARSET是指字符集，COLLATION是指相關(guān)的校對規(guī)則（也稱排序規(guī)則）

2，參數(shù)文件，也就是my.cnf中設(shè)定

????character_set_server=utf8

????collation_server=utf8_general_ci

????參數(shù)人間的設(shè)定將會覆蓋編譯時設(shè)定的字符集和校對規(guī)則。

3，啟動MySQL服務(wù)的時候指定：

????--character_set_server: 指定全局粒度的默認(rèn)字符集

????--collation_server：指定全局粒度的默認(rèn)校對規(guī)則

????啟動時指定的參數(shù)將覆蓋參數(shù)文件以及編譯時指定的字符集和校對規(guī)則。

這些參數(shù)，如果從大方向分的話，可以分為兩類：

1，連接時使用的字符集，即為show variable like '%character%' 顯示的那些字符集

2，存儲時使用的字符集，分為4個級別

????1）SERVER，全局級別

????2）DATABASE，數(shù)據(jù)庫級別

????3）TABLE，表級別

????4）column，列級別

2# MySQL查詢的基本過程和亂碼的形成以及如何避免亂碼

????1）查詢的基本過程

????? ? MySQL查詢基本過程如下：

????? ?1， 程序?qū)⒆址D(zhuǎn)換成二進(jìn)制格式

????? ?2，MySQL 客戶端發(fā)出查詢（client端字符集）====>到達(dá)server端連接器（connection字符集）====>

????????內(nèi)部轉(zhuǎn)換并查詢（列字符集、表字符集、數(shù)據(jù)庫字符集、server端字符集,轉(zhuǎn)換優(yōu)先級逐級遞減）=====>

????????查詢結(jié)果返回給result（result字符集）

????

????????這里先要回答一個latin字符集為何能存放漢字的問題（漢字每個字符占用2個字節(jié)長度，latin不支持雙字節(jié)長度）。

實際上是因為OS/APP層已經(jīng)將漢字轉(zhuǎn)換為單字符串的形式。一般來說，為了正常顯示和處理漢字，

OS層面和程序?qū)用嬉惨欢ㄔO(shè)定了字符集，這個字符集就會將漢字先一步處理成二進(jìn)制。比如OS和程序?qū)用嬖O(shè)置的是UTF8，

那么實際上輸入一個漢字，我們在mysql client端實際上是獲得3個單字節(jié)，而非雙字節(jié)，這樣latin字符集就可以處理了。

???這些字符集的轉(zhuǎn)換，實際上是字符長度的轉(zhuǎn)換。比如如果CLIENT端時LATIN1，connection是UTF8，
???那么就會發(fā)生3個字節(jié)長度轉(zhuǎn)換成6個字節(jié)長度。每個單字符后面都會被填0，這樣變長了自然是沒事的，
???頂多顯示的時候再轉(zhuǎn)回來，把后面填的零都切掉。但是反過來，如果是client段是utf8，connection是latin1，
???那么3個字節(jié)長度的utf8就會被轉(zhuǎn)成1個字節(jié)長度的latin1，后兩位丟失，以后是怎么都變不回來的。
???這種丟失非零位的過程是不可逆的。所以我們必須得保證設(shè)定字符集的時候
???（列字符集、表字符集、數(shù)據(jù)庫字符集、server段字符集）>=?connection字符集?>=?client字符集來避免字符編碼丟失問題。
???一種可能發(fā)生的狀況：??client(latin1)==>connection(utf8)==>服務(wù)端內(nèi)部的存儲時字符集（latin1），
???并不會發(fā)生字符編碼丟失，因為connection轉(zhuǎn)換到存儲時字符集時，只是切掉了client轉(zhuǎn)connection時后面填的零，相當(dāng)于轉(zhuǎn)回來了。
???
??3，實驗：
??????1）client字符集為utf8,connection?為latin1，存儲字符集為latin1
????????????(root@localhost)[sample3]>?show?create?table?test2;
????????????+-------+---------------------------------------------------------------------------------------------------------------------------+
????????????|?Table?|?Create?Table??????????????????????????????????????????????????????????????????????????????????????????????????????????????|
????????????+-------+---------------------------------------------------------------------------------------------------------------------------+
????????????|?test2?|?CREATE?TABLE?`test2`?(
??????????????`id`?int(11)?DEFAULT?NULL,
??????????????`name`?char(20)?DEFAULT?NULL
????????????)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1?|
????????????+-------+---------------------------------------------------------------------------------------------------------------------------+
????????????1?row?in?set?(0.00?sec)?
????????????
????????????(root@localhost)[sample3]>?show?variables?like?'%cha%';
????????????+-------------------------------+----------------------------+
????????????|?Variable_name?????????????????|?Value??????????????????????|
????????????+-------------------------------+----------------------------+
????????????|?character_set_client??????????|?utf8???????????????????????|
????????????|?character_set_connection??????|?latin1?????????????????????|
????????????|?character_set_database????????|?latin1?????????????????????|
????????????|?character_set_filesystem??????|?binary?????????????????????|
????????????|?character_set_results?????????|?utf8???????????????????????|
????????????|?character_set_server??????????|?latin1?????????????????????|
????????????|?character_set_system??????????|?utf8???????????????????????|
????????????|?character_sets_dir????????????|?/opt/mysql/share/charsets/?|
????????????|?innodb_change_buffer_max_size?|?25?????????????????????????|
????????????|?innodb_change_buffering???????|?all????????????????????????|
????????????+-------------------------------+----------------------------+
????????????10?rows?in?set?(0.00?sec)
????????????
????????????(root@localhost)[sample3]>?insert?into?test2?values?(1,'中國');
????????????Query?OK,?1?row?affected,?1?warning?(0.00?sec)
????????????(root@localhost)[sample3]>?select?*?from?test2;
????????????+------+------+
????????????|?id???|?name?|
????????????+------+------+
????????????|????1?|??????|
????????????+------+------+
????????????1?row?in?set?(0.00?sec)
????????????
????????????這種情況，已經(jīng)丟失了字符，轉(zhuǎn)換result是沒有用的。
????????????(root@localhost)[sample3]>?set?character_set_results=latin1;
????????????Query?OK,?0?rows?affected?(0.00?sec)
????????????(root@localhost)[sample3]>?select?*?from?test2;
????????????+------+------+
????????????|?id???|?name?|
????????????+------+------+
????????????|????1?|??????|
????????????+------+------+
????????????1?row?in?set?(0.00?sec)
??????2）client字符集為utf8,connection為utf8，存儲字符集為latin1
????????????(root@localhost)[sample3]>??show?variables?like?'%cha%';
????????????+-------------------------------+----------------------------+
????????????|?Variable_name?????????????????|?Value??????????????????????|
????????????+-------------------------------+----------------------------+
????????????|?character_set_client??????????|?utf8???????????????????????|
????????????|?character_set_connection??????|?utf8???????????????????????|
????????????|?character_set_database????????|?latin1?????????????????????|
????????????|?character_set_filesystem??????|?binary?????????????????????|
????????????|?character_set_results?????????|?utf8???????????????????????|
????????????|?character_set_server??????????|?latin1?????????????????????|
????????????|?character_set_system??????????|?utf8???????????????????????|
????????????|?character_sets_dir????????????|?/opt/mysql/share/charsets/?|
????????????|?innodb_change_buffer_max_size?|?25?????????????????????????|
????????????|?innodb_change_buffering???????|?all????????????????????????|
????????????+-------------------------------+----------------------------+
????????????10?rows?in?set?(0.00?sec)
????????????(root@localhost)[sample3]>???????
????????????(root@localhost)[sample3]>??insert?into?test2?values?(1,'中國');
????????????ERROR?1366?(HY000):?Incorrect?string?value:?'\xE4\xB8\xAD\xE5\x9B\xBD'?for?column?'name'?at?row?1
????????????(root@localhost)[sample3]>??
????????????這里直接報錯，5.6以后加強了數(shù)據(jù)庫數(shù)據(jù)的安全性，因為會丟失數(shù)據(jù)，所以不允許插入。

????3）client字符集為latin1，connection為utf8，存儲字符集為latin1?????????????
????????????(root@localhost)[sample3]>?show?variables?like?'%cha%';
????????????+-------------------------------+----------------------------+
????????????|?Variable_name?????????????????|?Value??????????????????????|
????????????+-------------------------------+----------------------------+
????????????|?character_set_client??????????|?latin1?????????????????????|
????????????|?character_set_connection??????|?utf8???????????????????????|
????????????|?character_set_database????????|?latin1?????????????????????|
????????????|?character_set_filesystem??????|?binary?????????????????????|
????????????|?character_set_results?????????|?utf8???????????????????????|
????????????|?character_set_server??????????|?latin1?????????????????????|
????????????|?character_set_system??????????|?utf8???????????????????????|
????????????|?character_sets_dir????????????|?/opt/mysql/share/charsets/?|
????????????|?innodb_change_buffer_max_size?|?25?????????????????????????|
????????????|?innodb_change_buffering???????|?all????????????????????????|
????????????+-------------------------------+----------------------------+
????????????10?rows?in?set?(0.00?sec)??????
????????????(root@localhost)[sample3]>?select?*?from?test2;
????????????+------+---------------+
????????????|?id???|?name??????????|
????????????+------+---------------+
????????????|????1?|???-???????????|
????????????+------+---------------+
????????????1?row?in?set?(0.00?sec)?
????????????亂碼了，這里為何會亂碼呢？按理說轉(zhuǎn)換過程中只會切掉填充的零才對。實際上是result的問題。這個result是utf8，
????????????而client存的時候就是3位，到connection轉(zhuǎn)到6位，到存儲時轉(zhuǎn)回3位，這時候到result又轉(zhuǎn)到6位，自然是亂碼的。
????????????只要result轉(zhuǎn)回latin1，就可以了。?
????????????(root@localhost)[sample3]>?set?character_set_results=latin1;
????????????Query?OK,?0?rows?affected?(0.00?sec)
????????????(root@localhost)[sample3]>?select?*?from?test2;
????????????+------+--------+
????????????|?id???|?name???|
????????????+------+--------+
????????????|????1?|?中國???|
????????????+------+--------+
????????????1?row?in?set?(0.00?sec)???
??????4)不丟失字符，但是字符集不同的亂碼.
????????????(root@localhost)[sample2]>?show?create?table?test;
????????????+-------+-------------------------------------------------------------------------------------------+
????????????|?Table?|?Create?Table??????????????????????????????????????????????????????????????????????????????|
????????????+-------+-------------------------------------------------------------------------------------------+
????????????|?test??|?CREATE?TABLE?`test`?(
??????????????`name`?char(20)?DEFAULT?NULL
????????????)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?|
????????????+-------+-------------------------------------------------------------------------------------------+
????????????1?row?in?set?(0.00?sec)
???????????????
????????????(root@localhost)[sample2]>??show?variables?like?'%cha%';
????????????+-------------------------------+----------------------------+
????????????|?Variable_name?????????????????|?Value??????????????????????|
????????????+-------------------------------+----------------------------+
????????????|?character_set_client??????????|?utf8???????????????????????|
????????????|?character_set_connection??????|?utf8???????????????????????|
????????????|?character_set_database????????|?utf8???????????????????????|
????????????|?character_set_filesystem??????|?binary?????????????????????|
????????????|?character_set_results?????????|?gbk????????????????????????|
????????????|?character_set_server??????????|?utf8???????????????????????|
????????????|?character_set_system??????????|?utf8???????????????????????|
????????????|?character_sets_dir????????????|?/opt/mysql/share/charsets/?|
????????????|?innodb_change_buffer_max_size?|?25?????????????????????????|
????????????|?innodb_change_buffering???????|?all????????????????????????|
????????????+-------------------------------+----------------------------+
????????????10?rows?in?set?(0.00?sec)
????????????(root@localhost)[sample2]>?insert?into?test?values('中國');
????????????Query?OK,?1?row?affected?(0.01?sec)
????????????(root@localhost)[sample2]>?
????????????(root@localhost)[sample2]>?select?*?from?test;
????????????+------+
????????????|?name?|
????????????+------+
????????????|???|
????????????+------+
????????????1?row?in?set?(0.00?sec)
????????????(root@localhost)[sample2]>?set?character_set_results=utf8;
????????????Query?OK,?0?rows?affected?(0.00?sec)
????????????(root@localhost)[sample2]>?select?*?from?test;
????????????+--------+
????????????|?name???|
????????????+--------+
????????????|?中國???|
????????????+--------+
????????????1?row?in?set?(0.00?sec)
????????????(root@localhost)[sample2]>
????????????
????????????
?修改客戶端字符集的5中方法：
?
?1、?運行，set?names?<字符集>；
?2、?在SQL文件中指定set?names?<字符集>；,用source命令導(dǎo)入sql文件
?????如：?mysql>?source?test.sql
?3、??在SQL文件中指定set?names?<字符集>；,然后通過重定向符，或者-e參數(shù)來執(zhí)行
?????[root@mysql01?3307]#?vi?test.sql
????????set?names?utf8;
????????select?*?from?mysql.user;
?????????
?????[root@mysql01?3307]#?mysql?-uroot?-p<password>?-S?/data/3307/mysql.sock??<test.sql
?????
?????[root@mysql01?3307]#?mysql?-uroot?-p<password>?-S?/data/3307/mysql.sock?-e?"set?names?<字符集>；?select?*?from?<database>.<table>;"?
??4、?通過指定mysql命令的字符集參數(shù)實現(xiàn)?--default-character-set=<字符集>
??????[root@mysql01?3307]#?mysql?-uroot?-p<password>?-S?/data/3307/mysql.sock?--default-chratacter-set=utf8?<database>?<test.sql
??
??5、?在配置文件里設(shè)置客戶端即服務(wù)器端相關(guān)參數(shù)，此設(shè)置永久生效，
??????[client]
??????default-character-set=utf8
??????###對于client參數(shù)，退出重新登錄，即可生效。
??????
??更改服務(wù)端的方法：
??????[mysqld]
??????default-character-set=utf8??####5.1
??????character-set-server=utf8???####5.5
??????
??????
??[root@mysql01?3307]#?mysql?-uroot?-p?-S?/data/3307/mysql.sock??-e?"show?variables?like?'%chara%';"
Warning:?Using?a?password?on?the?command?line?interface?can?be?insecure.
+--------------------------+----------------------------+
|?Variable_name????????????|?Value??????????????????????|
+--------------------------+----------------------------+
|?character_set_client?????|?utf8???????????????????????|
|?character_set_connection?|?utf8???????????????????????|
|?character_set_database???|?utf8???????????????????????|
|?character_set_filesystem?|?binary?????????????????????|
|?character_set_results????|?utf8???????????????????????|
|?character_set_server?????|?utf8???????????????????????|
|?character_set_system?????|?utf8???????????????????????|
|?character_sets_dir???????|?/opt/mysql/share/charsets/?|
+--------------------------+----------------------------+
[root@mysql01?3307]#??

這些參數(shù)中，其中client,connection,results默認(rèn)會跟隨系統(tǒng)的字符集設(shè)置，/etc/systemconfig/i18n



迷思。。。
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

設(shè)置
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
[mysql]
default-character-set=utf8
不能鎖定client端字符集

[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
skip-character-set-client-handshake??##加入忽略客戶端設(shè)置，使用服務(wù)端設(shè)置
[mysql]
default-character-set=utf8
這樣設(shè)置以后，mysql客戶端字符集鎖定為utf8