正文
修改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 |
+----------------------+----------+
