MySQL的字符集

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

張北網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián),張北網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為張北近1000家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設(shè)要多少錢(qián)，請(qǐng)找那個(gè)售后服務(wù)好的張北做網(wǎng)站的公司定做！

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ù)庫(kù)如果不顯式的指定字符集相關(guān)參數(shù)，將使用這些參數(shù)。也叫做全局字符集變量

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

各個(gè)變量的概念：

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

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

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

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

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

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

1，在編譯時(shí)指定，主要是：

????-DDEFAULT_CHARSET=utf8 \

????-DDEFAULT_COLLATION=utf8-general_ci \

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

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

????character_set_server=utf8

????collation_server=utf8_general_ci

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

3，啟動(dòng)MySQL服務(wù)的時(shí)候指定：

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

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

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

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

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

2，存儲(chǔ)時(shí)使用的字符集，分為4個(gè)級(jí)別

????1）SERVER，全局級(jí)別

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

????3）TABLE，表級(jí)別

????4）column，列級(jí)別

2# MySQL查詢(xún)的基本過(guò)程和亂碼的形成以及如何避免亂碼

????1）查詢(xún)的基本過(guò)程

????? ? MySQL查詢(xún)基本過(guò)程如下：

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

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

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

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

????

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

實(shí)際上是因?yàn)镺S/APP層已經(jīng)將漢字轉(zhuǎn)換為單字符串的形式。一般來(lái)說(shuō)，為了正常顯示和處理漢字，

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

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

???這些字符集的轉(zhuǎn)換，實(shí)際上是字符長(zhǎng)度的轉(zhuǎn)換。比如如果CLIENT端時(shí)LATIN1，connection是UTF8，
???那么就會(huì)發(fā)生3個(gè)字節(jié)長(zhǎng)度轉(zhuǎn)換成6個(gè)字節(jié)長(zhǎng)度。每個(gè)單字符后面都會(huì)被填0，這樣變長(zhǎng)了自然是沒(méi)事的，
???頂多顯示的時(shí)候再轉(zhuǎn)回來(lái)，把后面填的零都切掉。但是反過(guò)來(lái)，如果是client段是utf8，connection是latin1，
???那么3個(gè)字節(jié)長(zhǎng)度的utf8就會(huì)被轉(zhuǎn)成1個(gè)字節(jié)長(zhǎng)度的latin1，后兩位丟失，以后是怎么都變不回來(lái)的。
???這種丟失非零位的過(guò)程是不可逆的。所以我們必須得保證設(shè)定字符集的時(shí)候
???（列字符集、表字符集、數(shù)據(jù)庫(kù)字符集、server段字符集）>=?connection字符集?>=?client字符集來(lái)避免字符編碼丟失問(wèn)題。
???一種可能發(fā)生的狀況：??client(latin1)==>connection(utf8)==>服務(wù)端內(nèi)部的存儲(chǔ)時(shí)字符集（latin1），
???并不會(huì)發(fā)生字符編碼丟失，因?yàn)閏onnection轉(zhuǎn)換到存儲(chǔ)時(shí)字符集時(shí)，只是切掉了client轉(zhuǎn)connection時(shí)后面填的零，相當(dāng)于轉(zhuǎn)回來(lái)了。
???
??3，實(shí)驗(yàn)：
??????1）client字符集為utf8,connection?為latin1，存儲(chǔ)字符集為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,'中國(guó)');
????????????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是沒(méi)有用的。
????????????(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，存儲(chǔ)字符集為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,'中國(guó)');
????????????ERROR?1366?(HY000):?Incorrect?string?value:?'\xE4\xB8\xAD\xE5\x9B\xBD'?for?column?'name'?at?row?1
????????????(root@localhost)[sample3]>??
????????????這里直接報(bào)錯(cuò)，5.6以后加強(qiáng)了數(shù)據(jù)庫(kù)數(shù)據(jù)的安全性，因?yàn)闀?huì)丟失數(shù)據(jù)，所以不允許插入。

????3）client字符集為latin1，connection為utf8，存儲(chǔ)字符集為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)?
????????????亂碼了，這里為何會(huì)亂碼呢？按理說(shuō)轉(zhuǎn)換過(guò)程中只會(huì)切掉填充的零才對(duì)。實(shí)際上是result的問(wèn)題。這個(gè)result是utf8，
????????????而client存的時(shí)候就是3位，到connection轉(zhuǎn)到6位，到存儲(chǔ)時(shí)轉(zhuǎn)回3位，這時(shí)候到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?|?中國(guó)???|
????????????+------+--------+
????????????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('中國(guó)');
????????????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???|
????????????+--------+
????????????|?中國(guó)???|
????????????+--------+
????????????1?row?in?set?(0.00?sec)
????????????(root@localhost)[sample2]>
????????????
????????????
?修改客戶(hù)端字符集的5中方法：
?
?1、?運(yùn)行，set?names?<字符集>；
?2、?在SQL文件中指定set?names?<字符集>；,用source命令導(dǎo)入sql文件
?????如：?mysql>?source?test.sql
?3、??在SQL文件中指定set?names?<字符集>；,然后通過(guò)重定向符，或者-e參數(shù)來(lái)執(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、?通過(guò)指定mysql命令的字符集參數(shù)實(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è)置客戶(hù)端即服務(wù)器端相關(guān)參數(shù)，此設(shè)置永久生效，
??????[client]
??????default-character-set=utf8
??????###對(duì)于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)會(huì)跟隨系統(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??##加入忽略客戶(hù)端設(shè)置，使用服務(wù)端設(shè)置
[mysql]
default-character-set=utf8
這樣設(shè)置以后，mysql客戶(hù)端字符集鎖定為utf8

分享文章：MySQL的字符集
本文來(lái)源：http://bm7419.com/article22/jdohjc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、自適應(yīng)網(wǎng)站、企業(yè)建站、定制開(kāi)發(fā)、面包屑導(dǎo)航、微信公眾號(hào)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)