MySQL字元集

標籤：variable   mysq   生效   檔案中   不能   illegal   amp   完全   適應   

一、字元集和校正規則

字元集是一套符合和編碼，校正規則（collation）是在字元集內用於比較字元的一套規則，即字元集的定序。MySQL可以使用對種字元集和檢驗規則來組織字元。

MySQL伺服器可以支援多種字元集，在同一台伺服器，同一個資料庫，甚至同一個表的不同欄位都可以指定使用不同的字元集，相比oracle等其他資料庫管理系統，在同一個資料庫只能使用相同的字元集，MySQL明顯存在更大的靈活性。

每種字元集都可能有多種校對規則，並且都有一個預設的校對規則，並且每個校對規則只是針對某個字元集，和其他的字元集麼有關係。

在MySQL中，字元集的概念和編碼方案被看做是同義字，一個字元集是一個轉換表和一個編碼方案的組合。

Unicode（Universal Code）是一種在電腦上使用的字元編碼。Unicode 是為瞭解決傳統的字元編碼方案的局限而產生的，它為每種語言中的每個字元設定了統一併且唯一的二進位編碼，以滿足跨語言、跨平台進行文本轉換、處理的要求。Unicode存在不同的編碼方案，包括Utf-8，Utf-16和Utf-32。Utf表示Unicode Transformation Format。 二、查看mysql字元集方法 1、查看mysql伺服器支援的字元集

mysql> show character set;

mysql> select * from information_schema.character_sets;

mysql> select character_set_name, default_collate_name, description, maxlen from

information_schema.character_sets; 2、查看字元集的校對規則

mysql> show collation;

mysql> show collation like ‘utf8‘;

mysql> select * from information_schema.collations where collation_name like ‘utf8%‘; 3、查看當前資料庫的字元集

mysql> show variables like ‘character%‘;

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | utf8 |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.00 sec)

名詞解釋：

    character_set_client：用戶端請求資料的字元集
    character_set_connection：客戶機/伺服器串連的字元集
    character_set_database：預設資料庫的字元集，無論預設資料庫如何改變，都是這個字元集；如果沒有預設資料庫，那就使用 character_set_server指定的字元集，這個變數建議由系統自己管理，不要人為定義。
    character_set_filesystem：把os上檔案名稱轉化成此字元集，即把 character_set_client轉換character_set_filesystem， 預設binary是不做任何轉換的

    character_set_results：結果集，返回給用戶端的字元集
    character_set_server：資料庫伺服器的預設字元集
    character_set_system：系統字元集，這個值總是utf8，不需要設定。這個字元集用於資料庫物件（如表和列）的名字，也用於儲存在目錄表中的函數的名字。 4、查看當前資料庫的校對規則

mysql> show variables like ‘collation%‘;

+----------------------+-------------------+

| Variable_name | Value |

+----------------------+-------------------+

| collation_connection | utf8_general_ci |

| collation_database | latin1_swedish_ci |

| collation_server | utf8_general_ci |

+----------------------+-------------------+

3 rows in set (0.01 sec)

名詞解釋：

collation_connection 當前串連的字元集。
collation_database    當前日期的預設校對。每次用USE語句來“跳轉”到另一個資料庫的時候，這個變數的值就會改變。如果沒有當前資料庫，這個變數的值就是collation_server變數的值。
collation_server 伺服器的預設校對。

排序方式的命名規則為：字元集名字_語言_尾碼，其中各個典型尾碼的含義如下：
1）_ci：不區分大小寫排序方式
2）_cs：區分大小寫排序方式
3）_bin：二進位排序方式，大小比較將根據字元編碼，不涉及人類語言，因此_bin的排序方式不包含人類語言 三、MySQL字元集的設定 1、概述

MySQL字元集設定分為兩類：

1）建立對象的預設值。

2）控制server和client端互動通訊的配置。 1、建立對象的預設值

字元集合校對規則有4個層級的預設設定：

1）伺服器層級；

2）資料庫層級；

3）表層級、列層級；

4）串連層級。

更低層級的設定會整合進階別的設定。

這裡有一個通用的規則：先為伺服器或者資料庫選擇一個合理的字元集，然後根據不同的實際情況，讓某個列選擇自己的字元集。 2、控制server和client端互動通訊的配置

