深入探究Mysql模糊查詢是否區分大小寫

前言

近期,一直在忙著寫一個小小的個人博客項目,在實現 “全局搜索” 功能時,發現mysql模糊查詢語句有點兒神奇(本小白剛剛步入編程階段,所以可能讓大傢見笑瞭,哈哈哈),有時候 mysql模糊查詢語句區分大小寫,有時候 mysql查詢語句又不區分度大小寫,於是,做瞭很多次實驗,得出以下結論(可能有不對的地方,歡迎大佬們可以指出我的不足,也歡迎小夥伴們可以一起討論問題!)

先來介紹一下COLLATE,COLLATE通常是和數據編碼(CHARSET)相關的,一般來說每種CHARSET都有多種它所支持的COLLATE,並且每種CHARSET都指定一種COLLATE為默認值。例如latin1編碼的默認COLLATE為latin1_swedish_cigbk編碼的默認COLLATE為gbk_chinese_ciutf8mb4編碼的默認值為utf8mb4_general_ci

CHSRSET COLLATE
latin1 latin1_swedish_ci
gbk gbk_chinese_ci
utf8mb4 utf8mb4_general_ci
utf8 utf8_bin

有興趣的小夥伴,可以在navicate等數據庫可視化工具中,自己嘗試一下:




註意:mysql中有utf8utf8mb4兩種編碼,但是mysql中的utf8最多隻能支持3bytes長度的字符編碼,所以,建議大傢還是選擇utf8mb4編碼格式;

解決方法一

如圖所示,為瞭便於區分,讓小夥伴們更好地理解,在此,本小白建立瞭兩個一模一樣的數據庫,其中一個采用utf8編碼格式,另外一個采用utf8mb4格式:

區分大小寫

建表語句也采用utf8編碼格式:

建表語句

blog_test : utf8編碼格式

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_blog
-- ----------------------------
DROP TABLE IF EXISTS `t_blog`;
CREATE TABLE `t_blog`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `appreciation` bit(1) NOT NULL,
  `comment_tabled` bit(1) NOT NULL,
  `content` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `first_picture` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `flag` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `published` bit(1) NOT NULL,
  `recommend` bit(1) NOT NULL,
  `share_statement` bit(1) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `views` int(11) NULL DEFAULT NULL,
  `type_id` bigint(20) NULL DEFAULT NULL,
  `user_id` bigint(20) NULL DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK292449gwg5yf7ocdlmswv9w4j`(`type_id`) USING BTREE,
  INDEX `FK8ky5rrsxh01nkhctmo7d48p82`(`user_id`) USING BTREE,
  CONSTRAINT `FK292449gwg5yf7ocdlmswv9w4j` FOREIGN KEY (`type_id`) REFERENCES `t_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK8ky5rrsxh01nkhctmo7d48p82` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

數據庫內容

查詢結果 select * from t_blog where title like ‘%html%’;

select * from t_blog where title like ‘%HTML%’;

 

結論:

mysql采用utf8編碼格式,模糊查詢區分大小寫

不區分大小寫

建表語句

建表語句也采用utf8mb4編碼格式:

blog_test2 : utf8mb4編碼格式

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_blog
-- ----------------------------
DROP TABLE IF EXISTS `t_blog`;
CREATE TABLE `t_blog`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `appreciation` bit(1) NOT NULL,
  `comment_tabled` bit(1) NOT NULL,
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `first_picture` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `flag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `published` bit(1) NOT NULL,
  `recommend` bit(1) NOT NULL,
  `share_statement` bit(1) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `views` int(11) NULL DEFAULT NULL,
  `type_id` bigint(20) NULL DEFAULT NULL,
  `user_id` bigint(20) NULL DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK292449gwg5yf7ocdlmswv9w4j`(`type_id`) USING BTREE,
  INDEX `FK8ky5rrsxh01nkhctmo7d48p82`(`user_id`) USING BTREE,
  CONSTRAINT `FK292449gwg5yf7ocdlmswv9w4j` FOREIGN KEY (`type_id`) REFERENCES `t_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK8ky5rrsxh01nkhctmo7d48p82` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

數據庫內容

查詢結果 select * from t_blog where title like ‘%html%’;

select

* from t_blog where title like ‘%HTML%’;

 

結論

mysql采用utf8mb4編碼格式,模糊查詢不區分大小寫

解決方法二

 區分大小寫

方法

單獨指定所需字段(比如,在此處 我所需要區分大小寫的字段為表 ‘t_blog’ 中的 ‘title’ 字段)為 ‘utf8′ 編碼格式,COLLATE 為“utf8_bin”;

alter table t_blog CHANGE `title` `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;

結論

select * from t_blog where title like ‘%HTML%’;

select * from t_blog where title like ‘%html%’;

由上述執行結果可以看出,此方法可以使模糊查詢語句區分大小寫

 不區分大小寫

方法

單獨指定所需字段(比如,在此處 我所需要不區分大小寫的字段為表 ‘t_blog’ 中的 ‘title’ 字段)為 ‘utf8mb4′ 編碼格式,COLLATE 為“utf8mb4_bin”:

alter table t_blog CHANGE `title` `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL;

結論 select * from t_blog where title like ‘%HTML%’;

select * from t_blog where title like ‘%html%’;

由上述執行結果可以看出,此方法可以使模糊查詢語句不區分大小寫

解決辦法三

區分大小寫

方法

如圖所示,當前 ‘t_blog’ 表格的 ‘title’ 字段的 COLLATE 為 ‘utf8mb4_general_ci’ ,不區分大小寫

修改字段為BINARY:

alter table t_blog CHANGE `title` `title` varchar(255) BINARY NULL DEFAULT NULL;
alter table `t_blog` modify column `title` varchar(255) BINARY NULL DEFAULT NULL;

兩種修改方式選擇一種既可

結論

select * from t_blog where title like ‘%HTML%’;

select * from t_blog where title like ‘%html%’;

由上述執行結果可以看出,修改字段為BINARY,可以使模糊查詢語句區分大小寫

解決辦法四

區分大小寫

方法

如圖所示,當前 ‘t_blog’ 表格的 ‘title’ 字段的 COLLATE 為 ‘utf8mb4_general_ci’ ,不區分大小寫

查詢語句字段前面加BINARY:

select * from `t_blog` where BINARY `title` like '%html%';

結論

select * from t_blog where title like ‘%html%’;

select * from t_blog where BINARY title like ‘%html%’;

由上述執行結果可以看出,在查詢語句字段前面加BINARY,可以使模糊查詢語句區分大小寫

解決辦法五

(特別特別不推薦,盡量不要使用)

找到mysql相應版本下的my.ini文件,本小白對應的8.0.17版本;


在文件最後一行加上下面一句話:

lower_case_table_names=1

註意:0:區分大小寫,1:不區分大小寫

重啟mysql

本小白還有很多地方講解的不夠清楚明瞭,還請各位大佬指教!想更深入瞭解的可以參考大佬的博客(膜拜膜拜)mysql區分大小寫

總結

到此這篇關於Mysql模糊查詢是否區分大小寫的文章就介紹到這瞭,更多相關Mysql模糊查詢大小寫內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: