【MySQL】資料庫字元校對規則

楊奇龍發表於2014-05-11
MySql資料庫字元
一 什麼是字元校對規則
使用MySQL的大多數都知道字符集是一套符號和編碼,而對校驗本規則不太熟悉。校對規則是在字符集內用於比較字元的一套規則,可以控制 select 查詢時where 條件大小寫是否敏感的規則.如欄位 col 在表中的值為 'abc','ABC','AbC' 在不同的校對規則下,where col='ABC'會有不同的結果。
系統常用的字元校對規則
_
校對規則有如下特徵：
a 兩個不同的字符集不能有相同的校對規則。
b 每個字符集有一個預設校對規則。例如,utf8預設校對規則是utf8generalci
c 存在校對規則命名約定:它們以其相關的字符集名開始,通常包括一個語言名比如utf8,並且以ci(大小寫不敏感)、或bin(二元)結束 ,如 utf8_bin。

提示:
官方文件上說是有 cs(大小寫敏感) 但是show collation like 'utf8%'; 並沒有cs結尾的校對規則。

二 如何使用字元校對規則
MySQL 提供四種預設級別的字符集和校驗規則:伺服器級、資料庫級、表級,和連線級,一般欄位級別的不常用。
2.1 伺服器級
MySQL伺服器有一個伺服器字符集和一個伺服器校對規則,collationserver的預設字符集是在編譯mysql的時候編譯好的,比如
shell> ./configure --with-charset=utf8
或者：
shell> ./configure --with-charset=utf8 \
--with-collation=utf8generalci
如要要修改預設的字元校對規則,我們可以通過以下幾種方式:
a 在/etc/my.cnf 的[mysqld]中新增
collationserver = utf8bin
修改之後必須重啟
root@rac2 [(none)]> show variables like 'collation%';
+----------------------+-----------------+
| Variablename | Value |
+----------------------+-----------------+
| collationconnection | utf8generalci |
| collationdatabase | utf8bin |
| collationserver | utf8bin |
+----------------------+-----------------+
3 rows in set (0.00 sec)

b 通過mysqld 命令列新增 --character-set-server=utf8 --collation-server=utf8generalci
/usr/sbin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/home/mysql/data3306/data --log-error=/home/mysql/data3306/log/master-error.log --pid-file=/home/mysql/data3306/data/rac3.pid --socket=/tmp/mysql.sock --port=3306 --character-set-server=utf8 --collation-server=utf8generalci &

注意：這篇文章描述的並不準確
https://dev.mysql.com/doc/refman/5.1/zh/charset.html#charset-column
通過
shell> mysqld --default-character-set=utf8 \
--default-collation=utf8generalci
方式啟動會報錯
140430 9:34:56 InnoDB: 1.1.8 started; log sequence number 1628178
140430 9:34:56 [ERROR] /usr/sbin/mysqld: unknown variable 'default-character-set=utf8'
140430 9:34:56 [ERROR] Aborting

伺服器級別字符集校對規則
root@rac2 [(none)]> show variables like 'collation%';
+----------------------+-----------------+
| Variablename | Value |
+----------------------+-----------------+
| collationconnection | utf8generalci |
| collationdatabase | utf8bin |
| collationserver | utf8_bin |
+----------------------+-----------------+
root@rac2 [dba]> create table t1(col varchar(5)) engine=innodb ;
Query OK, 0 rows affected (0.10 sec)
root@rac2 [dba]> insert into t1 values('abc'),('ABC'),('AbC');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@rac2 [dba]> select * from t1 where col='ABC';
+------+
| col |
+------+
| ABC |
+------+
1 row in set (0.01 sec)
root@rac2 [dba]> create table t2(col varchar(5)) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.11 sec)
root@rac2 [dba]> insert into t2 values('abc'),('ABC'),('AbC'); 
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@rac2 [dba]> select * from t2 where col='ABC';
+------+
| col |
+------+
| abc |
| ABC |
| AbC |
+------+
3 rows in set (0.00 sec)

2.2 資料庫級別字元校對規則
每一個資料庫有一個資料庫字符集和一個資料庫校對規則。CREATE DATABASE和ALTER DATABASE語句有一個可選的子句來指定資料庫字符集和校對規則：
CREATE DATABASE dbname
[[DEFAULT] CHARACTER SET charsetname]
[[DEFAULT] COLLATE collation_name]
通常如果建立資料庫的時候不指定db的字符集和校對規則,則使用伺服器級別預設的校對規則。
如何修改資料庫級別的字元校對規則:
   a 通過在建立資料庫時指定 collationdatabase 字符集。
   b 通過ALTER DATABASE dbname [[DEFAULT] CHARACTER SET charsetname] [[DEFAULT] COLLATE collationname]
