MySQL - regexp_replace() 函数

MySQL：REGEXP_REPLACE() 函数

正则表达式在 MySQL 中提供了一种强大的机制，用于高级搜索和操作。除了过滤记录外，您还可以使用它们在字符串中查找和替换特定模式，这是数据清理和转换中的常见任务。

想象一下，您发现数据库中成千上万条记录存在重复的拼写错误，或者需要标准化数据格式。手动更正每个条目既不切实际又容易出错。REGEXP_REPLACE() 函数是解决此问题的理想工具，它允许您高效、一致地应用复杂的替换逻辑。

理解 REGEXP_REPLACE() 函数

REGEXP_REPLACE() 函数在 MySQL 8.0 中引入，它在字符串中搜索与正则表达式模式匹配的出现项，并将其替换为新字符串。如果找到匹配项，它将返回修改后的字符串；否则，它将返回原始字符串。如果 expression、pattern 或 replacement 参数中的任何一个为 NULL，则该函数返回 NULL。

语法

REGEXP_REPLACE() 函数的一般语法是：

REGEXP_REPLACE(expr, pattern, repl[, pos[, occurrence[, match_type]]])

参数

该函数接受以下参数：

• expr: 要搜索的源字符串。
• pattern: 要搜索的正则表达式模式。
• repl: 将替换匹配子字符串的字符串。

它还接受以下可选参数：

• pos: 在 expr 中开始搜索的位置。默认值为 1。
• occurrence: 指定要替换哪个匹配项。如果为 0（默认值），则替换所有匹配项。如果为正整数 N，则替换第 N 个匹配项。
• match_type: 一个字符串，用于修改匹配行为。常见选项包括 ‘i’ 表示不区分大小写匹配，‘c’ 表示区分大小写匹配（默认），‘m’ 表示多行模式，‘u’ 表示仅 Unix 行尾符。

实际示例

示例 1：基本替换

在此查询中，我们将单词 ‘temporary’ 替换为 ‘permanent’。

SELECT REGEXP_REPLACE('This is a temporary solution.', 'temporary', 'permanent') AS result;

找到模式，并进行替换：

result
This is a permanent solution.

示例 2：未找到匹配项

如果未找到模式，则返回原始字符串，不进行更改。

SELECT REGEXP_REPLACE('This is a final solution.', 'temporary', 'permanent') AS result;

输出：

result
This is a final solution.

示例 3：使用可选参数

此示例演示了不区分大小写匹配 (‘i’)，从位置 5 开始，并且仅替换第一个出现项 (1)。

SELECT REGEXP_REPLACE('A TALL building on a Tall hill.', 'tall', 'short', 5, 1, 'i') AS result;

搜索从 ‘A TA’ 之后开始，找到 ‘Tall’（不区分大小写），并进行替换。

result
A TALL building on a short hill.

示例 4：高级模式匹配

|（或）运算符允许您匹配多个备选项。在这里，我们将 ‘cat’ 和 ‘dog’ 都替换为 ‘pet’。

SELECT REGEXP_REPLACE('I have a cat and a dog.', 'cat|dog', 'pet') AS result;

输出：

result
I have a pet and a pet.

示例 5：使用捕获组进行数据重新格式化

捕获组 () 允许您使用 $n 或 \n 在替换字符串中引用匹配字符串的部分。这对于数据重新格式化非常有用，例如将日期格式从 ‘YYYY-MM-DD’ 更改为 ‘MM/DD/YYYY’。

SELECT REGEXP_REPLACE('Date: 2024-07-26', '([0-9]{4})-([0-9]{2})-([0-9]{2})', '$2/$3/$1') AS formatted_date;

输出：

formatted_date
Date: 07/26/2024

处理表数据

让我们将 REGEXP_REPLACE() 应用到一个表中。首先，创建并填充一个示例 CUSTOMERS 表。

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

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Alia', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

示例 6：标准化列中的电话号码

想象一个 employees 表，其中电话号码存储不一致。我们可以将它们标准化为 (XXX) XXX-XXXX 格式。

