SQL 模糊匹配速成指南
模糊匹配是 SQL 中用于查找符合部分条件的数据的强大工具,尤其在查询字段的部分值、前缀、后缀或包含某些字符时非常有用。
SQL 中常用的模糊匹配工具是 LIKE 和 REGEXP(不同数据库支持的语法可能略有不同)。
1. LIKE 模糊匹配
LIKE 是 SQL 中最常用的模糊匹配操作符,配合通配符使用:
通配符
• %:匹配零个或多个任意字符。
• _:匹配单个任意字符。
语法
SELECT columns
FROM table
WHERE column_name LIKE 'pattern';
常见模式
1. 以某个字符开头:
SELECT * FROM Employees WHERE Name LIKE 'A%';
匹配以 A 开头的名字。
2. 以某个字符结尾:
SELECT * FROM Employees WHERE Name LIKE '%n';
匹配以 n 结尾的名字。
3. 包含某些字符:
SELECT * FROM Employees WHERE Name LIKE '%li%';
匹配名字中包含 li 的记录。
4. 匹配固定长度:
SELECT * FROM Employees WHERE Name LIKE 'A___';
匹配以 A 开头,且长度为 4 的名字。
5. 复杂组合:
SELECT * FROM Employees WHERE Name LIKE 'J%o_n';
匹配以 J 开头,包含 o,并以 n 结尾的名字。
2. NOT LIKE
如果需要排除符合某种模式的记录,可以使用 NOT LIKE:
SELECT * FROM Employees WHERE Name NOT LIKE 'A%';
匹配所有不以 A 开头的名字。
3. 大小写敏感性
• 在 MySQL 中,LIKE 默认对大小写不敏感(取决于列的字符集和排序规则)。
• 在 PostgreSQL 或其他数据库中,可能需要使用 ILIKE 来进行大小写不敏感的模糊匹配:
SELECT * FROM Employees WHERE Name ILIKE 'A%';
4. 使用正则表达式(REGEXP/RLIKE)
如果需要更强大的匹配功能,可以使用正则表达式:
语法
SELECT columns
FROM table
WHERE column_name REGEXP 'pattern';
常见模式
1. 以某个字符开头:
SELECT * FROM Employees WHERE Name REGEXP '^A';
2. 以某个字符结尾:
SELECT * FROM Employees WHERE Name REGEXP 'n$';
3. 包含某些字符:
SELECT * FROM Employees WHERE Name REGEXP 'li';
4. 匹配固定长度:
SELECT * FROM Employees WHERE Name REGEXP '^.{4}$';
匹配长度为 4 的名字。
5. 复杂组合:
SELECT * FROM Employees WHERE Name REGEXP '^J.*o.n$';
匹配以 J 开头,包含 o 和任意单个字符 n 结尾的名字。
5. 常见使用
1. 模糊查找用户数据:
SELECT * FROM Users WHERE Email LIKE '%@gmail.com';
查找所有 Gmail 邮箱的用户。
2. 查找电话号码模式:
SELECT * FROM Contacts WHERE Phone REGEXP '^\\d{3}-\\d{4}$';
查找符合 XXX-XXXX 格式的电话号码。
3. 商品模糊搜索:
SELECT * FROM Products WHERE Name LIKE '%Phone%';
查找包含 “Phone” 的商品。
6. 性能优化
• 避免在大数据表中直接使用模糊匹配:LIKE '%xxx%' 会导致全表扫描。
• 索引的影响:索引在 % 开头的匹配中无效,但在 % 不在开头的情况下可能有用。
• 使用全文索引(Full-Text Index):对于大文本数据的匹配,全文索引性能更高。
7. 示例练习
题目 1:查询名字中包含 a 的所有学生。
SELECT * FROM Students WHERE Name LIKE '%a%';
题目 2:查询以 Pro 开头的所有商品。
SELECT * FROM Products WHERE Name LIKE 'Pro%';
题目 3:查询员工的名字长度为 5 且以 n 结尾。
SELECT * FROM Employees WHERE Name LIKE '____n';
题目 4:查找所有邮箱域名为 yahoo.com 的用户。
SELECT * FROM Users WHERE Email LIKE '%@yahoo.com';
题目 5:查询商品编号是以数字开头,后面跟着 AB 的所有记录。
SELECT * FROM Products WHERE ProductCode REGEXP '^[0-9]+AB';
总结
模糊匹配是 SQL 查询中非常常用的工具。通过掌握 LIKE 和 REGEXP 的用法,以及通配符的意义和正则表达式的应用,你可以灵活高效地实现各种模糊查询。在实际应用中,还需要注意性能优化,尤其是在大数据量的表中进行模糊匹配时。