SQL保留字符的处理(like,%,_)_sql like %字符

1.问题描述

SQL有两个与LIKE相关的保留字符：
% 通配符，表示一个或多个任意字符
_ 通配符，标识一个任意字符
这样自然就会遇到如果字段内容本身包含%或_的情况，即保留字符的处理。

举一个实际的数据库表例子（基于sqlite3）：

sqlite> .headers on
sqlite> select * from test_table;
id|name|description
0|name0|des0
1|name1|des1
2|_|%
3|a|b
4|_d|%d
5|tr|%u
6|_aa|yy
7|xyz|
sqlite> 
1
2
3
4
5
6
7
8
9
10
11
12
13

我们往往需要从上层读到一个query的关键字请求，譬如na，作为select like的参数，譬如：

sqlite> select * from test_table where name like 'na%';
id|name|description
0|name0|des0
1|name1|des1
1
2
3
4
5

这时候，如果读入的参数是保留字符，就会出问题，譬如%

sqlite> select * from test_table where name like '%%';
id|name|description
0|name0|des0
1|name1|des1
2|_|%
3|a|b
4|_d|%d
5|tr|%u
6|_aa|yy
7|xyz|
sqlite> select * from test_table where description like '%%';
id|name|description
0|name0|des0
1|name1|des1
2|_|%
3|a|b
4|_d|%d
5|tr|%u
6|_aa|yy
sqlite> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

再试一下_

sqlite> select * from test_table where name like '_%';
id|name|description
0|name0|des0
1|name1|des1
2|_|%
3|a|b
4|_d|%d
5|tr|%u
6|_aa|yy
7|xyz|
sqlite> select * from test_table where description like '_%';
id|name|description
0|name0|des0
1|name1|des1
2|_|%
3|a|b
4|_d|%d
5|tr|%u
6|_aa|yy
sqlite> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

显然，我们需要将%和_转义成字面意义。

2.SQL层解决方案

SQL提供了关键字 escape 来处理转义，并且并没有在语言层面固定转义字符，而是可以自定义。这可能也是Android上层并没有提供转义处理的标准API的原因。将上述SQL语句改为

sqlite> select * from test_table where description like '/%%' escape '/';
id|name|description
2|_|%
4|_d|%d
5|tr|%u
1
2
3
4
5
6

sqlite> select * from test_table where name like '/_%' escape '/';
id|name|description
2|_|%
4|_d|%d
6|_aa|yy
1
2
3
4
5
6

得到正确的结果。escape的用法即在like接的内容参数中，在保留字符前面添加一个转义字符，然后在语句后面后缀关键字escape子语句做转义字符声明：

like '/_%' escape '/'
1

在使用escape的过程中，需要注意以下问题：

2.1作用域

SQL语言语句往往很长很复杂，那么escape关键字的作用域有多大呢？先做实验：

sqlite> select * from test_table;
id|name|description
0|name0|des0
1|name1|des1
2|_|%
3|a|b
4|_d|%d
5|tr|%u
6|_aa|yy
7|xyz|
sqlite> select * from test_table where name like '/_%' or description like '/%%' escape '/';
id|name|description
2|_|%
4|_d|%d
5|tr|%u
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

上述是我们假设作用域为最近的where，写出的select语句。显然不对，name like ‘/_%’没有被正确执行。修改

sqlite> select * from test_table where name like '/_%' escape '/' or description like '/%%' escape '/';
id|name|description
2|_|%
4|_d|%d
5|tr|%u
6|_aa|yy
1
2
3
4
5
6
7

显然，这次是正确的。再试一下同时在同一个SQL语句中使用不同的自定义转义字符：

sqlite> select * from test_table where name like '9_%' escape '9' or description like '/%%' escape '/';
id|name|description
2|_|%
4|_d|%d
5|tr|%u
6|_aa|yy
1
2
3
4
5
6
7

没问题。可见，escape的作用域是最近的一个like参数表达式。

2.2不能用于非like语句

接下来一个自然的问题：对于非like语句，需要配置%和_的转义吗？如下实验：

sqlite> select * from test_table where name is '/_d' escape '/';
Error: near "escape": syntax error
sqlite> 
sqlite> 
sqlite> select * from test_table where name is '_d';
id|name|description
4|_d|%d
sqlite> 
1
2
3
4
5
6
7
8

sqlite> select * from test_table where name <> '_d';
id|name|description
0|name0|des0
1|name1|des1
2|_|%
3|a|b
5|tr|%u
6|_aa|yy
7|xyz|
sqlite> 
sqlite> 
sqlite> select * from test_table where name <> '/_d' escape '/';
Error: near "escape": syntax error
1
2
3
4
5
6
7
8
9
10
11
12
13
14

可见，报错。非like语句不能也不需要使用escape关键字处理保留字符。