mysql字元集

標籤：

mysql字元集 字元集基礎字元集：資料庫中的字元集包含兩層含義

1. 各種文字和符號的集合，包括各國家文字，標點符號，圖形符號，數字等。
2. 字元的編碼方式，即位元據與字元的映射規則；

 字元集分類：

• ASCII：美國資訊互換標準編碼；英語和其他西歐語言；單位元組編碼，7位（bits）表示一個字元，共128字元。
• GBK：漢字內碼擴充規範；中日韓漢字、英文、數字；雙位元組編碼；共收錄了21003個漢字，GB2312的擴充。
• utf-8：Unicode標準的可變長度字元編碼；Unicode標準（統一碼），業界統一標準，包含世界上數十種文字的系統；utf-8使用一至4個位元組為每一個字元編碼。
• 其他常見字元集：utf-32，utf-16，big5(繁體字),latin1（）

 mysql字元集：查看字元集：

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 |

 

新增字元集：

在編譯mysql時用--with-charset=gbk 來新增字元集  字元集與字元序（字元排序的規則）charset 和 collation 字元集與字元序是一對多的關係，但一個字元集至少有一個字元序 collation：字元序，字元的排序與比較規則，每個字元集都有對應的多套字元序。 不同的字元序決定了字串在比較排序中的精度和效能不同。 查看字元序

mysql> SHOW COLLATION ;

 mysql的字元序遵從命名慣例：

• 以_ci（表示大小寫不敏感）
• 以_cs（表示大小寫敏感）
• 以_bin(表示用編碼值進行比較)

 字元集順位charset 和 collation的順位：伺服器級>>資料庫級>>表級>>列級 伺服器級系統變數（可動態設定）：--character_set_server:預設的內部操作字元集--character_set_system:系統中繼資料（欄位，表名等）字元集 設定：

• 使用命令直接設定
• 設定檔中設定：mysqld,

  資料庫級（資料庫中儲存資料的預設字元集）

create database db_name character set latin1 collate latin1_swedish_ci;

-character_set_database:當前選中資料庫的預設字元集主要影響load data 等語句的預設字元集；create database的字元集如果不設定，預設使用character_set_server的字元集。 表級

mysql>create table tbl(...) default charset=utf-8 default collate=utf8_bin

 

資料存放區字元集使用規則：

• 使用列集的character set 設定值
• 若列級字元集不存在，則使用對應表級的default character set 設定值；
• 若表級字元集不存在，則使用資料庫級的default character set 設定值
• 若資料庫字元集不存在，則使用伺服器級character_set_server設定值

  實踐 查看字元集show [global] variables like ‘character%‘show [global] variables like ‘collation%‘ 

mysql> SHOW VARIABLES LIKE ‘%CHARACTER%‘;+--------------------------+----------------------------+| 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       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE ‘%collation%‘;+----------------------+-------------------+| Variable_name        | Value             |+----------------------+-------------------+| collation_connection | utf8_general_ci   || collation_database   | latin1_swedish_ci || collation_server     | latin1_swedish_ci |+----------------------+-------------------+

 修改字元集 修改伺服器級字元集

set global character_set_server=utf8; (全域)

修改表級字元集alter table tbl convert to character set XXX;(表)

mysql> alter table stu convert to character set utf8;

 

用戶端串連與字元集串連與字元集-character_set_client:用戶端來來源資料使用的字元集（用戶端程式發過來的SQL用什麼來編碼的）-character_set_conection:串連層字元集（做中介層轉換）-character_set_results:查詢結果字元集（返回給用戶端程式用的字元集） 一般可以統一設定(推薦)：

msyql>set  names utf8;

也可以統一叫做串連字元集；  在設定檔中：default-character-set = utf8 用戶端串連字元集

 

常見亂碼原因：

• 資料存放區字元集不能正確編碼（不支援）client發來的資料：client（utf8）->storage(latin1)
• 程式串連使用的字元集與通知mysql的character_set_client，character_set_conection，character_set_results 等不一致或不相容。（告訴mysql：set names gbk ;程式串連使用的字元集一致）

  1.甚至表本身utf8,而設定 set names gbk, 程式串連也使用 gbk;此時向表中插入漢字，依然可以顯示而並不亂碼,因為雖然程式串連使用gbk編碼，mysql內部會將gbk轉換成utf8來顯示； 2.表utf8,set names utf8,但是 程式串連使用 gbk,此時向表中插入漢字，是亂碼的。  load data 出現亂碼：