大部分MySQL用戶端都不具備同時支援多種字元集的能力，每次都只能使用一種字元集。

客戶和伺服器之間的字元集轉換工作是由如下幾個MySQL系統變數控制的。

1）character_set_server：mysql server預設字元集。

2）character_set_database：資料庫預設字元集。

3）character_set_client：MySQL server假定用戶端發送的查詢使用的字元集。

4）character_set_connection：MySQL Server接收用戶端發布的查詢請求後，將其轉換為character_set_connection變數指定的字元集。

5）character_set_results：mysql server把結果集和錯誤資訊轉換為character_set_results指定的字元集，並發送給用戶端。

6）character_set_system：系統中繼資料(欄位名等)字元集

還有以collation_開頭的同上面對應的變數，用來描述字元序。

注意事項：

? my.cnf中的default_character_set設定隻影響mysql命令串連伺服器時的串連字元集，不會對使用libmysqlclient庫的應用程式產生任何作用！

? 對欄位進行的SQL函數操作通常都是以內部操作字元集進行的，不受串連字元集設定的影響。

? SQL語句中的裸字串會受到串連字元集或introducer設定的影響，對於比較之類的操作可能產生完全不同的結果，需要小心！ 3、預設情況下字元集選擇規則

(1)編譯MySQL 時，指定了一個預設的字元集，這個字元集是 latin1;
(2)安裝MySQL 時，可以在設定檔 (my.cnf) 中指定一個預設的的字元集，如果沒指定，這個值繼承自編譯時間指定的;
(3)啟動mysqld 時，可以在命令列參數中指定一個預設的的字元集，如果沒指定，這個值繼承自設定檔中的配置,此時character_set_server被設定為這個預設的字元集;
(4)當建立一個新的資料庫時，除非明確指定，這個資料庫的字元集被預設設定為character_set_server;
(5)當選定了一個資料庫時，character_set_database被設定為這個資料庫預設的字元集;
(6)在這個資料庫裡建立一張表時，表預設的字元集被設定為character_set_database，也就是這個資料庫預設的字元集;
(7)當在表內設定一欄時，除非明確指定，否則此欄預設的字元集就是表預設的字元集; 2、分述 2.1、為列分配字元集

屬於同一個表的不同列可以有不同的字元集，如果沒有為一個列顯示的定義字元集就使用預設字元集。建立一個表的時候，若顯示的為列指定字元集，則字元集作為資料類型選項包含在其中，要放在資料類型後面及空指定和主鍵前面。

例如：

create table column_charset(

c1 char(10) character set utf8 not null,

c2 char(10) char set utf8,

c3 varchar(10) charset utf8,

c4 varchar(10)) engine=innodb;

注意：character set可以簡寫為char set和charset

使用show create table table_name;命令查看column_charset建表語句：

mysql> show create table column_charset\G;

*************************** 1. row ***************************

Table: column_charset

