场景
考虑如下场景:
mysql> use test_0705;
Database changed
mysql> select * from tb_test where name = ‘CodingCat‘;
+----+-----------+
| id | name |
+----+-----------+
| 1 | CodingCat |
| 2 | CODINGCAT |
| 3 | codingcat |
+----+-----------+
3 rows in set (0.02 sec)
mysql>
从上图我们看到,查询条件中指定要查询的是name=‘CodingCat‘
,但是结果把CODINGCAT
和codingcat
一起给查询出来了,可见此查询结果并未区分大小写。
探究
我们知道,在MySQL中有排序规则这个概念,排序规则表示在规定的存储的数据编码格式下的比较规则,如是否区分大小写等。针对上面的场景,查看表结构如下:
mysql> show create table tb_test;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_test | CREATE TABLE `tb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql>
该表继承了数据库的排序规则,数据库建表语句如下:
mysql> show create database test_0705;
+-----------+--------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------+
| test_0705 | CREATE DATABASE `test_0705` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql>
从数据库建表语句来看,并没有指定排序规则,则该数据库继承了全局的排序规则,查看数据库的排序规则如下:
mysql> show variables like ‘collation%‘;
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.38 sec)
mysql>
从全局的配置来看,数据库服务器使用的是utf8mb4_general_ci
排序规则,后缀有ci,意思是case insensitive,即大小写不敏感,所以查询出来的结果不区分大小写。
在MySQL中,排序规则常见有带后缀ci和不带ci两种,如utf8_bin和utf8_genera_ci,带ci表示区分大小写,否则不区分。
解决
对于上面的问题,如果在设置了字符排序规则是带ci后缀的,但是又想要查询结果是区分大小写,怎么做?有两种解决方法。
-
查询时在需要区分大小写的字段条件上使用binary关键字
mysql> select * from tb_test where binary name = ‘CodingCat‘; +----+-----------+ | id | name | +----+-----------+ | 1 | CodingCat | +----+-----------+ 1 row in set (0.04 sec) mysql>
-
修改表中字段的排序规则
mysql> ALTER TABLE tb_test MODIFY COLUMN name VARCHAR(255) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL; Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table tb_test; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_test | CREATE TABLE `tb_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.05 sec) mysql> select * from tb_test where binary name = ‘CodingCat‘; +----+-----------+ | id | name | +----+-----------+ | 1 | CodingCat | +----+-----------+ 1 row in set (0.05 sec) mysql>