shell> mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci
2.2数据库级
MySQL这样选择数据库字符集和数据库校对规则：
如果指定了character set X和collate Y，那么采用字符集X和校对规则Y。
如果指定了character set X而没有指定collate Y，那么采用character set X和character set X的默认校对规则。
否则，采用服务器字符集和服务器校对规则。
(1)修改配置文件/etc/my.cnf
进行了两组测试：
1) 在[mysqld]下添加：
| collation_server = utf8_bin collation_database = utf8_bin |
2) 在[mysqld]下添加：
| collation_database = utf8_bin |
重启实例，两组都不能正常启动，错误信息如下：
可见，my.cnf配置文件中不支持设置collation_database 变量。
（2）创建数据库时设置数据库校验规则
| mysql> create database yutest default character set utf8 collate utf8_bin; Query OK, 1 row affected (0.00 sec) mysql> show variables like 'collation_%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_bin | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) mysql> select * from t1; +------+ | name | +------+ | ABC | | abc | +------+ 2 rows in set (0.00 sec) mysql> select * from t1 where name='abc'; +------+ | name | +------+ | abc | +------+ 1 row in set (0.01 sec) |
可以看出，在数据库级进行相应的校对规则设置，查询大小写敏感。
2.3表级
MySQL按照下面的方式选择表字符集和校对规则：
如果指定了character set X和collate Y，那么采用character set X和collate Y。
如果指定了character set X而没有指定collate Y，那么采用character set X和character set X的默认校对规则。
否则，采用数据库字符集和服务器校对规则。
在创建表时设置表级校验规则：
| mysql> create database yutest2; Query OK, 1 row affected (0.01 sec) mysql> use yutest2; Database changed mysql> create table t1(name varchar(10)) -> default character set utf8 collate utf8_bin; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values('ABC'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values('abc'); Query OK, 1 row affected (0.00 sec) mysql> show variables like 'collation_%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) mysql> select * from t1; +------+ | name | +------+ | ABC | | abc | +------+ 2 rows in set (0.00 sec) mysql> select * from t1 where name='abc'; +------+ | name | +------+ | abc | +------+ 1 row in set (0.00 sec) |