-- 首先，添加一个电话号码列和一些示例数据
ALTER TABLE CUSTOMERS ADD COLUMN PHONE VARCHAR(20);
UPDATE CUSTOMERS SET PHONE = '123-456-7890' WHERE ID = 1;
UPDATE CUSTOMERS SET PHONE = '123.456.7891' WHERE ID = 2;
UPDATE CUSTOMERS SET PHONE = '1234567892' WHERE ID = 3;

-- 现在，运行更新查询以标准化格式
UPDATE CUSTOMERS
SET PHONE = REGEXP_REPLACE(PHONE,
    '([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})',
    '($1) $2-$3'
)
WHERE PHONE IS NOT NULL;

运行更新后，您可以验证更改：

SELECT ID, NAME, PHONE FROM CUSTOMERS WHERE PHONE IS NOT NULL;

ID	NAME	PHONE
1	Ramesh	(123) 456-7890
2	Khilan	(123) 456-7891
3	Alia	(123) 456-7892

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

您可以使用任何编程语言执行带有 REGEXP_REPLACE() 的查询。下面是 Python、Node.js、Java 和 PHP 的现代示例，重点关注安全性与资源管理等最佳实践。

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

此示例使用 try...except...finally 块进行健壮的连接处理和参数化查询，以防止 SQL 注入。

import mysql.connector
from mysql.connector import errorcode

# 最佳实践：使用环境变量或配置文件来存储凭据
config = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'TUTORIALS'
}

sql_query = """
SELECT NAME,
       REGEXP_REPLACE(ADDRESS, 'bad$', 'BAD') AS modified_address
  FROM CUSTOMERS
 WHERE ADDRESS LIKE '%bad';
"""

connection = None
try:
    connection = mysql.connector.connect(**config)
    cursor = connection.cursor()
    print("Querying for address modifications...")
    cursor.execute(sql_query)

    for (name, modified_address) in cursor:
        print(f"- {name}'s modified address: {modified_address}")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Authentication error: check username/password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    if connection and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed.")

# 预期输出：
# 查询地址修改...
# - Ramesh 修改后的地址：AhmedaBAD
# - Komal 修改后的地址：HyderaBAD
# MySQL 连接已关闭。

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

现代 JavaScript 使用 async/await 来编写更清晰的异步代码。mysql2/promise 库开箱即用地支持这种语法。

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

// 最佳实践：使用环境变量进行配置
const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'TUTORIALS'
};

async function main() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        console.log('Successfully connected to the database.');

        const sql = "SELECT REGEXP_REPLACE('Humpty dumpty sat on a wall', 'wall', 'fence') AS result;";
        const [rows, fields] = await connection.execute(sql);

        console.log('Query Result:');
        console.log(rows[0].result);

    } catch (error) {
        console.error('Error executing query:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

main();

// 预期输出：
// 成功连接到数据库。
// 查询结果：
// Humpty dumpty sat on a fence.
// 连接已关闭。

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

try-with-resources 语句会自动关闭 Connection、Statement 和 ResultSet 等资源，防止资源泄漏。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class RegexReplaceExample {
    // 使用常量定义连接详情
    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 REGEXP_REPLACE('This is a test.', 'test', 'demonstration') AS result;";

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

            System.out.println("Successfully connected and executed query.");
            if (rs.next()) {
                String result = rs.getString("result");
                System.out.println("Result: " + result);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

// 预期输出：
// 成功连接并执行查询。
// 结果：这是一个演示。

PHP 示例 (使用 MySQLi 和预处理语句)

此示例使用 MySQLi 的面向对象风格和预处理语句，这是在 PHP 中与数据库交互的现代、安全的方式。

<?php
// 数据库凭据应安全存储，而非硬编码
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'your_password';
$dbname = 'TUTORIALS';

// 启用 mysqli 错误报告
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
    echo "Connected successfully.\n";

    // 即使是查询且没有用户输入，使用预处理语句也是一个好习惯。
    $sql = "SELECT REGEXP_REPLACE(?, ?, ?) AS result";
    $stmt = $mysqli->prepare($sql);

    $original_string = 'eat sleep repeat';
    $pattern = 'eat';
    $replacement = 'code';

    $stmt->bind_param('sss', $original_string, $pattern, $replacement);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();

    echo "Result: " . $row['result'] . "\n";

    $stmt->close();
    $mysqli->close();

} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

// 预期输出：
// 连接成功。
// 结果：code sleep repeat