MySQL - RLIKE 运算符

MySQL：使用正则表达式进行模式匹配

MySQL 通过正则表达式提供了强大的模式匹配功能。这允许你执行远超简单 LIKE 子句的复杂搜索。主要的操作符是 RLIKE 及其别名 REGEXP。

正则表达式（或 regex）是一个字符序列，用于指定搜索模式。它是许多编程语言和工具中用于字符串操作和验证的标准工具。

RLIKE 和 REGEXP 运算符

RLIKE 运算符（及其相同的同义词 REGEXP）用于 WHERE 子句中，以根据正则表达式模式匹配字符串值。如果模式匹配字符串的任何部分，则返回 1（真），否则返回 0（假）。

语法

SELECT column_list FROM table_name
WHERE string_column RLIKE 'pattern';

常见正则表达式模式

以下是一些最常用的模式：

模式	匹配内容
^	字符串的开头。^a 匹配 ‘apple’ 但不匹配 ‘banana’。
$	字符串的结尾。a$ 匹配 ‘banana’ 但不匹配 ‘apple’。
.	任何单个字符。h.t 匹配 ‘hat’、‘hot’ 和 ‘h/t’。
*	前一个元素的零次或多次出现。a* 匹配 ”、‘a’、‘aa’。
+	前一个元素的一次或多次出现。a+ 匹配 ‘a’、‘aa’ 但不匹配 ”。
[abc]	集合中的任何单个字符。[hc]at 匹配 ‘hat’ 和 ‘cat’。
[^abc]	不在集合中的任何单个字符。[^b]at 匹配 ‘hat’、‘cat’ 但不匹配 ‘bat’。
[a-z]	指定范围内的任何字符。[0-9] 匹配任何数字。
p1|p2	选择。匹配 p1 或 p2。cat|dog 匹配 ‘cat’ 或 ‘dog’。

实际示例

让我们使用 CUSTOMERS 表作为示例。

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    EMAIL VARCHAR(255)
);

INSERT INTO CUSTOMERS(NAME, EMAIL) VALUES
('Ramesh', 'ramesh@example.com'),
('Khilan', 'khilan.k@test.org'),
('Kaushik', 'kaushik@example.com'),
('Chaitali', 'chaitali_t@web.net'),
('Komal', 'komal@test.org');

示例 1：查找以 ‘K’ 开头的名称

SELECT NAME, EMAIL FROM CUSTOMERS WHERE NAME RLIKE '^K';

示例 2：查找以 ‘l’ 结尾的名称

SELECT NAME, EMAIL FROM CUSTOMERS WHERE NAME RLIKE 'l$';

示例 3：查找 ‘.org’ 电子邮件地址

这会查找任何电子邮件地址中包含 ‘.org’ 的客户。

SELECT NAME, EMAIL FROM CUSTOMERS WHERE EMAIL RLIKE '\.org$';
-- 注意：我们转义了点（\.），因为 '.' 是一个特殊的正则表达式字符。

示例 4：使用交替

查找包含 ‘sh’ 或 ‘al’ 的名称。

SELECT NAME, EMAIL FROM CUSTOMERS WHERE NAME RLIKE 'sh|al';

现代正则表达式函数（MySQL 8.0+）

MySQL 8.0 引入了一组更强大、更具体的正则表达式函数。在现代开发中，通常首选这些函数而非 RLIKE/REGEXP，因为它们提供了更多的控制和功能。

• REGEXP_LIKE(expr, pat)：REGEXP 运算符的显式函数式等价物。清晰度高。
• REGEXP_INSTR(expr, pat)：返回匹配模式的子字符串的起始索引。
• REGEXP_REPLACE(expr, pat, repl)：用新字符串替换模式的出现。
• REGEXP_SUBSTR(expr, pat)：提取匹配模式的子字符串。

示例：提取域名

让我们使用 REGEXP_SUBSTR 从电子邮件地址中仅提取域名。模式 @(.*) 捕获 ’@’ 符号之后的所有内容。

SELECT NAME, EMAIL, REGEXP_SUBSTR(EMAIL, '@(.*)') AS domain
FROM CUSTOMERS;

示例：匿名化电子邮件

让我们使用 REGEXP_REPLACE 隐藏电子邮件中的用户名部分。模式 ^.*@ 匹配字符串开头直到（包括）’@’ 的所有内容。

SELECT NAME, EMAIL, REGEXP_REPLACE(EMAIL, '^.*@', '******@') AS anonymized_email
FROM CUSTOMERS;

性能和最佳实践

• 性能警告：正则表达式搜索无法有效利用标准 B-Tree 索引。在大型表上使用 RLIKE 的查询几乎总是会导致全表扫描，这可能非常慢。
• 对简单模式使用 LIKE：如果你的模式很简单（例如，以…开头，以…结尾，包含…），请使用 LIKE。col LIKE 'K%' 比 col RLIKE '^K' 快得多。
• 考虑全文搜索：对于大型文本字段中的复杂单词和短语搜索，MySQL 的全文搜索 (FTS) 是比正则表达式更高效、功能更丰富的替代方案。
• 使用锚点：在可能的情况下，使用 ^（开始）和 $（结束）锚点。这有助于正则表达式引擎更快地在不匹配的字符串上失败。

在应用程序代码中使用 RLIKE

从应用程序中使用正则表达式时，使用参数化查询来防止 SQL 注入至关重要。即使模式是字符串，也应将其作为参数传递。

Node.js (使用 mysql2)
Python (使用 mysql-connector-python)



### 代码
此示例查找电子邮件地址中包含 `.com` 的客户。

```javascript
// 文件名：queryRlike.js
const mysql = require('mysql2/promise');

async function findCustomersByEmailPattern(pattern) {
  let connection;
  try {
    const pool = mysql.createPool({
      host: 'localhost', user: 'root', password: 'password', database: 'TUTORIALS'
    });

    const sql = "SELECT NAME, EMAIL FROM CUSTOMERS WHERE EMAIL RLIKE ?";
    const params = [pattern];

    const [rows, fields] = await pool.query(sql, params);

    console.log(`Customers with email matching pattern '${pattern}':`);
    rows.forEach(row => {
      console.log(`Name: ${row.NAME}, Email: ${row.EMAIL}`);
    });

    await pool.end();

  } catch (error) {
    console.error('Database query failed:', error);
  }
}

// 查找所有 .com 电子邮件地址。请注意字符串中用于转义的双反斜杠。
findCustomersByEmailPattern('\\.com$');

代码

此示例查找电子邮件地址中包含 .com 的客户。

query_rlike.py

import mysql.connector
from mysql.connector import Error

def find_customers_by_email_pattern(pattern):
    """ 通过电子邮件上的正则表达式模式查找客户。 """
    try:
        with mysql.connector.connect(
            host='localhost', user='root', password='password', database='TUTORIALS'
        ) as connection:

            query = "SELECT NAME, EMAIL FROM CUSTOMERS WHERE EMAIL RLIKE %s"
            params = (pattern,)

            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, params)
                results = cursor.fetchall()

                print(f"Customers with email matching pattern '{pattern}':")
                for row in results:
                    print(f"Name: {row['NAME']}, Email: {row['EMAIL']}")

    except Error as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    # 查找所有 .com 电子邮件地址
    find_customers_by_email_pattern(r'\.com$')