MySQL - regexp_substr() 函数

MySQL REGEXP_SUBSTR() 函数

MySQL 正则表达式简介

正则表达式（regex）是用于高级字符串模式匹配的强大工具。虽然简单的 LIKE 子句对于基本的通配符搜索很有用，但正则表达式允许您定义复杂的搜索模式。在 MySQL 8.0 及更高版本中，引入了一套健壮的、兼容 ICU 的正则表达式函数，包括 REGEXP_SUBSTR()、REGEXP_LIKE()、REGEXP_REPLACE() 和 REGEXP_INSTR()。

REGEXP_SUBSTR() 专门用于提取与给定正则表达式模式匹配的子字符串。这对于从数据库中存储的非结构化或半结构化文本数据中解析和提取特定信息非常有用。

REGEXP_SUBSTR() 函数

REGEXP_SUBSTR() 函数在字符串中搜索与正则表达式模式匹配的子字符串并返回该子字符串。如果没有找到匹配项，它将返回 NULL。这与 REGEXP_LIKE() 不同，后者只返回 1（真）或 0（假）。

语法

REGEXP_SUBSTR(expression, pattern[, position[, occurrence[, match_type]]])

参数

此函数接受以下参数：

• expression: 要搜索的输入字符串。
• pattern: 要匹配的正则表达式模式。
• position（可选）：字符串内搜索的起始位置。默认为 1。
• occurrence（可选）：指定要查找的模式的第几次出现。默认为 1（第一次出现）。
• match_type（可选）：一个字符串，指定匹配选项。例如：‘c’ 表示区分大小写匹配，‘i’ 表示不区分大小写，‘m’ 表示多行模式。默认为区分大小写。

基础与进阶示例

示例 1：简单模式匹配

让我们查找句子中的第一个单词。模式 \w+ 匹配一个或多个单词字符（字母、数字、下划线）。

SELECT REGEXP_SUBSTR('Modern SQL is powerful!', '\\w+') AS first_word;

注意：在 SQL 字符串中，反斜杠 \ 是一个转义字符，因此您通常需要将其加倍 \\ 以在正则表达式模式中表示一个字面反斜杠。

first_word
Modern

示例 2：未找到匹配项

如果模式在字符串中不存在，函数将返回 NULL。

SELECT REGEXP_SUBSTR('Modern SQL is powerful!', 'MySQL') AS result;

result
NULL

示例 3：使用起始位置和出现次数

让我们查找同一句子中的第二个单词（occurrence = 2）。

SELECT REGEXP_SUBSTR('Modern SQL is powerful!', '\\w+', 1, 2) AS second_word;

second_word
SQL

示例 4：实际用例 - 提取域名

一个常见的任务是从电子邮件地址中提取域名。模式 '@(.*)$' 捕获 @ 符号之后的所有内容。

SELECT REGEXP_SUBSTR('developer@example.co.uk', '@(.*)$') AS domain_part;

这会返回 @example.co.uk。要获取不带 @ 的域名，我们可以使用 REGEXP_REPLACE 或将 REGEXP_SUBSTR 与 SUBSTRING 结合使用。

SELECT SUBSTRING(REGEXP_SUBSTR('developer@example.co.uk', '@(.*)$'), 2) AS domain_name;

domain_name
example.co.uk

在表数据上的使用

让我们创建一个 products 表，其中包含版本字符串并提取主版本号。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    version_code VARCHAR(50)
);

INSERT INTO products (product_name, version_code) VALUES
    ('Data Analyzer', 'v2.1.5-beta'),
    ('Image Editor', 'v3.0.1-stable'),
    ('Text Processor', 'Legacy v1.9'),
    ('Firewall', '4.0');

我们希望提取第一串数字，它代表主版本号。模式 [0-9]+ 或 \\d+ 匹配一个或多个数字。

SELECT
    product_name,
    version_code,
    REGEXP_SUBSTR(version_code, '\\d+') AS major_version
FROM products;

product_name	version_code	major_version
Data Analyzer	v2.1.5-beta	2
Image Editor	v3.0.1-stable	3
Text Processor	Legacy v1.9	1
Firewall	4.0	4

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

从客户端应用程序调用 REGEXP_SUBSTR() 非常简单。请记住正确处理可能返回的 NULL 值。

