主题
sql中like的用法,在不同版本的MySQL中表现是不同的。具体细节如下:
version 5.6.45
case 1
select version();
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`status` tinyint(3) NULL DEFAULT NULL COMMENT '0:正常,1:冻结 2:删除',
`page` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `name_index`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 530839 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (1, 'article_1', 'article_1', 1, 1);
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (2, 'article_2', 'article_2', 1, 2);
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (3, 'article_3', 'article_3', 1, 3);
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (4, 'article_4', 'article_4', 1, 4);
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (5, 'article_5', 'article_5', 0, 5);
explain select * from article where name like '1';
explain select * from article where name like '1%';
explain select * from article where name like '%1';
explain select * from article where name like '%1%';
explain select * from article where name not like '%1%';
2和3type = range,说明用到了索引。4、5、6type=all,没用到索引。
4、5、6的possible_keys、keys为空。
key_len= 25(varchar(25)) * 4 + 2 + 1 = 103
2、3的extra=Using index意料之中,4、5、6的extra=Using where说明需要回表扫描。
case 2
select version();
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
-- `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
-- `status` tinyint(3) NULL DEFAULT NULL COMMENT '0:正常,1:冻结 2:删除',
-- `page` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `name_index`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 530839 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (1, 'article_1');
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (2, 'article_2');
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (3, 'article_3');
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (4, 'article_4');
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (5, 'article_5');
explain select * from article where name like '1';
explain select * from article where name like '1%';
explain select * from article where name like '%1';
explain select * from article where name like '%1%';
explain select * from article where name not like '%1%';
case 2和case 1比起来剪掉3个字段以后,重点变化有2点
1:4、5、6的type=index,说明用到了索引。但是只是在索引上顺序查找记录而已,索引最重要的功能快速定位并没有得逞。
2:1、2的type从range->index,1、2的rows从1->5。
3:1、2的extra从Using index condition—>Using where;Using index。
5.7.28 & 8.0.18
case 1
select version();
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`status` tinyint(3) NULL DEFAULT NULL COMMENT '0:正常,1:冻结 2:删除',
`page` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `name_index`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 530839 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (1, 'article_1', 'article_1', 1, 1);
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (2, 'article_2', 'article_2', 1, 2);
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (3, 'article_3', 'article_3', 1, 3);
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (4, 'article_4', 'article_4', 1, 4);
INSERT INTO `explain`.`article`(`id`, `name`, `title`, `status`, `page`) VALUES (5, 'article_5', 'article_5', 0, 5);
explain select * from article where name like '1';
explain select * from article where name like '1%';
explain select * from article where name like '%1';
explain select * from article where name like '%1%';
explain select * from article where name not like '%1%';
case 2
select version();
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
-- `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
-- `status` tinyint(3) NULL DEFAULT NULL COMMENT '0:正常,1:冻结 2:删除',
-- `page` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `name_index`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 530839 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (1, 'article_1');
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (2, 'article_2');
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (3, 'article_3');
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (4, 'article_4');
INSERT INTO `explain`.`article`(`id`, `name`) VALUES (5, 'article_5');
explain select * from article where name like '1';
explain select * from article where name like '1%';
explain select * from article where name like '%1';
explain select * from article where name like '%1%';
explain select * from article where name not like '%1%';