MySQL查询未区分大小写

场景

考虑如下场景:

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‘,但是结果把CODINGCATcodingcat一起给查询出来了,可见此查询结果并未区分大小写。

探究

我们知道,在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> 
    

MySQL查询未区分大小写

上一篇:C++ 类模板作为函数模板示例


下一篇:按键精灵的几个常见操作:激活窗口、打开Excel读取数据、FindPic、等待用户输入、等待网站返回处理结果……