正则表达式(REGEXP)与通配符(LIKE)的超详细对比

1. regexp (正则表达式匹配)

用途与特点

regexp (正则表达式) 是一种强大的字符串匹配工具，用于执行高级模式匹配操作。它提供了比简单通配符更精确、更灵活的文本搜索能力，特别适合处理复杂的字符串匹配场景。

主要特点：

• 支持复杂的模式匹配（如开头、结尾、字符集等）
• 可以实现精确的字符次数控制
• 提供分组、或操作等高级功能
• 适用于验证、提取和替换文本中的特定模式

基本语法

select column_name from table_name where column_name regexp 'pattern';

其中：

• column_name是要进行匹配的列名
• table_name是要查询的表名
• pattern是要匹配的正则表达式模式

正则表达式元素详解

基本元字符

1. .：匹配任意单个字符（除了换行符）
   • 示例：a.c 匹配 "abc"、"aac"、"a1c" 等
2. ^：匹配字符串的开始位置
   • 示例：^a 匹配以"a"开头的字符串
3. $：匹配字符串的结束位置
   • 示例：a$ 匹配以"a"结尾的字符串

字符类

1. []：指定一个字符集合，匹配其中的任意字符
   • 示例：[abc] 匹配"a"、"b"或"c"
   • 范围模式：[a-z] 匹配任意小写字母
2. [^]：指定一个不匹配的字符集合
   • 示例：[^abc] 不匹配"a"、"b"或"c"

数量限定符

1. *：匹配前面的模式零次或多次
   • 示例：ab*c 匹配"ac"、"abc"、"abbc"等
2. +：匹配前面的模式一次或多次
   • 示例：ab+c 匹配"abc"、"abbc"，但不匹配"ac"
3. ?：匹配前面的模式零次或一次
   • 示例：ab?c 匹配"ac"或"abc"
4. {n}：精确匹配前面的模式n次
   • 示例：a{2} 匹配"aa"
5. {n,}：匹配前面的模式至少n次
   • 示例：a{2,} 匹配"aa"、"aaa"等
6. {n,m}：匹配前面的模式至少n次且不超过m次
   • 示例：a{2,4} 匹配"aa"、"aaa"或"aaaa"

特殊构造

1. |：逻辑"或"操作符
   • 示例：abc|def 匹配"abc"或"def"
2. ()：用于组合模式
   • 示例：a(bc)* 匹配"a"、"abc"、"abcbc"等

实际应用示例

基本匹配

-- 查找包含字母"a"的所有行
select * from products where product_name regexp 'a';

位置匹配

-- 查找以"pro"开头的产品
select * from products where product_name regexp '^pro';

-- 查找以"2023"结尾的订单号
select * from orders where order_id regexp '2023$';

字符集匹配

-- 查找包含数字的电话号码
select * from customers where phone regexp '[0-9]';

-- 查找不包含元音字母的产品名称
select * from products where product_name regexp '[^aeiou]';

复杂模式

-- 查找包含"error"或"warning"的日志记录
select * from system_logs where message regexp 'error|warning';

-- 查找标准的电子邮件格式
select * from users where email regexp '^[a-za-z0-9._%-]+@[a-za-z0-9.-]+\\.[a-za-z]{2,4}$';

-- 查找重复字母的单词
select * from words where word regexp '([a-za-z])\\1';

2. like (通配符匹配)

用途与特点

like 是sql中用于简单字符串匹配的操作符，使用通配符进行模式匹配。它适合简单的字符串搜索和匹配需求。

主要特点：

• 语法简单，易于使用
• 性能通常优于正则表达式
• 适合简单的模式匹配需求
• 支持两种基本通配符：%和_

基本语法

select column_name from table_name where column_name like 'pattern';

其中：

• % 匹配任意数量的字符（包括零个字符）
• _ 匹配单个任意字符

通配符详解

1. %：匹配任意数量的字符（零个或多个）

   • a%：以"a"开头的字符串
   • %a：以"a"结尾的字符串
   • %a%：包含"a"的字符串

2. _：匹配单个任意字符

   • a_c：匹配"abc"、"a1c"、"a c"等
   • _a%：第二个字符是"a"的字符串

实际应用示例

基本匹配

-- 查找包含"book"的产品
select * from products where product_name like '%book%';

位置匹配

-- 查找以"mr."开头的人名
select * from customers where name like 'mr.%';

-- 查找以".com"结尾的邮箱
select * from users where email like '%.com';

精确匹配

-- 查找5个字符长度的产品代码
select * from products where product_code like '_____';

-- 查找第二和第三个字符为"12"的订单号
select * from orders where order_id like '_12%';

特殊字符匹配

-- 查找包含百分号(%)的记录(使用转义)
select * from data where notes like '%\%%' escape '\';

-- 查找包含下划线(_)的记录
select * from data where notes like '%\_%' escape '\';

性能优化技巧

1. 避免在模式开头使用通配符：

   -- 避免这样写（无法使用索引）
   select * from users where name like '%smith';
   
   -- 改为这样写（可以使用索引）
   select * from users where name like 'smith%';
   

2. 对于固定前缀的查询，考虑使用范围查询：

   -- 不如
   select * from products where product_name like 'a%';
   
   -- 可以考虑
   select * from products where product_name >= 'a' and product_name < 'b';
   