Create Table: CREATE TABLE `column_charset` (

`c1` char(10) CHARACTER SET utf8 NOT NULL,

`c2` char(10) CHARACTER SET utf8 DEFAULT NULL,

`c3` varchar(10) CHARACTER SET utf8 DEFAULT NULL,

`c4` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

ERROR:

No query specified

插入資料，感受一下效果：

mysql> insert into column_charset(c1,c2,c3,c4) value("圖靈","圖靈","圖靈","chavin");

Query OK, 1 row affected (0.01 sec)

mysql> select * from column_charset;

+--------+--------+--------+--------+

| c1 | c2 | c3 | c4 |

+--------+--------+--------+--------+

| 圖靈 | 圖靈 | 圖靈 | chavin |

+--------+--------+--------+--------+

1 row in set (0.00 sec) 2.2、為表分配字元集

create table table_charset(

c1 varchar(10),

c2 varchar(10))engine=innodb default charset=utf8;

注意：為表指定字元集可以使用以下幾種方式：

default charset=utf8;

charset=utf8;

default character set=utf8;

character set=utf8;

default char set=utf8;

char set=utf8;

檢查建表語句：

mysql> show create table table_charset\G;

*************************** 1. row ***************************

Table: table_charset

Create Table: CREATE TABLE `table_charset` (

`c1` varchar(10) DEFAULT NULL,

`c2` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

測試：

mysql> insert into table_charset(c1,c2) values(‘圖靈‘,‘圖靈‘);

Query OK, 1 row affected (0.01 sec)

mysql> select * from table_charset;

+--------+--------+

| c1 | c2 |

+--------+--------+

| 圖靈 | 圖靈 |

+--------+--------+

1 row in set (0.00 sec) 2.3、為資料庫指定字元集

建立的每個資料庫都有一個預設字元集，如果沒有指定，就用latin1。

create database dbking charset=utf8;

注意：建立資料庫分配字元集可以採用以下幾種子句：

charset=utf8;

default charset=utf8;

charset utf8;

default charset utf8;

char set=utf8;

default char set=utf8;

char set utf8;

default char set utf8;

character set=utf8;

default character set=utf8;

character set utf8;

default character set utf8;

使用show create database db_name;命令查看資料庫建立語句：

mysql> show create database dbking;

+----------+-----------------------------------------------------------------+

| Database | Create Database |

+----------+-----------------------------------------------------------------+

| dbking | CREATE DATABASE `dbking` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+-----------------------------------------------------------------+

1 row in set (0.00 sec) 2.4、為列分配校對規則

每個列都應該有一個校對，如果沒有顯示指定，MySQL就使用屬於該字元集的預設校對。如果指定了一個字元集和一個校對，字元集應該放在前面。

create table column_collate(

c1 varchar(10) charset utf8 collate utf8_romanian_ci not null,

c2 varchar(10) charset utf8 collate utf8_spanish_ci)engine=innodb;

查看錶的校正規則資訊：

mysql> select table_name,column_name,collation_name

from information_schema.columns

where table_name=‘column_collate‘;

+----------------+-------------+------------------+

| table_name | column_name | collation_name |

+----------------+-------------+------------------+

| column_collate | c1 | utf8_romanian_ci |

| column_collate | c2 | utf8_spanish_ci |

+----------------+-------------+------------------+

2 rows in set (0.04 sec)

注意：字元集和校對在處理字元運算式的過程中扮演著重要角色。我們不能比較兩個屬於不同校對的不同字元值。例如：

mysql> insert into column_collate(c1,c2) values(‘A‘,‘A‘);

Query OK, 1 row affected (0.22 sec)

mysql> select * from column_collate;

+----+------+

| c1 | c2 |

+----+------+

| A | A |

+----+------+

1 row in set (0.00 sec)

mysql> select * from column_collate where c1=c2;

ERROR 1267 (HY000): Illegal mix of collations (utf8_romanian_ci,IMPLICIT) and (utf8_spanish_ci,IMPLICIT) for operation ‘=‘ 2.5、為表指定校對規則

create table table_collate(

c1 varchar(10),

c2 varchar(10))engine=innodb default charset utf8 collate utf8_romanian_ci;

檢查表的校對規則：

mysql> select table_name,column_name,collation_name from information_schema.columns where table_name=‘table_collate‘;

+---------------+-------------+------------------+

| table_name | column_name | collation_name |

+---------------+-------------+------------------+

| table_collate | c1 | utf8_romanian_ci |

| table_collate | c2 | utf8_romanian_ci |

+---------------+-------------+------------------+

2 rows in set (0.00 sec) 2.6、為資料庫指定校對規則

create database dbking102 default charset utf8 collate utf8_romanian_ci;

查看資料庫定義語句：

mysql> show create database dbking102\G;

*************************** 1. row ***************************

Database: dbking102

Create Database: CREATE DATABASE `dbking102` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_romanian_ci */

1 row in set (0.00 sec) 2.7、字元直接量字元集

如果沒有顯示指定，那麼字元直接量的字元集就是資料庫的預設字元集。如果要顯示分配另一個字元集，需要把字元集的名字放在直接量前面，並且要在字元集前面加上底線。

mysql> select _utf8‘語言 Language 言語 язык‘;

+---------------------------------+

| 語言 Language 言語 язык     |

+---------------------------------+

| 語言 Language 言語 язык     |

+---------------------------------+   

2.8、修改和設定MySQL伺服器層級字元集

MySQL伺服器支援眾多不同的字元集，這類字元集可在編譯時間和運行時指定。    1） 編譯時間指定

編譯時間可指定預設字元集和預設校對規則，要想同時更改預設字元集和校對規則，要同時使用--with-charset和--with-collation選項。校對規則必須是字元集的合法校對規則。

./configure -- with-charset=CHARSET --with-collation=COLLATION

通過configure選項--with-extra-charsets=LIST，可以定義在伺服器中再定義增加字元集。LIST 指下面任何一項：
    a.空格間隔的一系列字元集名
    b.complex -，以包括不能動態裝載的所有字元集
    c.all –，以將所有字元集包括進二進位

./configure -- with-charset=CHARSET --with-collation=COLLATION --with-extra-charsets=all    2） 在參數檔案my.cnf中指定

[mysqld]

character_set_server=utf8

    --影響參數：character_set_server 和 character_set_database

    --注意：修改後要重啟資料庫才會生效。

[client]

default-character-set=utf8

    --影響參數：character_set_client，character_set_connection 和character_set_results。

    --注意：修改後無需重啟資料庫。
3） 在啟動參數前指定

./mysqld --character-set-server=utf8 &

    --影響參數：character_set_server 和 character_set_database
4）在mysql用戶端登陸時通過--default-character-set指定

mysql -uroot -pmysql --default-character-set=utf8

--影響參數：set character_set_client，set character_set_connection，set character_set_results。    5）臨時指定

a）分別指定

mysql> SET character_set_client = utf8;

mysql> SET character_set_connection = utf8;

mysql> SET character_set_database = utf8;

mysql> SET character_set_results = utf8;

mysql> SET character_set_server = utf8;

b）mysql用戶端使用：set names utf8;

等同於

set character_set_client=utf8;

set character_set_connection=utf8;

set character_set_results=utf8;

c）set character set utf8;

等同於

set character_set_client=utf8;

set character_set_results=utf8;

set [email protected]@collation_database;3、總結

下面介紹下幾個MYSQL命令：
1）show character set;或show char set;
查看資料庫支援的所有字元集
    2）status;或\s;
查看目前狀態 裡麵包括當然的字元集設定
    3）show variables like ‘char%‘;
查看系統字元集設定，包括所有的字元集設定
    4）show table status from sqlstudy like ‘%countries%‘;
查看sqlstudy資料庫中表的字元集設定
    5）show full columns from countries;
查看錶列的字元集設定，關鍵是在同一個表中，每列可以設定成不同的字元集
知道怎麼查看字元集了，下面我來說下如何設定這些字元集
    1.修改伺服器級
        a. 臨時更改：
            mysql>SET GLOBAL character_set_server=utf8;
       b. 永久更改：
修改my.cnf檔案
          [mysqld]
          character-set-server=utf8
   2.修改資料庫級
         a. 臨時更改：
             mysql>SET GLOBAL character_set_database=utf8;
        b. 永久更改：
改了伺服器級就可以了
  3.修改表級
         mysql>ALTER TABLE table_name DEFAULT CHARSET utf8;
更改了後永久生效
  4.修改列級
修改樣本：
         mysql>alter table `products` change `products_model` `products_model` varchar( 20 )
        character set  utf8 collate utf8_general_ci null default null;
更改了後永久生效
     5.更改串連字元集
          a. 臨時更改：
              mysql> set names utf8;
         b. 永久更改：
修改my.cnf檔案
在[client]中增加：
              default-character-set=utf8
執行SQL語句時資訊的路徑是這樣的
資訊輸入路徑：client→connection→server;
資訊輸出路徑：server→connection→results.四、MySQL資料庫中字元集轉換流程

1、MySQL Server收到請求時將請求資料從character_set_client轉換為character_set_connection；
2、進行內部操作前將請求資料從character_set_connection轉換為內部操作字元集，其確定方法如下：
使用每個資料欄位的CHARACTER SET設定值；
若上述值不存在，則使用對應資料表的DEFAULT CHARACTER SET設定值(MySQL擴充，非SQL標準)；
若上述值不存在，則使用對應資料庫的DEFAULT CHARACTER SET設定值；
若上述值不存在，則使用character_set_server設定值。
    3、將操作結果從內部操作字元集轉換為character_set_results。

源自於《高效能MySQL》中關於字元集轉換的圖解：

五、MySQL資料庫亂碼原因解析及案例

1、產生亂碼的根本原因
    1）客戶機沒有正確地設定client字元集，導致原先的SQL語句被轉換成connection所指字元集，而這種轉換，是會丟失資訊的，如果client是utf8格式，那麼如果轉換成gb2312格式，這其中必定會丟失資訊，反之則不會丟失。一定要保證connection的字元集大於client字元集才能保證轉換不丟失資訊。
    2）資料庫字型沒有設定正確，如果資料庫字型設定不正確，那麼connection字元集轉換成database字元集照樣丟失編碼，原因跟上面一樣。2、亂碼或資料丟失

    character_set_client:我們要告訴伺服器，我給你發送的資料是什麼編碼？
    character_set_connection:告訴字元集轉換器，轉換成什麼編碼？
    character_set_results:查詢的結果用什麼編碼？
如果以上三者都為字元集N,可簡寫為set names ‘N‘;2.1 亂碼問題

類比情景1：

向預設字元集為utf8的資料表插入utf8編碼的資料前串連字元集設定為latin1，查詢時設定串連字元集為utf8。

插入時根據MySQL伺服器的預設設定，character_set_client、character_set_connection和character_set_results均為latin1；
插入操作的資料將經過latin1=>latin1=>utf8的字元集轉換過程，這一過程中每個插入的漢字都會從原始的3個位元組變成6個位元組儲存；
查詢時的結果將經過utf8=>utf8的字元集轉換過程，將儲存的6個位元組原封不動返回，產生亂碼……

例如：

mysql> set names latin1;

mysql> create table temp(name varchar(10)) charset utf8;

mysql> insert into temp values(‘中國‘);

mysql> select * from temp;

+--------+

| name |

+--------+

| 中國 |

+--------+

mysql> set names utf8;

mysql> select * from temp;

+---------------+

| name |

+---------------+

| ??­??? |

+---------------+

注意：儲存字元集編碼比插入時字元集大時，如果原封不動返回資料會出現亂碼，不過可通過修改查詢字元集，避免亂碼，即不會遺失資料。2.2 資料丟失問題

類比情景1：

向預設字元集為latin1的資料表插入utf8編碼的資料前設定了串連字元集為utf8
插入時根據串連字元集設定，character_set_client、character_set_connection和character_set_results均為utf8；
插入資料將經過utf8=>utf8=>latin1的字元集轉換，若未經處理資料中含有\u0000~\u00ff範圍以外的Unicode字 符，會因為無法在latin1字元集中表示而被轉換為“?”(0×3F)符號，以後查詢時不管串連字元集設定如何都無法恢複其內容了。

例如：

mysql> set names utf8;

mysql> create table temp(name varchar(10)) charset latin1;

mysql> insert into temp values(‘中國‘);

mysql> select * from temp;

+------+

| name |

+------+

| ?? |

+------+

mysql> set names latin1;

mysql> select * from temp;

+------+

| name |

+------+

| ?? |

+------+

資料不完整了，且無法恢複。3、 亂碼終極解決方案

        1）首先要明確你的用戶端時候何種編碼格式，這是最重要的（IE6一般用utf8，命令列一般是gbk，一般程式是gb2312)
        2）確保你的資料庫使用utf8格式，很簡單，所有編碼通吃。
        3）一定要保證connection字元集大於等於client字元集，不然就會資訊丟失，比如： latin1 < gb2312 < gbk < utf8，若設定set character_set_client = gb2312，那麼至少connection的字元集要大於等於gb2312，否則就會丟失資訊
        4）以上三步做正確的話，那麼所有中文都被正確地轉換成utf8格式儲存進了資料庫，為了適應不同的瀏覽器，不同的用戶端，你可以修改character_set_results來以不同的編碼顯示中文字型，由於utf8是大方向，因此web應用是我還是傾向於使用utf8格式顯示中文的。

MySQL字元集