SQL 数据分析 · 指南 · 2026年10月30日 · 11 分钟阅读

SQL LIKE 模糊查询：% 和 _ 通配符、ILIKE、NOT LIKE 与转义

LIKE 按模式筛选文本，而不是按精确值，也就是常说的「模糊查询」。模式里只有两个通配符：% 匹配任意多个字符（包括零个），_ 恰好匹配一个字符。 所以 city LIKE 'Mos%' 找出所有以「Mos」开头的城市，model LIKE '%300' 找出以「300」结尾的机型，airport_name LIKE '%ovo%' 找出名字里任何位置含有「ovo」的机场。在 PostgreSQL 和 DuckDB 里 LIKE 区分大小写，不区分大小写的版本是 ILIKE。NOT LIKE 取反，但无论 LIKE 还是 NOT LIKE，都不会返回该列为 NULL 的行。

下面的例子都在 Nomto SQL 课程的航空公司数据库上运行，使用 PostgreSQL 语法，引擎是 DuckDB（Nomto SQL 练习场背后的引擎），每个结果表都是真实输出。工作中常见的 MySQL、Hive 和 Spark SQL 有哪些不同，也会在对应的地方说明。

LIKE 基本语法

SELECT columns
FROM table
WHERE text_column LIKE 'pattern';

模式就是一个用单引号包起来的普通字符串。不带通配符时，LIKE 的效果和 = 一样，真正有用的是 % 和 _。模式必须匹配整个值，所以 % 放在哪里非常关键：

% 通配符：开头是、结尾是、包含

以某个字符串开头

SELECT airport_code, airport_name, city
FROM airports
WHERE city LIKE 'Mos%'
ORDER BY airport_code;

以某个字符串结尾

SELECT model
FROM aircrafts
WHERE model LIKE '%300'
ORDER BY model;

包含某个字符串

两边都加 %，意思是「出现在字符串的任何位置」：

SELECT airport_name, city
FROM airports
WHERE airport_name LIKE '%ovo%'
ORDER BY airport_name
LIMIT 6;

完整结果有 12 个机场，这里只截取前六行。

「包含」常常会比你想要的多匹配一些。乘客姓名以「名 姓」的形式全大写存储。用 '%IVANOV%' 搜姓氏 Ivanov 会返回 14,040 张机票，而 '% IVANOV'（一个空格，然后姓氏在最末尾）只返回 7,539 张：

SELECT CASE WHEN passenger_name LIKE '% IVANOV'  THEN 'IVANOV'
            WHEN passenger_name LIKE '% IVANOVA' THEN 'IVANOVA'
            ELSE 'other' END AS surname,
       COUNT(*) AS tickets
FROM tickets
WHERE passenger_name LIKE '%IVANOV%'
GROUP BY surname
ORDER BY tickets DESC;

宽松的模式悄悄把女性形式 Ivanova 也算了进来。中文数据里是同样的道理：搜 '%张%' 找姓张的人，名字里带「张」字的也会被找出来，应该写 '张%'。数据允许的话，模式要尽量锚定得紧一些。

_ 通配符：恰好一个字符

_ 代表一个字符，不多也不少。五个下划线找出名字是五个字母的城市：

SELECT city
FROM airports
WHERE city LIKE '_____'
ORDER BY city;

两种通配符可以混用：'S_r%' 表示 S，然后任意一个字符，然后 r，后面随意。

SELECT city FROM airports WHERE city LIKE 'S_r%' ORDER BY city;

Strezhevoy 不是错误：S，下划线匹配到「t」，然后是「r」。_ 匹配任何字符，辅音也算。

中文字符：一个汉字算一个字符

_ 按字符计数，不按字节计数，所以一个汉字对应一个 _：

SELECT '北京' LIKE '__' AS two_chars,
       '北京首都' LIKE '北京%' AS prefix,
       length('北京') AS len;

PostgreSQL 和使用 utf8mb4 字符集的 MySQL 也是这样。要找两个字的名字，写 name LIKE '__' 就行。注意 MySQL 的 LENGTH() 返回的是字节数（一个汉字通常是 3），按字符数要用 CHAR_LENGTH()。

不区分大小写：ILIKE 和 LOWER

在 PostgreSQL 和 DuckDB 里，LIKE 逐个字符精确比较，小写字母匹配不上大写字母：

SELECT 'ABC' LIKE 'abc' AS like_result, 'ABC' ILIKE 'abc' AS ilike_result;

WHERE city LIKE 'mos%' 一行都不返回；WHERE city ILIKE 'mos%' 返回莫斯科的三个机场。ILIKE 不是标准 SQL：PostgreSQL、DuckDB、Snowflake 和 Spark SQL（3.3 起）有它，MySQL、SQL Server 和 Hive 没有。通用的做法是把两边都转成小写：

SELECT city FROM airports WHERE lower(city) LIKE 'mos%';

