在mysql5.5中,可以通過(guò)“SHOW VARIABLES LIKE ‘character%’;”語(yǔ)句來(lái)查詢字符編碼,可以顯示MySQL客戶端使用的字符集、連接數(shù)據(jù)庫(kù)時(shí)使用的字符集、創(chuàng)建數(shù)據(jù)庫(kù)使用的字符集、數(shù)據(jù)庫(kù)系統(tǒng)使用的字符集等等。
(推薦教程:mysql視頻教程)
字符(Character)是計(jì)算機(jī)中字母、數(shù)字、符號(hào)的統(tǒng)稱,一個(gè)字符可以是一個(gè)中文漢字、一個(gè)英文字母、一個(gè)阿拉伯?dāng)?shù)字、一個(gè)標(biāo)點(diǎn)符號(hào)等。
計(jì)算機(jī)是以二進(jìn)制的形式來(lái)存儲(chǔ)數(shù)據(jù)的。平時(shí)我們?cè)陲@示器上看到的數(shù)字、英文、標(biāo)點(diǎn)符號(hào)、漢字等字符都是二進(jìn)制數(shù)轉(zhuǎn)換之后的結(jié)果。
字符集(Character set)定義了字符和二進(jìn)制的對(duì)應(yīng)關(guān)系,為字符分配了唯一的編號(hào)。常見的字符集有 ASCII、GBK、ios-8859-1 等。
字符編碼(Character encoding)也可以稱為字集碼,規(guī)定了如何將字符的編號(hào)存儲(chǔ)到計(jì)算機(jī)中。
大部分字符集都只對(duì)應(yīng)一種字符編碼,例如:ASCII、IOS-8859-1、GB2312、GBK,都是既表示了字符集又表示了對(duì)應(yīng)的字符編碼。所以一般情況下,可以將兩者視為同義詞。Unicode 字符集除外,Unicode 有三種編碼方案,即 UTF-8、UTF-16 和 UTF-32。最為常用的是 UTF-8 編碼。
在 MySQL 中,可以通過(guò)SHOW VARIABLES LIKE ‘character%’;命令查看當(dāng)前 MySQL 使用的字符集,命令和運(yùn)行結(jié)果如下:
mysql>?SHOW?VARIABLES?LIKE?'character%'; +--------------------------+---------------------------------------------------------+ |?Variable_name????????????|?Value???????????????????????????????????????????????????| +--------------------------+---------------------------------------------------------+ |?character_set_client?????|?gbk?????????????????????????????????????????????????????| |?character_set_connection?|?gbk?????????????????????????????????????????????????????| |?character_set_database???|?latin1??????????????????????????????????????????????????| |?character_set_filesystem?|?binary??????????????????????????????????????????????????| |?character_set_results????|?gbk?????????????????????????????????????????????????????| |?character_set_server?????|?latin1??????????????????????????????????????????????????| |?character_set_system?????|?utf8????????????????????????????????????????????????????| |?character_sets_dir???????|?C:Program?FilesMySQLMySQL?Server?5.7sharecharsets?| +--------------------------+---------------------------------------------------------+ 8?rows?in?set,?1?warning?(0.01?sec)
上述運(yùn)行結(jié)果說(shuō)明如下表所示:
名稱 | 說(shuō)明 |
---|---|
character_set_client | MySQL 客戶端使用的字符集 |
character_set_connection | 連接數(shù)據(jù)庫(kù)時(shí)使用的字符集 |
character_set_database | 創(chuàng)建數(shù)據(jù)庫(kù)使用的字符集 |
character_set_filesystem | MySQL 服務(wù)器文件系統(tǒng)使用的字符集,默認(rèn)值為 binary,不做任何轉(zhuǎn)換 |
character_set_results | 數(shù)據(jù)庫(kù)給客戶端返回?cái)?shù)據(jù)時(shí)使用的字符集 |
character_set_server | MySQL 服務(wù)器使用的字符集,建議由系統(tǒng)自己管理,不要人為定義 |
character_set_system | 數(shù)據(jù)庫(kù)系統(tǒng)使用的字符集,默認(rèn)值為 utf8,不需要設(shè)置 |
character_sets_dir | 字符集的安裝目錄 |
亂碼時(shí),不需要關(guān)心 character_set_filesystem、character_set_system 和 character_sets_dir 這 3 個(gè)系統(tǒng)變量,它們不會(huì)影響亂碼?。
在 MySQL 中,查看可用字符集的命令和執(zhí)行過(guò)程如下:
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.02?sec)
其中:
-
第一列(Charset)為字符集名稱;
-
第二列(Description)為字符集描述;
-
第三列(Default collation)為字符集的默認(rèn)校對(duì)規(guī)則;
-
第四列(Maxlen)表示字符集中一個(gè)字符占用的最大字節(jié)數(shù)。
常用的字符集如下:
-
latin1 支持西歐字符、希臘字符等。
-
gbk 支持中文簡(jiǎn)體字符。
-
big5 支持中文繁體字符。
-
utf8 幾乎支持所有國(guó)家的字符。