MySQL忽略大小写查询

MySQL忽略大小写查询

MySQL部署时一般使用大小写敏感的字符集规则。例如utf8mb4_bin、utf8mb4_0900_as_cs 等。

但在涉及到前端的业务通常有模糊查询的需求。例如搜索栏常常需要忽略大小写。abc 可以筛选出 ABC、abc、aBc 等。

创建测试表

create table  test1(
a int unsigned auto_increment primary key ,
b varchar(10) 
) collate utf8mb4_bin;

insert  into test1(b) select "aaa";
insert  into test1(b) select "aAA";
insert  into test1(b) select "aAa";
insert  into test1(b) select "AAa";
insert  into test1(b) select "BBa"

> select * from test1;
1,aaa
2,aAA
3,aAa
4,AAa
5,BBa

1. 通过在SQL查询时制定字符集规则的方式忽略大小写

   > select * from test1 where b like "A%" COLLATE utf8mb4_0900_ai_ci;
   1,aaa
   2,aAA
   3,aAa
   4,AAa
   
   > select * from test1 where b like "A%"
   4,AAa
   
   

2. 使用upper()、lower()函数将整列与值全部转换为大写、或者小写

   > select * from test1 where upper(b) like upper('A%');
   1,aaa
   2,aAA
   3,aAa
   4,AAa
   

上述方法都存在无法使用普通索引的问题。即使字段b上已经建好了普通索引。
但依旧推荐使用方法2 ，原因如下

在MySQL5.7中可以通过使用虚拟列的方式创建表达式索引。

在MySQL8.0中可以使用函数索引的方式创建函数索引

alter table  test1 add index idx_b ( (upper(b)));