3. 对于简单的相等匹配，使用=而非like：

   -- 更好
   select * from users where username = 'admin';
   
   -- 不如
   select * from users where username like 'admin';
   

3. regexp与like的区别与选择

主要区别

选择建议

1. 使用like的情况：

   • 只需要简单的字符串包含、开头或结尾匹配
   • 查询性能是关键考虑因素
   • 对大型表进行前缀搜索（可以使用索引）
   • 模式简单且不需要复杂逻辑

   -- 适合使用like的示例
   select * from customers where name like 'john%';
   

2. 使用regexp的情况：

   • 需要复杂的模式匹配（如特定格式验证）
   • 需要匹配字符集合或排除特定字符
   • 需要精确控制重复次数
   • 需要"或"逻辑等高级功能

   -- 适合使用regexp的示例
   select * from products where product_code regexp '^[a-z]{2}[0-9]{4}$';
   

3. 混合使用场景： 在某些情况下，可以结合使用两者以获得最佳性能和灵活性：

   -- 先用like缩小范围，再用regexp精确匹配
   select * from logs 
   where message like '%error%' 
   and message regexp 'error [0-9]{3}';
   

性能对比示例

假设有一个包含100万条记录的用户表：

-- like查询（较快，可以使用索引）
select * from users where username like 'admin%';

-- 等效的regexp查询（较慢，无法使用索引）
select * from users where username regexp '^admin';

对于这个简单的前缀匹配，like通常比regexp快10-100倍，特别是在有适当索引的情况下。

4. 高级技巧与注意事项

正则表达式优化

1. 避免过度复杂的模式：复杂的正则表达式会显著降低查询速度

2. 使用非贪婪匹配：在支持的情况下，使用*?或+?进行非贪婪匹配

   -- 匹配最短的可能结果
   select * from text where content regexp '<div>.*?</div>';
   

3. 预编译正则表达式：某些数据库支持预编译正则表达式以提高性能

通配符使用技巧

1. escape子句：处理包含通配符本身的字符串

   select * from data where notes like '50\%%' escape '\';
   

2. 字符集处理：考虑数据库的字符集和排序规则对匹配的影响

3. 大小写敏感：大多数数据库的like是大小写敏感的，但可以通过设置改变

数据库特定差异

1. mysql：

   • regexp是rlike的同义词
   • 支持基本的正则表达式功能
   • regexp默认不区分大小写，使用regexp binary进行区分大小写匹配

2. postgresql：

   • 使用~运算符进行正则匹配
   • 支持更完整的正则表达式功能
   • 提供!~运算符进行不匹配操作

3. sql server：

   • 没有内置的regexp功能
   • 可以使用clr集成添加正则表达式支持
   • like功能与其他数据库类似

4. oracle：

   • 提供regexp_like、regexp_replace等函数
   • 支持高级的正则表达式功能

5. 实际应用场景

数据验证

-- 验证电子邮件格式
select * from users where email not regexp '^[a-za-z0-9._%-]+@[a-za-z0-9.-]+\\.[a-za-z]{2,4}$';

-- 验证电话号码格式
select * from customers where phone regexp '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

数据清洗

-- 查找并替换无效字符
update products 
set product_name = regexp_replace(product_name, '[^a-za-z0-9 ]', '')
where product_name regexp '[^a-za-z0-9 ]';

-- 标准化日期格式
update orders 
set order_date = str_to_date(
    regexp_substr(order_date_field, '[0-9]{4}-[0-9]{2}-[0-9]{2}'),
    '%y-%m-%d'
)
where order_date_field regexp '[0-9]{4}-[0-9]{2}-[0-9]{2}';

日志分析

-- 查找特定错误模式的日志记录
select * from server_logs 
where log_message regexp 'error (500|503|504):';

-- 按错误类型分类统计
select 
    regexp_substr(error_message, '[a-za-z]+error') as error_type,
    count(*) as count
from application_logs
where error_message regexp '[a-za-z]+error'
group by error_type;

产品目录搜索

-- 高级产品搜索（支持多种匹配方式）
select * from products
where 
    (product_name like '%organic%' or description like '%organic%')
    and product_code regexp '^[a-z]{2}[0-9]{3}'
    and price between 10 and 100;

6. 总结与最佳实践

1. 基本原则：

   • 简单匹配使用like
   • 复杂模式使用regexp
   • 考虑查询性能影响

2. 性能最佳实践：

   • 对频繁搜索的列创建适当索引
   • 避免在大型表上使用复杂的正则表达式
   • 考虑将正则匹配放在应用层处理

3. 可读性建议：

   • 为复杂的正则表达式添加注释
   • 考虑将常用模式存储为视图或函数
   • 在团队中建立一致的编码标准

4. 测试策略：

   • 测试正则表达式与各种输入数据的匹配情况
   • 比较不同方法的性能表现
   • 监控生产环境中的查询性能

通过合理选择和使用regexp与like，可以高效地处理各种字符串匹配需求，同时保持查询性能和代码可维护性。

总结

到此这篇关于正则表达式(regexp)与通配符(like)超详细对比的文章就介绍到这了,更多相关regexp与like对比内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！