MySQL 中的 LIKE 模糊查找的一些小问题

今天被 QA 同事发现一个关于 MySQL 中模糊查找的 BUG,说起来还挺尴尬的。 当我看到问题,我竟然还不知道我哪里写错了。

问题是这样的,我本意是要在数据库中查找以abc_开头的行,结果,我的最终语句写成了像下面这样:

SELECT * FROM users_tab WHERE name LIKE 'abc_%';

这样能查找到想要的结果吗?看看就知道了:

mysql> SELECT * FROM users_tab WHERE name LIKE 'abc_%';
+----+----------+
| id | name     |
+----+----------+
|  1 | abc_1    |
|  2 | abc_2    |
|  3 | abcdef_1 |
|  4 | abcdef_2 |
+----+----------+
4 rows in set (0.00 sec)

问题出在,我只知道 % 是 SQL 的通配符,但是实际上 _ 也是标准的 SQL 通配符。 % 匹配零个或多个任意字符,_ 匹配单个任意字符。参见:MySQL 官方文档

这样一来,上面的语句给出的结果就很清楚了。

所以应该怎么做以避免这种特殊字符的特殊含义?当然是转义字符(默认是 \)。 用 \_ 表示匹配 _ 本身,用 \% 表示匹配 % 本身。

所以,我 LIKE 后面部分应该写作:abc\_%。对不对?

mysql> SELECT * FROM users_tab WHERE name LIKE 'abc\_%';
+----+-------+
| id | name  |
+----+-------+
|  1 | abc_1 |
|  2 | abc_2 |
+----+-------+
2 rows in set (0.00 sec)

这个错误看似非常简单,但是实际上犯此错误的人绝对不在少数。 经我实际测试,我就在隔壁组的一个查找页面中尝试输入一个 % 字符,结果查找出了所有结果。 我猜后端在构造 SQL 语句的时候,LIKE 后面的部分绑定的参数应该是如下这种写法得到的:

arg := fmt.Sprintf("%%%s%%", input)
  • 当输入 a 时,得到 %a%
  • 当输入 % 时,得到 %%%

于是乎,所有结果都被匹配了。 然而,正确期望得到的应该是:%\%%。 这样才表示查找包含 % 这个字符本身的结果。

上述写法的错误之处在于没有处理 input 中的特殊字符:%_,还有转义字符 \

由于 \ 本身是 SQL 语句的转义字符,如果是手写拼接 SQL(非常不推荐),则应该写成:

SELECT * FROM users_tab WHERE name LIKE '\\\\';

而如果是以参数绑定的方式,则应该写成:

SELECT * FROM users_tab WHERE name LIKE ?;

其中 ? 被绑定的参数为:\\,类似 \_\%

针对参数绑定这种情况,我写了一个 golang 程序来帮助转换:

package main

import (
	"fmt"
	"strings"
)

var escapeReplacer = strings.NewReplacer(`%`, `\%`, `_`, `\_`, `\`, `\\`)

func main() {
	fmt.Println(escapeReplacer.Replace(`abc_`))
	fmt.Println(escapeReplacer.Replace(`%`))
	fmt.Println(escapeReplacer.Replace(`\`))
	fmt.Println(escapeReplacer.Replace(`_`))
}

运行结果:

$ go run main.go
abc\_
\%
\\
\_

经过这样一次替换操作,其结果就可以放心地给 LIKE 语句作参数使用了。

使用其它转义字符也是可以的,本文不讨论。

如果文章有帮助到你,请我喝杯冰可乐吧~

发表于:2021年11月17日 ,阅读量:82 ,标签:MySQL查看源内容