SQL LIKE 查询从入门到工程实践：索引优化、通配符转义与注入防护

SQL 里的LIKE应该是接触数据库管理系统时最早遇到的查询关键字之一。很多教程会告诉你“%代表任意多个字符_代表一个字符”然后给两个例子就结束了。但实际在业务里写LIKE会碰到大小写、转义、索引失效、慢查询、注入风险、通配符误匹配等一系列问题。这篇就把LIKE从语法到性能、从安全到批量任务完整拆一遍重点看它能不能走索引、什么时候不能走、怎么在真实业务里安全地使用它。这次我们只围绕一件事LIKE的完整用法和工程化实践。看完你不仅能写对LIKE查询还能理解LIKE在慢 SQL 优化和 SQL 注入防护这两个高频场景里的位置。文章会按“核心语法 - 适用场景 - 环境准备 - 功能测试 - 性能分析 - 接口/批量任务 - 安全边界 - 问题排查 - 最佳实践”的顺序展开。每个部分都配可执行的 SQL 或代码示例建议收藏备用。1. LIKE 核心能力速览能力项说明所属范畴数据库管理系统中的条件查询关键字核心功能字符串模式匹配支持通配符%和_通配符%匹配任意长度字符串含空串_匹配单个字符转义方式ESCAPE子句自定义转义字符大小写敏感度取决于数据库排序规则collationMySQL 默认不敏感PostgreSQL 敏感索引使用前缀匹配LIKE abc%可走索引前导通配符LIKE %abc通常索引失效替代方案LOCATE、INSTR、POSITION、全文索引、正则表达式典型风险前导通配符导致全表扫描未转义的通配符导致 SQL 注入或误匹配适合场景模糊搜索、日志过滤、批量数据匹配、业务编码规则校验LIKE功能很简单真正的复杂度在“能不能走索引”“怎么防注入”“怎么批量执行”这些工程问题上。2. 适用场景与使用边界2.1 适合场景LIKE最适合做模糊匹配例如搜索用户名、商品名、订单号片段、文件名前缀。配合ESCAPE可以做特殊字符精确包含查询比如查询包含%或_的文本。在数据清洗场景中LIKE常用来识别“包含某个关键字”的记录再配合UPDATE或DELETE处理。批量任务中LIKE常用于按模式筛选数据例如筛选某时间段生成的文件名、某前缀的订单号。2.2 不适合场景大规模全文搜索。数据量超过百万级LIKE %关键词%几乎无法走索引应该优先考虑全文索引或 Elasticsearch 等搜索引擎。复杂模式匹配。如果需求是“匹配邮箱格式”“匹配手机号”建议使用数据库的正则表达式函数。高频核心路径查询。LIKE的模糊匹配语义对索引不友好核心接口应避免用前导通配符查询。2.3 使用边界与安全提醒涉及用户输入时LIKE的%和_需要特殊处理。如果直接拼接进 SQL会带来 SQL 注入风险。在涉及个人隐私、肖像、声音、文本等数据查询时必须确认数据来源合法、使用范围合规。使用LIKE做数据匹配前要确认字段的字符集和排序规则否则可能出现中文匹配不到、大小写不敏感但预期敏感等问题。禁止在未授权数据库上执行LIKE扫描测试。所有测试应在本地测试库中进行。3. 环境准备与 SQL 执行工具LIKE的语法在所有主流数据库管理系统MySQL、PostgreSQL、SQL Server、SQLite中基本通用以下验证方案以 MySQL 为主兼容其他数据库。3.1 环境检查清单检查项推荐要求操作系统Windows / Linux / macOS 均可数据库MySQL 5.7 或 MySQL 8.0客户端mysql 命令行或 DBeaver / Navicat测试库单独的本地测试库避免影响正式数据字符集utf8mb4避免中文乱码影响 LIKE 匹配结果3.2 创建测试表并写入样例数据先创建一个简单的用户表用于后续所有 LIKE 测试CREATE DATABASE IF NOT EXISTS like_test DEFAULT CHARACTER SET utf8mb4; USE like_test; CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(100), remark VARCHAR(200) ); INSERT INTO users (name, email, remark) VALUES (Alice, aliceexample.com, VIP member), (Bob, bobexample.com, normal user), (Charlie, charlietest.org, VIP user), (David, davidexample.net, test account), (Eve, evetest.org, VIP), (Frank, frankexample.com, internal 100% system), (Grace, gracetest.org, admin_vip_user);插入完成后先验证基础数据SELECT * FROM users;到这里测试环境已经就绪。下面所有测试都基于该表。4. LIKE 语法详解与功能测试4.1 基础语法LIKE的基本语法SELECT 字段列表 FROM 表名 WHERE 字段 LIKE 模式;模式中可以使用两个通配符通配符含义%匹配任意数量的字符包括 0 个字符_匹配任意 1 个字符测试用例 1查询所有邮箱以example.com结尾的用户。SELECT id, name, email FROM users WHERE email LIKE %example.com;预期结果Alice、Bob、Frank的邮箱都以example.com结尾其余数据不返回。测试用例 2查询名字中第二个字符是a的用户。SELECT id, name FROM users WHERE name LIKE _a%;预期的_匹配任意一个字符第二个字符为a后续可以有任意字符。David符合条件因为D是第一个字符a是第二个字符。测试用例 3查询remark中同时包含VIP和user的记录。SELECT * FROM users WHERE remark LIKE %VIP%user%;这里隐式依赖顺序只要文本中先出现VIP后出现user就可以匹配到。4.2 转义通配符测试业务中经常要查“包含百分号”或“包含下划线”的文本。因为%和_是通配符必须使用ESCAPE指定转义字符默认转义字符是反斜杠\但不同数据库支持度不一样。推荐显式指定。测试用例 4查询remark中包含字面量100%的记录。SELECT * FROM users WHERE remark LIKE %100\%% ESCAPE \\;如果写成SELECT * FROM users WHERE remark LIKE %100%%;会导致全表任意包含100的记录都匹配成功而且行为不符合预期。这里要注意MySQL 默认转义字符是反斜杠但如果sql_mode包含NO_BACKSLASH_ESCAPES反斜杠不再作为转义符。最稳妥的方式是用ESCAPE显式指定一个字符比如#SELECT * FROM users WHERE remark LIKE %100#%% ESCAPE #;其中第一个#%表示字面量百分号最后的%表示任意后续字符。4.3 大小写敏感性测试大小写是否敏感取决于排序规则。在 MySQL 中utf8mb4_general_ci不区分大小写LIKE alice%能匹配Alice。utf8mb4_bin区分大小写LIKE alice%匹配不到Alice。测试方法SELECT id, name FROM users WHERE name LIKE a%;如果结果包含Alice说明当前排序规则不区分大小写。在 PostgreSQL 中SELECT id, name FROM users WHERE name LIKE a%;默认LIKE区分大小写Alice不会被匹配。如果要用不区分大小写的匹配在 PostgreSQL 中使用ILIKE或转为小写。在 SQL Server 中大小写由数据库排序规则决定通常安装时默认不区分。4.4 反向匹配NOT LIKE用于排除匹配某个模式的记录。测试用例 5查询邮箱不以example.com结尾的用户。SELECT id, name, email FROM users WHERE email NOT LIKE %example.com;注意NOT LIKE在字段为NULL时返回UNKNOWN过滤后不会出现在结果中。如果遇到空值问题需要补充IS NULL判断。5. LIKE 性能分析与慢 SQL 排查字符串匹配在数据量大的时候很容易变成慢 SQL。这一节重点讲LIKE的索引使用规则和排查方法。5.1 什么情况下 LIKE 能走索引结论是只要模式以固定前缀开头且匹配字段上有索引LIKE就可以走索引。用一个实验验证。先给email字段加索引ALTER TABLE users ADD INDEX idx_email (email);然后查看执行计划EXPLAIN SELECT * FROM users WHERE email LIKE alice%;如果执行计划中type为range说明走了索引。前缀匹配alice%可以走索引。再看前导通配符EXPLAIN SELECT * FROM users WHERE email LIKE %alice%;此时执行计划中type大概率是ALL也就是全表扫描。5.2 为什么前导通配符会让索引失效B 树索引是按字段值的从左到右顺序排列的。LIKE %alice%要求匹配任意位置出现的子串索引无法定位起始点只能逐行扫描。这是数据库管理系统索引结构的固有特性。对比几种模式的索引使用情况模式是否能走索引原因LIKE abc%能有固定前缀索引可定位范围LIKE %abc不能无固定前缀LIKE %abc%不能无固定前缀且中间匹配LIKE abc_def%能前面部分固定_不影响前缀定位字段是函数表达式不能索引建立在原始字段上函数包裹字段导致无法使用索引5.3 慢 SQL 排查流程当LIKE查询变慢时按以下顺序排查查看执行计划确认是否全表扫描。确认模式是否包含前导%。确认字段上是否建立了合适的索引。确认数据量和返回行数是否只需要部分字段。使用EXPLAIN ANALYZE或EXPLAIN EXTENDED查看实际扫描行数。MySQL 8.0 可以使用EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE %alice%;5.4 索引失效的替代方案如果必须做包含匹配可以考虑以下方式使用覆盖索引配合INSTR函数。使用全文索引效果更好。对经常需要模糊搜索的短文本字段考虑引入 Elasticsearch。如果数据量可控可以使用LOCATE(abc, email) 0替代LIKE %abc%但本质上仍无法利用索引只能减少部分解析开销。INSTR示例SELECT * FROM users WHERE INSTR(email, alice) 0;功能上等于LIKE %alice%但语义更明确。注意它依然不能走索引。6. LIKE 在接口 API 与批量任务中的应用LIKE单独使用价值有限在实际工程中通常出现在接口筛选条件、存储过程和批量数据处理任务里。这一节给出可以直接套用的示例。6.1 接口查询参数拼接在 Java、Python、Node.js 后端中模糊查询条件通常是接口参数。直接拼接字符串是非常危险的做法必须使用参数化查询或 ORM 框架的查询构造器。Python 示例import pymysql conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databaselike_test, charsetutf8mb4 ) keyword alice with conn.cursor() as cursor: sql SELECT id, name, email FROM users WHERE email LIKE %s cursor.execute(sql, (f%{keyword}%,)) rows cursor.fetchall() for row in rows: print(row) conn.close()注意LIKE参数本身是%alice%作为参数传给 SQL 语句而不是拼进 SQL 字符串。这样即使keyword里包含%或_也只是字符串数据不会改变 SQL 语义。6.2 存储过程中的 LIKE 使用存储过程常用于批量数据处理LIKE可以结合游标逐行匹配。MySQL 存储过程示例DELIMITER // CREATE PROCEDURE find_users_by_keyword( IN p_keyword VARCHAR(100) ) BEGIN SELECT id, name, email FROM users WHERE email LIKE CONCAT(%, p_keyword, %) OR name LIKE CONCAT(%, p_keyword, %); END // DELIMITER ; CALL find_users_by_keyword(alice);CONCAT(%, p_keyword, %)在存储过程内部拼接模式字符串不需要在调用端手动加百分号。6.3 批量任务按模式筛选并更新假设要批量清洗数据把所有remark包含 “VIP” 且邮箱为test.org的用户标记为“已通知”。先确认筛选结果SELECT id, name, email, remark FROM users WHERE remark LIKE %VIP% AND email LIKE %test.org;确认无误后再执行更新UPDATE users SET remark CONCAT(remark, [notified]) WHERE remark LIKE %VIP% AND email LIKE %test.org;批量任务执行前一定要先 SELECT 确认影响行数再执行 UPDATE。6.4 批量脚本与失败重试在数据量较大时建议使用脚本分批处理。Python 示例import pymysql import time conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databaselike_test, charsetutf8mb4 ) batch_size 100 offset 0 while True: with conn.cursor() as cursor: sql SELECT id, name, email FROM users WHERE remark LIKE %s LIMIT %s OFFSET %s cursor.execute(sql, (%VIP%, batch_size, offset)) rows cursor.fetchall() if not rows: break for row in rows: # 在这里处理每一条记录 print(fProcessing id{row[0]}, name{row[1]}) offset batch_size time.sleep(0.1) # 避免对数据库造成过大压力 conn.close()分页处理时OFFSET数据量大会变慢更优方案是使用游标或基于主键的范围分页。批量任务应加上try-except和失败重试避免一条异常导致整个任务中断。7. LIKE 与 SQL 注入防护LIKE关键字最容易出现安全问题的场景有两个一是通配符没有转义导致 SQL 注入二是拼接字符串导致注入点扩大。7.1 注入风险如果业务代码这样做# 危险写法不要使用 keyword request.args.get(keyword, ) sql fSELECT * FROM users WHERE name LIKE %{keyword}%攻击者传入% OR 11 --之类的输入时SQL 会被改写为SELECT * FROM users WHERE name LIKE %% OR 11 -- %最终结果是绕过查询条件返回全表数据。这在数据库管理系统中属于高危漏洞。7.2 防护方案第一使用参数化查询这是最基础的处理方式。第二如果必须转义用户输入中的特殊字符需要在拼接模式之前处理def escape_like(keyword: str) - str: return keyword.replace(\\, \\\\).replace(%, \\%).replace(_, \\_)使用时safe_keyword escape_like(keyword) sql SELECT * FROM users WHERE name LIKE %s ESCAPE \\\\ cursor.execute(sql, (f%{safe_keyword}%,))这样用户输入的%和_会被视为普通字符不会被当成通配符。7.3 安全使用清单禁止字符串拼接 SQL。所有用户输入必须通过参数绑定传入。通配符必须按业务需求显式转义。测试LIKE查询时至少测试包含%、_、单引号、反斜杠的输入。数据库账号权限最小化应用账号不应具备DROP、TRUNCATE等高危权限。8. LIKE 常见问题与排查方法LIKE写法和查询并不复杂但实际使用中经常遇到下面这些问题。8.1 问题排查表问题现象可能原因排查方式解决方案中文搜索不到记录字符集或排序规则不一致检查表和字段的 character set统一为 utf8mb4重建测试数据大小写与预期不符排序规则为_ci不区分大小写查看 collation按需改用_bin或BINARY比较%和_被当成通配符没有转义检查模式字符串使用ESCAPE显式转义查询很慢全表扫描前导%导致索引失效使用EXPLAIN查看 type改为前缀匹配或引入全文索引带空格的数据匹配不到字段包含前后空格使用CONCAT或TRIM检查清洗数据或使用TRIM(name)NOT LIKE过滤掉 NULL 值SQL 三值逻辑检查字段是否允许 NULL补充OR field IS NULL接口查询传入特殊字符报错未转义单引号或反斜杠查看数据库日志使用参数化查询LIKE匹配表情符号失败字符集不支持检查字符集改为 utf8mb48.2 字符集问题中文匹配不到通常不是LIKE的问题而是表或字段的字符集不是utf8mb4。检查方式SHOW CREATE TABLE users;如果看到latin1或gbk字符集需要转字符集ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;执行前先备份数据。8.3 空格问题LIKE alice%无法匹配alice带尾部空格。排查时可以先看长度SELECT id, name, LENGTH(name) FROM users WHERE id 1;如确认有空格清洗数据UPDATE users SET name TRIM(name) WHERE TRIM(name) name;9. LIKE 最佳实践与使用建议9.1 SQL 编写建议尽量使用前缀匹配。LIKE abc%比LIKE %abc%快很多且可利用索引。必须用包含匹配时控制扫描范围加上其他过滤条件缩小数据量。模式字符串中如果包含用户输入先转义%、_、\。NOT LIKE场景要警惕NULL值被过滤按语义补条件。避免在LIKE前使用函数例如WHERE LOWER(name) LIKE %abc%会导致索引失效。9.2 索引设计建议高频前缀查询字段建议建立普通 B 树索引。不要只为了LIKE建立冗余索引先分析是否真的高频。如果高频LIKE场景较多考虑引入全文索引或外部搜索引擎。联合索引要使用左前缀规则LIKE匹配字段放在联合索引靠后位置意义很小。9.3 工程化建议保留一份最小可运行测试表结构和样例数据方便新同事快速理解LIKE行为。批量任务必须加日志。每处理一批记录输出时间、处理行数、失败行数。批量任务要支持断点续跑。设计主键游标不要单纯依赖OFFSET。接口服务中模糊查询参数要做长度限制和字符白名单。涉及人名的模糊搜索建议结合排序规则明确大小写规则避免业务反馈结果不稳定。9.4 合规与授权提醒数据库中的个人信息查询必须有合法的业务授权不得越权查询他人数据。从公开网络搜索材料中截取的 SQL 示例只用于本地学习不用于生产环境。在正式环境执行UPDATE或DELETE前务必先在同结构测试库验证。发布或商用前要对查询结果做人工复核避免因LIKE通配符误匹配导致数据泄露。10. 总结与下一步LIKE的语法本身是 SQL 入门中最简单的一环但工程中的LIKE远不止一个关键字那么简单。它牵扯出三个核心问题能不能走索引、能不能防注入、能不能应对批量数据。最值得深入验证的功能是前缀匹配的索引效果。建一张百万行测试表分别用LIKE alice%和LIKE %alice%跑一遍EXPLAIN实际观察type从range变成ALL比死记硬背结论更有价值。最容易踩的坑有两个一是用户输入的通配符没有转义导致LIKE变成万能匹配二是在大表上用LIKE %关键词%做搜索把数据库拖到全表扫描。这两个坑在面试题和实际故障中出现频率都很高建议单独建立测试用例反复验证。对于已经熟悉LIKE基本写法的读者下一步可以尝试将LIKE查询改造成参数化接口外加一个批量数据清洗脚本跑通“传入关键字 - 模糊匹配 - 结果导出”的完整链路。这个链路在数据库管理系统类的日常开发任务中出现频率相当高。如果你正在重温 SQL 基础建议把这里提到的排序规则、索引失效、转义规则、注入防护四个知识点整理成自己的笔记。真正能区分新手和熟练开发者的往往不是LIKE本身而是这些围绕LIKE展开的边界情况。