aiapple@ubuntu:~$ file test.ttest.t: UTF-8 Unicode textaiapple@ubuntu:~$ cat test.t你好 mysql> show variables like ‘%char%‘;+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                         || character_set_connection | utf8                    || character_set_database   | gbk                   || character_set_filesystem | binary                   || character_set_results    | utf8                        || character_set_server     | utf8                        || character_set_system     | utf8                       || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec) mysql>load data infile ‘/home/aiapple/test.t‘ into table t;

此時會出現亂碼，即告知資料庫的character_set_database   的字元集與程式使用的檔案test.t 不一樣；

更改set character_set_database   =   utf8 則能正常顯示；這裡與 普通告知mysql字元集 set names xxx 區別開來，畢竟是匯入表到一個 資料庫當中，應該使資料庫的字元集與檔案的字元集相同  用戶端串連與字元集使用建議：

• 建立資料庫/表時顯示的指定字元集，不使用預設
• 串連字元集與資料存放區字元集設定一致，推薦使用utf8
• 驅動程式串連時顯示指定字元集（set names xxx）

 用戶端的設定： 

 

 三要素：１.程式驅動或用戶端的字元集(在用戶端設定)２.告知mysql的字元集（set names xxx） 3.資料存放區的字元集（表結構的字元集alter table tbl convert to character set xxx） 前兩個必須一致才不會出現亂碼，推薦三個都設定成一致 需求：在系統運行了一段時間，有了一定的資料，後發現字元集不能滿足要求需要重新修改，又不想丟棄這段時間的資料。alter database character set xxx 或者alter table  tablename character set xxx；這兩條命令只對想建立的表或者記錄生效 方法：先將資料匯出，經過適當的調整重新匯入才可完成。以下類比的是將latin1字元集的資料庫修改成GBK字元集的資料庫的過程1）匯出表結構

mysqldump -uroot -p --default-character-set=gbk -d WY_yun >createtab.sq

--default-character-set表示設定以什麼字元集串連；-d表示只匯出表結構，不匯出資料 2）手動修改creatatab.sql中表結構定義中的字元集為新的字元集 3）確保記錄不在更新，匯出所有記錄

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 WY_yun > data.sql

-quick：該選項用於轉儲存大的表。它強制mysqldump 從伺服器一次一行地檢索表中的行，而不是檢索所有的行，並在輸出前將它緩衝到記憶體中。--no-create-info：不寫重新建立麼個轉儲存表的create table 語句--extended-insert：使用包括幾個VALUES列表的多行insert文法。這樣使轉隱藏檔更小，重載檔案時可以加速插入--default-character-set=latin1:按照原有的字元集匯出所有資料，這樣匯出的檔案中，所有中文都是可見的，不會儲存成亂碼 4）修改資料的字元集為新字元集---開啟data.sql，將set names latin1 修改成 set names gbk 5)使用新得字元集建立新的資料庫

mysql> create database na default charset gbk;

 6）將新字元集的表結構匯入新庫；建立表，執行createtab.sql

mysql -uroot -p na <createtab.sql

 7) 將新字元集的資料檔案匯入新庫；匯入資料，執行data.sql

 mysql -uroot -p na < data.sql

似乎實驗沒有成功可以去提問？

總結

 

 

• 字元集與字元序是1:N
• 字元序是字元的排序與比較規則及字元的精度和效能；
• 表層級字元集修改：alter table stu convert to character set utf8;
• 三個串連相關字元集統一設定：set names utf8;
• 常見亂碼原因
• load資料時，因讓資料庫層級的字元集與檔案相同；
• 三要素：
  • 　　程式驅動或用戶端的字元集(在用戶端設定)
  • 　　告知mysql的字元集（set names xxx）
  • 　　資料存放區的字元集（表結構的字元集alter table tbl convert to character set xxx）
• 運行一段時間後探索資料亂碼，如何儲存資料；

  

mysql字元集