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

陪她去流浪 桃子 阅读次数：12343

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

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

1

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 后面的部分绑定的参数应该是如下这种写法得到的：

1

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

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

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

由于 \ 本身是 SQL 语句的转义字符，如果是手写拼接 SQL（非常不推荐），则应该写成：

1

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

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

1

SELECT * FROM users_tab WHERE name LIKE ?;

其中 ? 被绑定的参数为：\\，类似 \_ 和 \%。

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

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(`_`))
}

运行结果：

1
2
3
4
5

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

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

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

标签：MySQL

相关文章