近期,一直在忙着写一个小小的个人博客项目,在实现“全局搜索” 功能时,发现mysql模糊查询语句有点儿神奇(本小白刚刚步入编程阶段,所以可能让大家见笑了,哈哈哈),有时候mysql模糊查询语句区分大小写,有时候mysql查询语句又不区分度大小写,于是,做了很多次实验,得出以下结论(可能有不对的地方,欢迎大佬们可以指出我的不足,也欢迎小伙伴们可以一起讨论问题!)
先来介绍一下COLLATE,COLLATE通常是和数据编码(CHARSET)相关的,一般来说每种CHARSET都有多种它所支持的COLLATE,并且每种CHARSET都指定一种COLLATE为默认值。例如latin1编码的默认COLLATE为latin1_swedish_ci,gbk编码的默认COLLATE为gbk_chinese_ci,utf8mb4编码的默认值为utf8mb4_general_ci:
CHSRSET | COLLATE |
---|---|
latin1 | latin1_swedish_ci |
gbk | gbk_chinese_ci |
utf8mb4 | utf8mb4_general_ci |
utf8 | utf8_bin |
有兴趣的小伙伴,可以在navicate等数据库可视化工具中,自己尝试一下:
注意:mysql中有utf8和utf8mb4两种编码,但是mysql中的utf8最多只能支持3bytes长度的字符编码,所以,建议大家还是选择utf8mb4编码格式;
解决方法一
如图所示,为了便于区分,让小伙伴们更好地理解,在此,本小白建立了两个一模一样的数据库,其中一个采用utf8编码格式,另外一个采用utf8mb4格式:
区分大小写
建表语句也采用utf8编码格式:
建表语句
- blog_test : utf8编码格式
SET NAMES utf8;SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for t_blog-- ----------------------------DROPTABLEIFEXISTS`t_blog`;CREATETABLE`t_blog`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`appreciation`bit(1)NOTNULL,`comment_tabled`bit(1)NOTNULL,`content`longtextCHARACTERSET utf8COLLATE utf8_binNULL,`create_time`datetime(0)NULLDEFAULTNULL,`first_picture`varchar(255)CHARACTERSET utf8COLLATE utf8_binNULLDEFAULTNULL,`flag`varchar(255)CHARACTERSET utf8COLLATE utf8_binNULLDEFAULTNULL,`published`bit(1)NOTNULL,`recommend`bit(1)NOTNULL,`share_statement`bit(1)NOTNULL,`title`varchar(255)CHARACTERSET utf8COLLATE utf8_binNULLDEFAULTNULL,`update_time`datetime(0)NULLDEFAULTNULL,`views`int(11)NULLDEFAULTNULL,`type_id`bigint(20)NULLDEFAULTNULL,`user_id`bigint(20)NULLDEFAULTNULL,`description`varchar(255)CHARACTERSET utf8COLLATE utf8_binNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE,INDEX`FK292449gwg5yf7ocdlmswv9w4j`(`type_id`)USINGBTREE,INDEX`FK8ky5rrsxh01nkhctmo7d48p82`(`user_id`)USINGBTREE,CONSTRAINT`FK292449gwg5yf7ocdlmswv9w4j`FOREIGNKEY(`type_id`)REFERENCES`t_type`(`id`)ONDELETERESTRICTONUPDATERESTRICT,CONSTRAINT`FK8ky5rrsxh01nkhctmo7d48p82`FOREIGNKEY(`user_id`)REFERENCES`t_user`(`id`)ONDELETERESTRICTONUPDATERESTRICT)ENGINE=InnoDBAUTO_INCREMENT=14CHARACTERSET= utf8COLLATE= utf8_bin ROW_FORMAT= Dynamic;
数据库内容
查询结果
- select * from
t_blog
wheretitle
like ‘%html%’; - select * from
t_blog
wheretitle
like ‘%HTML%’;
结论:
mysql采用utf8编码格式,模糊查询区分大小写
不区分大小写
建表语句
建表语句也采用utf8mb4编码格式:
- blog_test2 : utf8mb4编码格式
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for t_blog-- ----------------------------DROPTABLEIFEXISTS`t_blog`;CREATETABLE`t_blog`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`appreciation`bit(1)NOTNULL,`comment_tabled`bit(1)NOTNULL,`content`longtextCHARACTERSET utf8mb4COLLATE utf8mb4_general_ciNULL,`create_time`datetime(0)NULLDEFAULTNULL,`first_picture`varchar(255)CHARACTERSET utf8mb4COLLATE utf8mb4_general_ciNULLDEFAULTNULL,`flag`varchar(255)CHARACTERSET utf8mb4COLLATE utf8mb4_general_ciNULLDEFAULTNULL,`published`bit(1)NOTNULL,`recommend`bit(1)NOTNULL,`share_statement`bit(1)NOTNULL,`title`varchar(255)CHARACTERSET utf8mb4COLLATE utf8mb4_general_ciNULLDEFAULTNULL,`update_time`datetime(0)NULLDEFAULTNULL,`views`int(11)NULLDEFAULTNULL,`type_id`bigint(20)NULLDEFAULTNULL,`user_id`bigint(20)NULLDEFAULTNULL,`description`varchar(255)CHARACTERSET utf8mb4COLLATE utf8mb4_general_ciNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE,INDEX`FK292449gwg5yf7ocdlmswv9w4j`(`type_id`)USINGBTREE,INDEX`FK8ky5rrsxh01nkhctmo7d48p82`(`user_id`)USINGBTREE,CONSTRAINT`FK292449gwg5yf7ocdlmswv9w4j`FOREIGNKEY(`type_id`)REFERENCES`t_type`(`id`)ONDELETERESTRICTONUPDATERESTRICT,CONSTRAINT`FK8ky5rrsxh01nkhctmo7d48p82`FOREIGNKEY(`user_id`)REFERENCES`t_user`(`id`)ONDELETERESTRICTONUPDATERESTRICT)ENGINE=InnoDBAUTO_INCREMENT=14CHARACTERSET= utf8mb4COLLATE= utf8mb4_general_ci ROW_FORMAT= Dynamic;
数据库内容
查询结果
- select * from
t_blog
wheretitle
like ‘%html%’; - select * from
t_blog
wheretitle
like ‘%HTML%’;
结论
mysql采用utf8mb4编码格式,模糊查询不区分大小写
解决方法二
区分大小写
方法
单独指定所需字段(比如,在此处 我所需要区分大小写的字段为表 ‘t_blog’ 中的 ‘title’ 字段)为 ‘utf8’ 编码格式,COLLATE 为“utf8_bin”;
altertable t_blog CHANGE`title``title`varchar(255)CHARACTERSET utf8COLLATE utf8_binNULLDEFAULTNULL;
结论
- select * from
t_blog
wheretitle
like ‘%HTML%’; - select * from
t_blog
wheretitle
like ‘%html%’;
由上述执行结果可以看出,此方法可以使模糊查询语句区分大小写;
不区分大小写
方法
单独指定所需字段(比如,在此处 我所需要不区分大小写的字段为表 ‘t_blog’ 中的 ‘title’ 字段)为 ‘utf8mb4’ 编码格式,COLLATE 为“utf8mb4_bin”:
altertable t_blog CHANGE`title``title`varchar(255)CHARACTERSET utf8mb4COLLATE utf8mb4_binNULLDEFAULTNULL;
结论
- select * from
t_blog
wheretitle
like ‘%HTML%’;
- select * from
t_blog
wheretitle
like ‘%html%’;
由上述执行结果可以看出,此方法可以使模糊查询语句不区分大小写;
解决办法三
区分大小写
方法
- 如图所示,当前 ‘t_blog’ 表格的 ‘title’ 字段的 COLLATE 为 ‘utf8mb4_general_ci’ ,不区分大小写
- 修改字段为BINARY:
altertable t_blog CHANGE`title``title`varchar(255)BINARYNULLDEFAULTNULL;
altertable`t_blog`modifycolumn`title`varchar(255)BINARYNULLDEFAULTNULL;
两种修改方式选择一种既可
结论
- select * from
t_blog
wheretitle
like ‘%HTML%’; - select * from
t_blog
wheretitle
like ‘%html%’;
由上述执行结果可以看出,修改字段为BINARY,可以使模糊查询语句区分大小写;
解决办法四
区分大小写
方法
- 如图所示,当前 ‘t_blog’ 表格的 ‘title’ 字段的 COLLATE 为 ‘utf8mb4_general_ci’ ,不区分大小写
- 查询语句字段前面加BINARY:
select*from`t_blog`whereBINARY`title`like'%html%';
结论
select * from
t_blog
wheretitle
like ‘%html%’;select * from
t_blog
where BINARYtitle
like ‘%html%’;
由上述执行结果可以看出,在查询语句字段前面加BINARY,可以使模糊查询语句区分大小写。
解决办法五(特别特别不推荐,尽量不要使用)
找到mysql相应版本下的my.ini文件,本小白对应的8.0.17版本;
在文件最后一行加上下面一句话:
lower_case_table_names=1
注意:0:区分大小写,1:不区分大小写
重启mysql
本小白还有很多地方讲解的不够清楚明了,还请各位大佬指教!想更深入了解的可以参考大佬的博客(膜拜膜拜)mysql区分大小写