注意 在my.cnf 中的[mysql]或者[mysqld]中配置 
collationdatabase=utf8_bin
會分別報錯：
mysql: unknown variable 'collationdatabase=utf8_bin' 
140430 13:56:19 [ERROR] /usr/sbin/mysqld: unknown variable 'collation_database=utf8_bin'
140430 13:56:19 [ERROR] Aborting
例子 
root@rac2 [(none)]> show variables like 'collation%';
+----------------------+-----------------+
| Variablename | Value |
+----------------------+-----------------+
| collationconnection | utf8generalci |
| collationdatabase | utf8bin |
| collationserver | utf8_bin |
+----------------------+-----------------+
3 rows in set (0.00 sec)
root@rac2 [(none)]> create database dba01;
Query OK, 1 row affected (0.00 sec)
root@rac2 [(none)]> use dba01
Database changed
root@rac2 [dba01]> CREATE TABLE t1(col varchar(5)) ;
Query OK, 0 rows affected (0.09 sec)
root@rac2 [dba01]> insert into t1 values('abc'),('ABC'),('AbC');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@rac2 [dba01]> select * from t1;
+------+
| col |
+------+
| abc |
| ABC |
| AbC |
+------+
3 rows in set (0.00 sec)
root@rac2 [dba01]> select * from t1 where col='abc';
+------+
| col |
+------+
| abc |
+------+
1 row in set (0.00 sec)
MySQL這樣選擇資料庫字符集和資料庫校對規則:
  如果指定了CHARACTER SET X和COLLATE Y,那麼採用字符集X和校對規則Y。
  如果指定了CHARACTER SET X而沒有指定COLLATE Y,那麼採用CHARACTER SET X和CHARACTER SET X的預設校對規則。否則,採用伺服器字符集和伺服器校對規則。


2.3 表級別的校對規則
   每一個表有一個表字符集和一個校對規則,為指定表字符集和校對規則,CREATE TABLE 和ALTER TABLE語句有一個可選的子句：
CREATE TABLE tblname (columnlist)
[DEFAULT CHARACTER SET charsetname [COLLATE collationname]]
ALTER TABLE tblname
[DEFAULT CHARACTER SET charsetname] [COLLATE collationname]
例子:
root@rac2 [dba00]> CREATE TABLE t3(col varchar(5)) DEFAULT CHARACTER SET utf8 COLLATE utf8bin ;
Query OK, 0 rows affected (0.12 sec)
root@rac2 [dba00]> insert into t3 values('abc'),('ABC'),('AbC');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@rac2 [dba00]> select * from t3;
+------+
| col |
+------+
| abc |
| ABC |
| AbC |
+------+
3 rows in set (0.00 sec)
root@rac2 [dba00]> select * from t3 where col='abc';
+------+
| col |
+------+
| abc |
+------+
1 row in set (0.01 sec)
MySQL按照下面的方式選擇表字符集和 校對規則：
   如果指定了CHARACTER SET X和COLLATE Y,那麼採用CHARACTER SET X和COLLATE Y。
   如果指定了CHARACTER SET X而沒有指定COLLATE Y,那麼採用CHARACTER SET X和CHARACTER SET X的預設校對規則。
   否則,採用伺服器字符集和伺服器校對規則。
   如果在列定義中沒有指定列字符集和校對規則,則預設使用表字符集和校對規則。表字符集和校對規則是MySQL的擴充套件;在標準SQL中沒有。


2.4 連線字符集和校對規則詳見 
文件 https://dev.mysql.com/doc/refman/5.1/zh/charset.html#charset-connection。

三 總結
    資料庫查詢使用校對規則的優先順序 列>表>資料庫>伺服器,預設情況下會繼承當前字符集所對應預設的字符集校對規則,對於想要在查詢的時候區分大小寫情況而使用校對規則的話,最好建立資料庫和表的時候 就指定好期望的字元校對規則。
   對於雲產品的RDS 小白客戶,顯然有些難了,需要使用文件來指引。
  當然作為程式設計師或者DBA 一定要使用自己熟悉的知識，或者使用之前一定要做好足夠的瞭解，線上無小事。。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-1158563/，如需轉載，請註明出處，否則將追究法律責任。

相關文章