【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 一定要使用自己熟悉的知识，或者使用之前一定要做好足够的了解，线上无小事。。