|
一、字符集和校驗規則
字符集是一套符合和編碼，校驗規則（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 collation_connection=@@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格式顯示中文的。
|
