In-depth exploration of whether Mysql fuzzy query is case-sensitive

Preface

Recently, I have been busy writing a small personal blog project. When implementing the "global search" function, I found that MySQL fuzzy query statements are a bit magical (I have just entered the programming stage, so I may make you laugh, hahaha). Sometimes MySQL fuzzy query statements are case-sensitive , and sometimes MySQL query statements are not case-sensitive . So, I did many experiments and came to the following conclusions (there may be some mistakes, welcome to point out my shortcomings, and welcome friends to discuss the problem together!)

Let's first introduce COLLATE. COLLATE is usually related to data encoding (CHARSET). Generally speaking, each CHARSET has multiple COLLATEs it supports, and each CHARSET specifies a COLLATE as the default value. For example, the default COLLATE for latin1 encoding is latin1_swedish_ci , the default COLLATE for gbk encoding is gbk_chinese_ci , and the default COLLATE for utf8mb4 encoding is utf8mb4_general_ci :

Interested friends can try it themselves in database visualization tools such as Navicate:

Note : There are two encodings in MySQL: utf8 and utf8mb4 . However, utf8 in MySQL can only support character encodings with a length of up to 3 bytes. Therefore, it is recommended that you choose the utf8mb4 encoding format.

Solution 1

As shown in the figure, in order to facilitate the distinction and let friends better understand, here, I have established two identical databases, one of which uses utf8 encoding format and the other uses utf8mb4 format:

case sensitive

The table creation statement also uses the utf8 encoding format:

Create table statement

blog_test : utf8 encoding format

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;

Database Contents

Query results select * from t_blog where title like '%html%';

select * from t_blog where title like '%HTML%';

in conclusion:

MySQL uses utf8 encoding format, and fuzzy queries are case-sensitive

Case insensitive

Create table statement

The table creation statement also uses the utf8mb4 encoding format:

blog_test2: utf8mb4 encoding format

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;

Database Contents

Query results select * from t_blog where title like '%html%';

select

* from t_blog where title like '%HTML%';

in conclusion

MySQL uses utf8mb4 encoding format, and fuzzy queries are not case-sensitive

Solution 2

case sensitive

method

Specify the required fields individually (for example, the field I need to be case-sensitive here is the 'title' field in the 't_blog' table) as 'utf8' encoding format, and COLLATE as "utf8_bin";

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

in conclusion

select * from t_blog where title like '%HTML%';

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

From the above execution results, we can see that this method can make the fuzzy query statement case-sensitive ;

Case insensitive

method

Specify the required fields individually (for example, the field I need to be case-insensitive here is the 'title' field in the table 't_blog') as 'utf8mb4' encoding format, and COLLATE as "utf8mb4_bin":

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

Conclusion select * from t_blog where title like '%HTML%';

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

From the above execution results, we can see that this method can make the fuzzy query statement case-insensitive ;

Solution 3

case sensitive

method

As shown in the figure, the COLLATE of the 'title' field of the current 't_blog' table is 'utf8mb4_general_ci', which is case-insensitive

Change the field to 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;

Choose one of the two modification methods.

in conclusion

select * from t_blog where title like '%HTML%';

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

From the above execution results, we can see that changing the field to BINARY can make the fuzzy query statement case-sensitive ;

Solution 4

case sensitive

method

As shown in the figure, the COLLATE of the 'title' field of the current 't_blog' table is 'utf8mb4_general_ci', which is case-insensitive

Add BINARY before the query statement field:

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

in conclusion

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

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

From the above execution results, we can see that adding BINARY before the query statement field can make the fuzzy query statement case-sensitive .

Solution 5

(It is especially not recommended, try not to use it)

Find the my.ini file under the corresponding version of MySQL, which corresponds to version 8.0.17 for this newbie;

Add the following sentence to the last line of the file:

lower_case_table_names=1

Note: 0: case sensitive, 1: case insensitive

Restart mysql

There are still many places that I haven't explained clearly enough, so I would like to ask the experts for advice! If you want to learn more, you can refer to the blog of the boss (worship) mysql is case sensitive

Summarize

This is the end of this article about whether MySQL fuzzy query is case sensitive. For more information about MySQL fuzzy query case sensitivity, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!