MySQL - regexp_like() 函数

MySQL：REGEXP_LIKE() 函数

在 MySQL 中，模式匹配对于数据过滤至关重要。虽然 LIKE 运算符处理简单的通配符搜索，但 REGEXP_LIKE() 提供了正则表达式的全部功能，以执行复杂而精确的模式验证。

与返回位置 (REGEXP_INSTR()) 或匹配字符串 (REGEXP_SUBSTR()) 的函数不同，REGEXP_LIKE() 作为布尔检查。它只是告诉您字符串中是否存在某个模式，这使得它非常适合在 WHERE 子句中使用，以便根据复杂的规则过滤记录。

理解 REGEXP_LIKE() 函数

REGEXP_LIKE() 函数在 MySQL 8.0 及更高版本中可用，它根据正则表达式模式评估字符串。如果找到模式，它返回 1（真）；如果未找到，则返回 0（假）；如果表达式或模式中的任何一个为 NULL，则返回 NULL。它的功能等同于 RLIKE 或 REGEXP 运算符，但提供了更清晰、更面向函数的语法。

语法

REGEXP_LIKE() 函数的语法是：

REGEXP_LIKE(expr, pattern[, match_type])

参数

• expr: 要检查的源字符串。
• pattern: 要搜索的正则表达式模式。
• match_type: (可选) 修改匹配行为的字符串。常见选项包括 ‘i’ 表示不区分大小写，‘c’ 表示区分大小写（默认），‘m’ 表示多行模式，‘n’ 表示允许 ’.’ 匹配行终止符。

实际示例

示例 1：基本模式检查

此查询检查字符串是否包含单词 ‘Tutorials’。

SELECT REGEXP_LIKE('Welcome to TutorialsPoint!', 'Tutorials') AS result;

找到模式，因此函数返回 1。

result
1

示例 2：不区分大小写匹配

使用 ‘i’ 匹配类型，我们可以找到 ‘tutorials’，无论其大小写如何。

SELECT REGEXP_LIKE('Welcome to TutorialsPoint!', 'tutorials', 'i') AS result;

即使大小写不同，匹配也成功。

result
1

示例 3：处理 NULL 值

如果表达式或模式中的任何一个为 NULL，则结果为 NULL。

SELECT REGEXP_LIKE('Some text', NULL) AS result_null_pattern;
SELECT REGEXP_LIKE(NULL, 'some pattern') AS result_null_expr;

两个查询都将返回 NULL。

result_null_pattern
NULL

使用 REGEXP_LIKE() 过滤表数据

REGEXP_LIKE() 的真正强大之处在于它在 WHERE 子句中的应用。让我们建立一个示例表。

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(25),
    SALARY DECIMAL(18, 2)
);

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

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

正则表达式中的 ^ 锚点匹配字符串的开头。此查询查找所有姓名以 ‘K’ 开头的客户。

SELECT NAME, ADDRESS FROM CUSTOMERS WHERE REGEXP_LIKE(NAME, '^K');

这会返回所有满足条件的姓名。

NAME	ADDRESS
Khilan	Delhi
Kaushik	Kota
Komal	Hyderabad

示例 5：查找包含 ‘r’ 的地址（不区分大小写）

此查询查找地址中包含字母 ‘r’ 或 ‘R’ 的所有客户。

SELECT NAME, ADDRESS FROM CUSTOMERS WHERE REGEXP_LIKE(ADDRESS, 'r', 'i');

结果包含带有 ‘r’ 的地址。

NAME	ADDRESS
Ramesh	Ahmedabad
Komal	Hyderabad
Muffy	Indore

最佳实践：LIKE vs. REGEXP_LIKE

对于简单的前缀、后缀或子字符串搜索，LIKE 运算符明显更快，应优先使用。REGEXP_LIKE 资源消耗更高，应保留给无法用 LIKE 的 % 和 _ 通配符表达的模式。

• 对以下情况使用 LIKE： WHERE NAME LIKE 'K%'（更快）
• 对以下情况使用 REGEXP_LIKE： WHERE REGEXP_LIKE(NAME, '^K')（更强大，但在这种简单情况下较慢）
• 对复杂模式使用 REGEXP_LIKE： WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')（无法通过 LIKE 实现）

在客户端应用程序中使用 REGEXP_LIKE()

您可以轻松地在应用程序代码中使用 REGEXP_LIKE 来执行强大的服务器端数据验证和过滤。以下是现代示例。

Python 示例 (使用 mysql-connector-python)

import mysql.connector

# 最佳实践：使用配置函数或文件来存储凭据
def get_db_connection():
    return mysql.connector.connect(
        host='localhost',
        user='root',
        password='your_password',
        database='TUTORIALS'
    )

# Find customers whose names end with 'sh'
sql_query = "SELECT NAME FROM CUSTOMERS WHERE REGEXP_LIKE(NAME, %s);"
pattern = 'sh$'

try:
    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(sql_query, (pattern,))
            results = cursor.fetchall()
            print(f"Found {cursor.rowcount} customers whose name ends with 'sh':")
            for row in results:
                print(f"- {row[0]}")
except mysql.connector.Error as err:
    print(f"Database Error: {err}")

# 预期输出：
# 找到 2 位姓名以 'sh' 结尾的客户：
# - Ramesh
# - Kaushik

Node.js 示例 (使用 mysql2/promise 和 async/await)

const mysql = require('mysql2/promise');

const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'TUTORIALS'
};

async function findCustomersByPattern(pattern) {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const sql = "SELECT NAME FROM CUSTOMERS WHERE REGEXP_LIKE(NAME, ?);";
        const [rows] = await connection.execute(sql, [pattern]);

        console.log(`Customers with names containing 'al':`);
        if (rows.length > 0) {
            rows.forEach(row => console.log(`- ${row.NAME}`));
        } else {
            console.log('No matching customers found.');
        }
    } catch (error) {
        console.error('Failed to query database:', error);
    } finally {
        if (connection) await connection.end();
    }
}

findCustomersByPattern('al');

// 预期输出：
// 姓名中包含 'al' 的客户：
// - Khilan
// - Chaitali
// - Komal

Java 示例 (使用 JDBC 和 try-with-resources)

import java.sql.*;

public class RegexLikeExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String sql = "SELECT COUNT(*) FROM CUSTOMERS WHERE REGEXP_LIKE(ADDRESS, 'Mumbai|Delhi')";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            if (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("Found " + count + " customers in Mumbai or Delhi.");
            }
        } catch (SQLException e) {
            System.err.println("Database operation failed: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

// 预期输出：
// 在孟买或德里找到 2 位客户。

PHP 示例 (使用 PDO)

PDO (PHP Data Objects) 是用于在 PHP 中访问数据库的现代、面向对象的接口。它支持预处理语句以防止 SQL 注入。

<?php
$host = '127.0.0.1';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
     echo "Database connection established.\n";

     $pattern = '^.{5}$'; // Names that are exactly 5 characters long
     $stmt = $pdo->prepare('SELECT NAME FROM CUSTOMERS WHERE REGEXP_LIKE(NAME, :pattern)');
     $stmt->execute(['pattern' => $pattern]);

     echo "Customers with 5-letter names:\n";
     while ($row = $stmt->fetch()) {
         echo "- " . $row['NAME'] . "\n";
     }

} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 预期输出：
// 数据库连接已建立。
// 姓名有 5 个字母的客户：
// - Komal
// - Muffy