【MYSQL】解决模糊查询含有关键字的问题

小新野子

已于 2024-12-05 10:01:00 修改

阅读量755

点赞数 20

文章标签： mysql 数据库

于 2024-12-03 17:34:50 首次发布

前言

下面讲了关于模糊查询条件中含有通配符关键字的问题，但是对于含有转义字符的问题没有提及，如果想了解转义字符的问题，请参考链接：【MYSQL】MYSQL转义字符带来的坑。

一、问题分析

假如你有一张表 user_info:

id	address
1	浙江省杭州市
2	浙江省舟山市
3	`浙江省xx%市`
4	浙江省宁波市

如果页面需要根据地址进行模糊查询，但是sql语句如下:

select 
	id, address
from 
	user_info
where
 	address like concat('%', #{condition}, '%')

我们想查询地址中含有%的数据，如果condition输入的是 '%'，期望是查询id=3的数据，结果是查询的全表的数据：

select id, address from user_info where address like '%%%'; -- 查询的是全表

所以使用concat函数在实现模糊查询时，查询条件中含有通配符会存在问题。跳转到concat函数用法

二、解决办法

1. 解决方式1

如果需要仍然使用concat拼接字符串的方式，则需要将拼接的字符串进行转义，出现问题的原因就是查询的字符串中含有关键字%，导致sql查询有问题。
所以当需要模糊查询时，需要处理查询条件中的通配符关键字

	// 转义condition中的通配符
	public static String escapeCondition(String condition) {
        if (condition == null || condition.length() <= 0) {
            return condition;
        }
        StringBuilder ans = new StringBuilder();
        for (char c : condition.toCharArray()) {
            if (c == '%' || c == '_' || c == '\\') {
                // 如果当前字符是关键字符，则添加转义字符
                ans.append("\\");
            }
            ans.append(c);
        }
        return ans.toString();
    }

# 将查询条件的%进行转义，加上\后，sql如下
select id, address from user_info where address like '%\%%'; -- 能够正确将id=3的结果查询出来

PS: 如果不喜欢使用\字符进行转义，也可以使用escape关键字来自定义转义字符。跳转到escape关键字用法

2. 解决方式2

可以使用INSTR函数替代concat函数实现模糊查询
INSTR(str, subStr): 是返回子字符串(subStr)在目标字符串(str)的位置。(不区分大小写)

select INSTR('Hello World', 'he'); -- 返回结果是 1
select INSTR('Hello World', 'Hex'); -- 返回结果是 0

所以上面的写法可以改写为如下，就可以实现模糊查询：

select 
	id, address
from 
	user_info
where
	INSTR(address, #{condition})

三、函数用法

concat函数用法
mysql用法：CONCAT(str1,str2,…)，将指定的若干字符串拼接在一起

# mysql的concat用法
select CONCAT('12','34','56','7');  -- 结果为 '1234567'
# mysql中只要有一个为null,整个结果都是null
select CONCAT('12','34','56', null, '7'); --结果为null

oracle用法：CONCAT(str1,str2)，和mysql中不同的是，oracle中concat只能拼接两个字符串，如果要想拼接多个，只能使用嵌套的方式。

# oracle的concat用法
select CONCAT('12', '34') from dual;  -- 结果为 '1234'
select CONCAT('12', null) from dual;  -- 结果为 null
# oracle嵌套拼接多个字符
SELECT CONCAT(CONCAT('12', '34'), '56') FROM dual; -- 结果为 '123456'

escape关键字用法
在mysql中，使用escape关键字用于指定一个转义字符，在使用like操作符处理通配符%和_时，能够区分他们作为普通字符还是作为通配符使用，在默认情况下，mysql使用反斜杠 \作为转义字符，但是可以通过escape关键字自定义转义字符。
```
# 默认情况下使用\作为转义字符
select id, address from user_info where address like '%\%%';
# 想使用 ^ 作为转义字符, 和上面的sql效果相同
select id, address from user_info where address like '%^%%' ESCAPE '^';
```