【数据库原理】模糊匹配

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 的用法，以及通配符的意义和正则表达式的应用，你可以灵活高效地实现各种模糊查询。在实际应用中，还需要注意性能优化，尤其是在大数据量的表中进行模糊匹配时。