PHP (PDO)
Node.js (mysql2/promise)
Java (JDBC)
Python (mysql-connector-python)




```php
<?php
// 假设 $pdo 是前一个教程中已连接的 PDO 对象

$sql = "SELECT product_name, version_code, REGEXP_SUBSTR(version_code, '\\d+') AS major_version FROM products";

$stmt = $pdo->query($sql);

echo "Product Major Versions:\n";
while ($row = $stmt->fetch()) {
    $majorVersion = $row['major_version'] ?? 'N/A'; // 处理 NULL 值
    echo "- {$row['product_name']} ({$row['version_code']}): Major version is {$majorVersion}\n";
}
?>

Output: Product Major Versions: - Data Analyzer (v2.1.5-beta): Major version is 2 - Image Editor (v3.0.1-stable): Major version is 3 ...

main.js

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

async function getMajorVersions() {
    let connection;
    try {
        connection = await mysql.createConnection({ /* connection config */ });

        const sql = "SELECT product_name, version_code, REGEXP_SUBSTR(version_code, '\\d+') AS major_version FROM products";
        const [rows] = await connection.execute(sql);

        console.log('Product Major Versions:');
        for (const row of rows) {
            const majorVersion = row.major_version || 'N/A'; // 处理 null 值
            console.log(`- ${row.product_name} (${row.version_code}): Major version is ${majorVersion}`);
        }
    } catch (error) {
        console.error('Error fetching product versions:', error);
    } finally {
        if (connection) await connection.end();
    }
}

getMajorVersions();

Output: Product Major Versions: - Data Analyzer (v2.1.5-beta): Major version is 2 - Image Editor (v3.0.1-stable): Major version is 3 ...

ProductVersionExtractor.java

import java.sql.*;

public class ProductVersionExtractor {
    // 假设 DB_URL, USER, PASS 已定义
    public static void main(String[] args) {
        String sql = "SELECT product_name, version_code, REGEXP_SUBSTR(version_code, '\\d+') AS major_version FROM products";

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

            System.out.println("Product Major Versions:");
            while (rs.next()) {
                String productName = rs.getString("product_name");
                String versionCode = rs.getString("version_code");
                String majorVersion = rs.getString("major_version");
                if (rs.wasNull()) {
                    majorVersion = "N/A"; // 处理 NULL 值
                }
                System.out.printf("- %s (%s): Major version is %s%n", productName, versionCode, majorVersion);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output: Product Major Versions: - Data Analyzer (v2.1.5-beta): Major version is 2 - Image Editor (v3.0.1-stable): Major version is 3 ...

version_extractor.py

import mysql.connector

def get_major_versions():
    try:
        conn = mysql.connector.connect(/* connection config */)
        cursor = conn.cursor(dictionary=True) # 将行作为字典获取

        sql = "SELECT product_name, version_code, REGEXP_SUBSTR(version_code, '\\d+') AS major_version FROM products"
        cursor.execute(sql)

        print('Product Major Versions:')
        for row in cursor.fetchall():
            major_version = row.get('major_version') or 'N/A'; // 处理 None 值
            print(f"- {row['product_name']} ({row['version_code']}): Major version is {major_version}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    get_major_versions()

Output: Product Major Versions: - Data Analyzer (v2.1.5-beta): Major version is 2 - Image Editor (v3.0.1-stable): Major version is 3 ...

## 最佳实践

- **性能：** 正则表达式操作可能是 CPU 密集型的。如果可能，请避免在大型、未索引的列的 `WHERE` 子句中使用它们。如果您经常按模式搜索，请考虑在数据入库时将相关数据提取到单独的、已建立索引的列中。
- **复杂性：** 保持模式尽可能简单。复杂的正则表达式可能难以阅读、调试和维护。
- **转义：** 请记住在您的 SQL 字符串字面量中转义反斜杠（`\`）（例如，`\d` 变为 `\\d`）。
- **数据规范化：** 尽管正则表达式在解析现有数据方面很强大，但最佳的长期解决方案通常是规范化您的数据模式（schema）。例如，将主版本、次版本和补丁版本存储在单独的 `INT` 列中比每次都解析版本字符串效率高得多。