修改mysql数据的字符集校验规则使其区分大小写

mysql 使用utf8字符集默认的校验规则collate为utf8_general_ci，不区分数据的大小写

测试如下

13:50:04[test](;)> alter table test add col1 varchar(25) character set utf8 collate utf8_bin;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

13:51:43[test](;)> update test set col1='BBc' where name='bbc';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

13:52:10[test](;)> select * from test;
+------+------+
| name | col1 |
+------+------+
| BBC  | BBc  |
+------+------+
1 row in set (0.00 sec)

13:52:17[test](;)> select * from test where col1='bbc';
Empty set (0.00 sec)

13:52:36[test](;)> alter table test modify name varchar(25) character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

13:53:26[test](;)> select * from test where name='bbc';    
Empty set (0.00 sec)
13:53:32[test](;)> alter table test modify name varchar(25) character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

14:06:26[test](;)> select * from test where name='bbc';
+------+------+
| name | col1 |
+------+------+
| BBC  | BBc  |
+------+------+
1 row in set (0.00 sec)

 

修改数据库和表的字符集对已存入的和后来插入的数据都无影响，数据的collate依然是默认的

alter table collate_ DEFAULT CHARSET=utf8 collate=utf8_bin;
alter database test default COLLATE=utf8_bin;

修改相关变量不起效，变更字段的collate才奏效

14:09:00[(none)](;)> show variables like '%coll%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| collation_connection | utf8_bin |
| collation_database   | utf8_bin |
| collation_server     | utf8_bin |
+----------------------+----------+