MySQL中模糊查询一般使用 like 关键字 例如建立表如下所示
CREATE TABLE `user` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`username` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '用户名',
`password` varchar(100) CHARACTER SET utf8 DEFAULT '' COMMENT '密码',
`email` varchar(100) CHARACTER SET utf8 DEFAULT '' COMMENT '邮箱',
`mobile` varchar(100) CHARACTER SET utf8 DEFAULT '' COMMENT '手机号',
`status` tinyint(1) DEFAULT 1 COMMENT '状态 0:禁用 1:正常',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COMMENT = '用户表' ROW_FORMAT = Dynamic;
在表中建立了两个索引一个id主键索引,一个username唯一索引,当我们使用模糊查询,查询所有张姓用户、或者所有名为三、或者username中包含有三的用户,可以有SQL语句:
SELECT * FROM user WHERE username LIKE '张%'
SELECT * FROM user WHERE username LIKE '%三'
SELECT * FROM user WHERE username LIKE '%三%'
但是在上面的三条SQL语句中仅有第一条使用到索引,第二条和第三条SQL语句并不符合MySQL索引的左前缀原则。MySQL提供有其他函数,其中有字符串函数INSTR(str,substr),可以使用该函数代替第二、第三种SQL语句的模糊查询,语法是
INSTR(str,substr) 函数的意思是:这个函数返回字符串在某一个字段的内容中的位置, 没有找到字符串返回0,否则返回位置(从1开始)
SELECT * FROM user WHERE INSTR(username,'三')
大于0可以省略,默认是大于零的
注意如果建立有索引
SELECT * FROM user WHERE username LIKE '张%' 不等价于 SELECT * FROM user WHERE INSTR(username,'张') = 1
原因是INSTR(str,substr),无法使用索引,而 like 'str%',还是可以使用到一部分索引的,我们可以去解释一下这些语句
但是在其他查询中,该字段未建立索引,比如模糊查询上表中的邮箱,INSTR(str,substr)的效率确实优于 like '%str%'