SQL like 与 正则 区别

SQL 中的LIKE和正则表达式REGEXP或RLIKE都用于模式匹配但它们在表达能力、语法复杂度、性能上有显著区别。核心区别一览表对比维度LIKE正则表达式匹配粒度通配符%、_元字符、量词、字符类等表达能力弱简单模式匹配极强复杂模式、重复、位置、子匹配大小写敏感取决于数据库排序规则通常可配置如REGEXP BINARY通配符%任意多个字符、_单个字符.任意单个字符、*、、?、{n}等字符类支持无支持[0-9]、[a-z]、\d、\w等位置匹配无法匹配行首/行尾仅全串匹配支持^行首、$行尾转义符ESCAPE子句如LIKE 100\% ESCAPE \\如\.匹配点号性能较快简单扫描即可实现较慢需编译、回溯等索引利用前缀abc%可利用索引无法利用普通索引可尝试全文索引标准性SQL 标准核心功能各数据库扩展语法略有差异详细说明与示例1. 基础语法示例MySQL 风格-- 1. LIKE查找以 abc 开头的字符串 SELECT * FROM t WHERE col LIKE abc%; -- 2. 正则查找以 abc 开头的字符串 SELECT * FROM t WHERE col REGEXP ^abc; -- 3. LIKE查找包含 abc 的字符串任意位置 SELECT * FROM t WHERE col LIKE %abc%; -- 4. 正则查找包含 abc 的字符串任意位置 SELECT * FROM t WHERE col REGEXP abc; -- 5. 正则查找数字开头的字符串 SELECT * FROM t WHERE col REGEXP ^[0-9]; -- 或使用预定义字符类 SELECT * FROM t WHERE col REGEXP ^\\d;2. 正则能但 LIKE 做不到的典型场景需求正则表达式LIKE 解决方案通常不可行手机号格式11位数字^1[0-9]{10}$很难实现邮箱格式^[a-zA-Z0-9._%-][a-zA-Z0-9.-]\.[a-zA-Z]{2,}$无法表达至少包含一个数字[0-9]需要LIKE %0% OR LIKE %1% ...匹配3或5个字符^.{3,5}$需要LIKE ___ OR ____ OR _____排除特定模式^(?!abc).*部分数据库不支持零宽断言无法直接实现3. 性能与索引注意事项-- ✅ LIKE 前缀匹配可使用索引B-Tree SELECT * FROM user WHERE name LIKE 张%; -- ❌ LIKE 中缀或后缀匹配无法使用索引 SELECT * FROM user WHERE name LIKE %张%; -- ❌ 正则表达式几乎无法使用普通索引 SELECT * FROM user WHERE name REGEXP ^张; -- 正则优化的可能方案使用全文索引MySQL -- 或者将常用正则条件转换为 LIKE 前缀条件4. 数据库差异说明MySQLREGEXP不区分大小写取决于 collation、RLIKE同义词PostgreSQL~区分大小写、~*不区分大小写、SIMILAR TO混合风格不推荐SQL Server无内置正则需用LIKE或 CLR 扩展SQLiteREGEXP需要加载扩展默认不可用5. 使用建议场景推荐方案简单前缀匹配如搜索框输入LIKE keyword%简单通配符固定长度的占位LIKE _abc_复杂格式验证邮箱、手机号、IP正则表达式或在应用层验证大数据量 前缀匹配务必用LIKE 索引少数几条记录 极复杂的匹配逻辑正则表达式总结能用LIKE解决的问题就不要用正则性能更好、更易读需要复杂模式、字符类、位置匹配时正则是不二之选对性能敏感的场景百万级以上数据优先考虑LIKE前缀匹配 索引或者改用全文搜索工具Elasticsearch、Sphinx 等