在 MySQL 和 SQL Server 里，是否区分大小写取决于列的排序规则（collation），而默认的排序规则不区分大小写，所以普通的 LIKE 'mos%' 在那里就能找到 Moscow。查询在不同数据库之间迁移时，这一点经常让人意外。

NOT LIKE，以及 NULL 行为什么会消失

NOT LIKE 保留不匹配的行。陷阱在于：如果列是 NULL，LIKE 和 NOT LIKE 都返回 NULL，WHERE 两种情况下都会把这一行丢掉。大约一半的机票在 contact_data 里有邮箱。按地址里是否含下划线来拆分（\_ 是什么意思，下一节会讲）：

SELECT COUNT(*) FILTER (WHERE contact_data->>'email' LIKE '%\_%' ESCAPE '\')     AS with_underscore,
       COUNT(*) FILTER (WHERE contact_data->>'email' NOT LIKE '%\_%' ESCAPE '\') AS without_underscore,
       COUNT(*) FILTER (WHERE contact_data->>'email' IS NULL)                    AS no_email,
       COUNT(*) AS total
FROM tickets;

LIKE 加 NOT LIKE 一共是 199,083，不是 366,733：没有邮箱的 167,650 张机票两边都不在。如果「不匹配」应该包括「没有值」，就要明确写出来：WHERE email NOT LIKE '%\_%' ESCAPE '\' OR email IS NULL，或者 WHERE COALESCE(email, '') NOT LIKE ...。背后的三值逻辑见 SQL NULL 值处理。

转义：怎样搜索 % 或 _ 本身

% 和 _ 是通配符，所以要搜这两个字符本身，需要一个转义字符。不转义的话，「含有下划线的邮箱」会匹配所有邮箱：

SELECT COUNT(*) AS emails,
       COUNT(*) FILTER (WHERE email LIKE '%_%')               AS unescaped,
       COUNT(*) FILTER (WHERE email LIKE '%\_%' ESCAPE '\')   AS escaped
FROM (SELECT contact_data->>'email' AS email FROM tickets) t
WHERE email IS NOT NULL;

'%_%' 的意思是「至少有一个任意字符」，任何非空字符串都满足。加上 ESCAPE '\' 之后，\_ 才表示一个真正的下划线。

各数据库的默认转义字符不一样，复制查询时往往就在这里出错：

• PostgreSQL 默认把反斜杠当作转义字符，所以不写 ESCAPE 的 LIKE '%\_%' 也能用。
• MySQL 和 PostgreSQL 一样，默认用反斜杠。
• DuckDB 没有默认转义字符。同样不写 ESCAPE 的 LIKE '%\_%' 在这份数据上返回 0 行，因为它在找一个真的反斜杠。务必写上 ESCAPE '\'。
• SQL Server 也没有默认转义字符，但允许把通配符放进方括号：LIKE '%[_]%'。在其他数据库里方括号只是普通字符（DuckDB 对这个模式返回 0 行）。
• Hive 和 Spark SQL 默认用反斜杠转义，但在字符串字面量里反斜杠本身往往还要再转义一次，写成 '%\\_%'，具体取决于配置。

显式写一个 ESCAPE 子句在上面这些数据库里都能用，而且转义字符可以任选，比如 LIKE '%!_%' ESCAPE '!'，这样就绕开了反斜杠的各种麻烦。

同时匹配多个模式

LIKE 只接受一个模式。要匹配多个，就用 OR 组合条件；如果 WHERE 里还有别的条件，要加上括号：

SELECT city
FROM airports
WHERE (city LIKE 'Nov%' OR city LIKE 'Nor%')
ORDER BY city;

不加括号的话，WHERE a AND b OR c 会被理解为 (a AND b) OR c，这是多出一堆行的经典原因。

PostgreSQL 还接受 city LIKE ANY (ARRAY['Nov%', 'Nor%'])。DuckDB 不行，会报错 Unsupported comparison "~~" for ANY/ALL subquery，所以在 Nomto SQL 练习场里请用 OR。

模式变复杂时，改用正则表达式。在 DuckDB 里，regexp_matches(city, '^(Nov|Nor|Noy)') 返回 Norilsk、Novokuznetsk、Novosibirsk、Novy Urengoy 和 Noyabrsk。PostgreSQL 用运算符 city ~ '^(Nov|Nor|Noy)'，MySQL 用 city REGEXP '^(Nov|Nor|Noy)'，Hive 和 Spark SQL 用 city RLIKE '^(Nov|Nor|Noy)'。在 DuckDB 里小心 ~：它要求正则匹配整个字符串，所以 city ~ '^Nov' 什么都不返回，需要写成 city ~ 'Nov.*'。

LIKE 和 = 的区别

知道完整的值时，就用 =。在 PostgreSQL 和 DuckDB 里，不带通配符的 LIKE 和 = 结果相同（'abc ' LIKE 'abc' 和 'abc ' = 'abc' 都是 false）。在 SQL Server 里，= 会忽略末尾空格，而 LIKE 不会，所以那里 'abc ' = 'abc' 为真，'abc' LIKE 'abc ' 却为假。

LIKE 也能用在转成文本的日期上，CAST(scheduled_departure AS VARCHAR) LIKE '2026-08-01%'，这里它和按范围查询一样得到 568 个航班。但更推荐范围写法 scheduled_departure >= '2026-08-01' AND scheduled_departure < '2026-08-02'：它保留列的类型，不管日期的文本格式如何都能在任何数据库里运行，还能使用索引。更多内容见 SQL 日期函数。

LIKE 的性能：什么时候走索引

在大表上，模式怎么开头决定了查询有多快：

• 固定前缀（'Mos%'） 可以使用 B-tree 索引，因为所有匹配的值在排序后挨在一起。在 PostgreSQL 里，只有 C 排序规则下才开箱即用；其他排序规则需要用 text_pattern_ops（或 varchar_pattern_ops）建索引。MySQL 的 LIKE '张%' 可以直接走普通索引。
• 前导通配符（'%ovo' 或 '%ovo%'） 用不上普通索引，数据库只能逐个值去读。PostgreSQL 里可以用三元组索引（pg_trgm 扩展加 GIN 或 GiST 索引）加速「包含」搜索；MySQL 里可以考虑全文索引（中文要配合 ngram 解析器）。
• lower(col) LIKE ... 和 ILIKE 不会用 col 上的普通索引。在 PostgreSQL 里，前者可以在 lower(col) 上建表达式索引，ILIKE 需要三元组索引。

DuckDB、BigQuery、Snowflake、Hive 这类列式分析引擎本来就是扫描整列，课程规模的数据上你感觉不到差别。但在有上百万行的线上 PostgreSQL 或 MySQL 表上，差别会非常明显。面试里「LIKE '%xx%' 为什么不走索引」也是常见问题。

常见错误

• 忘了写通配符。 WHERE city LIKE 'Mos' 只能找到名字恰好是「Mos」的城市。
• 用双引号包模式。 在 PostgreSQL 和 DuckDB 里，"Mos%" 是列名，不是字符串。要用单引号。
• 只想要前缀，却两边都加了 %。 更慢，而且匹配得太多，Ivanov/Ivanova 的例子就是这样。
• 模式里的 _ 没有转义。 LIKE 'view_%' 能匹配「view_offer」，也能匹配「viewX」或「views」；如果你要的是真正的下划线，就要转义。
• 以为 NOT LIKE 会返回 NULL 行。 永远不会；需要的话加上 OR col IS NULL。
• 组合多个模式时混用 AND 和 OR 却不加括号。

找到字符串之后，要从中截取部分内容（SPLIT_PART、SUBSTRING、REPLACE），见 SQL 字符串函数大全。模式筛选也收录在 SQL 速查表里。

常见问题

SQL LIKE 里的 % 是什么意思？

% 匹配任意长度的字符序列，包括空字符串。'Mos%' 能匹配「Mos」「Moscow」以及任何以「Mos」开头的值。_ 恰好匹配一个字符，一个汉字也算一个字符。

SQL LIKE 区分大小写吗？

在 PostgreSQL、DuckDB 和 Oracle 里区分。在 MySQL 和 SQL Server 里取决于排序规则，而默认的排序规则不区分大小写。在 PostgreSQL 或 DuckDB 里要不区分大小写，就用 ILIKE；任何数据库里都可以用 lower(col) LIKE 'pattern'。

LIKE 里怎样转义下划线或百分号？

在它前面加一个转义字符，并用 ESCAPE 声明：col LIKE '%\_%' ESCAPE '\' 会找出含有真正下划线的值。PostgreSQL 和 MySQL 默认用反斜杠，DuckDB 和 SQL Server 没有默认转义字符，所以显式的 ESCAPE 子句是通用写法。SQL Server 还接受 '%[_]%'。

LIKE 能同时匹配多个值吗？

不能直接匹配。用 OR 组合条件（city LIKE 'Nov%' OR city LIKE 'Nor%'），在 PostgreSQL 里用 LIKE ANY (ARRAY[...])，或者改用正则表达式（REGEXP、RLIKE、~）。

LIKE 比 = 慢吗？

带固定前缀的 LIKE 能使用索引，几乎和 = 一样快。以 % 开头的模式必须检查每一行，在大表上会慢很多，除非加上三元组索引（PostgreSQL）或全文索引。

LIKE 能用在数字上吗？

在 PostgreSQL 里需要先转换类型：CAST(col AS TEXT) LIKE '12%'。MySQL 会自动转换。对数字来说，范围条件（col BETWEEN 1200 AND 1299）几乎总是更好的选择。

其他语言： English, Español, Português (Brasil), Tiếng Việt