一 什么是字符校对规则
使用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 一定要使用自己熟悉的知识，或者使用之前一定要做好足够的了解，线上无小事。。
使用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 一定要使用自己熟悉的知识，或者使用之前一定要做好足够的了解，线上无小事。。
