五种主流数据库：字符串匹配

当我们不能完全确定需要查找的信息时，可以使用 SQL 模糊查找的功能进行文本检索，对应的运算符是 LIKE。

本文比较五种主流数据库对于文本模糊查找的实现和差异，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

字符串模糊匹配	MySQL	Oracle	SQL Server	PostgreSQL	SQLite
[NOT] LIKE	??	??	??	??	??
[NOT] ILIKE	?	?	?	??	?
REGEXP_LIKE()	??	??	?	??	?
[NOT] SIMILAR TO	?	?	?	??	?
全文检索	??	??	??	??	??

LIKE 运算符

假如我们想要知道姓“关”的员工有哪些，可以使用以下查询：

SELECT emp_name
FROM employee
WHERE emp_name LIKE '关%';

其中，LIKE 关键字指定了一个字符串匹配模式，查找姓名以“关”字开头的员工。查询返回的结果如下：

emp_name
--------
关兴 
关平 
关羽 

LIKE 运算符支持以下两个通配符，可以用于指定匹配的模式：

• 百分号（%），表示匹配零个或者多个任意字符。
• 下画线（_），表示匹配一个任意字符。

以下是一些常用的模式和匹配的字符串：

• LIKE ‘en%’，匹配以“en”开始的字符串，例如“english”、“end”。
• LIKE ‘%en%’，匹配包含“en”的字符串，例如“length”、“when”。
• LIKE ‘%en’，匹配以“en”结束的字符串，例如“ten”、“when”。
• LIKE ‘Be_’，匹配以“Be”开头，再加上一个任意字符的字符串。例如“Bed”、“Bet”。
• LIKE ‘_e%’，匹配一个任意字符加上“e”开始的字符串，例如“he”、“year”。

由于百分号和下画线是 LIKE 运算符中的通配符，因此，如果我们查找的模式中包含了“%”或者“_”，就需要用到转义字符（escape character）。

转义字符可以将通配符当作普通字符使用。我们首先创建一个测试表：

CREATE TABLE t_like(c1 VARCHAR(200));
INSERT INTO t_like(c1) VALUES ('项目进度：25%已完成');
INSERT INTO t_like(c1) VALUES ('记录日期：2021 年 5 月 25 日');

表 t_like 只有一个字段 c1，数据类型为字符串，表中包含两条记录。假如现在我们需要查找包含“25%”的数据，其中百分号是要查找的内容而不是任意多个字符，可以使用转义字符进行查找：

SELECT c1
FROM t_like
WHERE c1 LIKE '%25#%%' ESCAPE '#';

ESACPE 关键字为 LIKE 运算符指定了一个 # 符号作为转义字符，因此查找模式中的第二个 % 代表了百分号，其他的 % 则是通配符。该查询返回的结果如下：

c1 
----------
项目进度：25%已完成

提示：对于 MySQL 和 PostgreSQL 而言，如果省略 ESCAPE 子句，默认的转义字符为 反斜杠（\）。

使用 LIKE 运算符进行文本查找时，还需要注意英文字母的大小写问题。例如，以下语句使用大写字母查找员工的电子邮箱：

SELECT email
FROM employee
WHERE email LIKE 'M%';

该查询在不同的数据库中返回的结果不同：

-- MySQL、Microsoft SQL Server 以及 SQLite
email 
----------------
madai@shuguo.com
mizhu@shuguo.com

-- Oracle 以及 PostgreSQL
email
-----

对于 LIKE 运算符，MySQL、Microsoft SQL Server 以及 SQLite 中的字符串默认不区分大小写，Oracle 和 PostgreSQL 中的字符串默认区分大小写。

提示：PostgreSQL 提供了不区分大小写的 ILIKE 运算符，使用方法和 LIKE 相同。 Microsoft SQL Server 支持使用方括号匹配（[]）或者不匹配（[^]）指定范围或集合内的任何单个字符，例如[a-z]表示匹配字符 a 到字符 z。

NOT LIKE 运算符可以执行与 LIKE 运算符相反的操作，也就是返回不匹配某个模式的文本。例如，以下语句查找 t_like 表中不包含“25%”的记录：

SELECT c1
FROM t_like
WHERE c1 NOT LIKE '%25#%%' ESCAPE '#';

查询返回的结果如下：

c1 
---------------
记录日期：2021 年 5 月 25 日

正则表达式函数

正则表达式能够提供更强大的模式匹配功能，MySQL、Oracle 以及 PostgreSQL 都提供了一些列的正则表达式函数，其中匹配字符串的函数是 REGEXP_LIKE()。例如：

SELECT regexp_like('SQL', '^s', 'c'), regexp_like('SQL', '^s', 'i');

regexp_like('SQL', '^s', 'c')|regexp_like('SQL', '^s', 'i')|
-----------------------------+-----------------------------+
                            0|                            1|

REGEXP_LIKE() 函数的第一个参数是搜索的字符串，第二个参数是正则表达式，第三个可选的参数指定是否区分大小写等匹配行为。

完整的正则表达式语法可以参考具体数据库文档。

PostgreSQL 还实现了 SIMILAR TO 运算符，它在 LIKE 运算符的基础上增加了一些正则表达式功能。例如：

-- PostgreSQL
SELECT 'SQL' SIMILAR TO '[A-Z]{3}';

?column?|
--------+
true    |

其中，[A-Z] 表示大写字母，{3} 代表重复 3 次。

全文检索

全文检索通过将文本内容进行分词和索引，可以实现高效的全文搜索和语义查询。MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 都实现了数据库全文检索功能。