MySQL - regexp_instr() 函数

MySQL - REGEXP_INSTR() 函数

在现代数据驱动的应用中，在数据库内执行复杂的文本搜索至关重要。虽然使用 LIKE 进行简单的模式匹配很有用，但 MySQL 的正则表达式 (regular expression) 功能提供了更强大、更灵活的方式来查找和验证数据。正则表达式允许您定义复杂的搜索模式，以定位或操作文本字符串。

MySQL 提供了一套用于处理正则表达式的函数。自 MySQL 8.0 版本起，这些函数已标准化并基于强大的 ICU (International Components for Unicode，统一码国际组件) 库。REGEXP_INSTR() 函数是这套函数中的一个基本工具，旨在查找与正则表达式模式匹配的子字符串的起始位置。

理解 REGEXP_INSTR() 函数

REGEXP_INSTR() 函数在一个字符串中搜索正则表达式模式，并返回匹配项的起始索引。如果未找到模式，则返回 0。如果任何核心参数（expr 或 pattern）为 NULL，则函数返回 NULL。字符索引是基于 1 的，这意味着第一个字符位于位置 1。

语法

REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]])

核心参数

• expr：要搜索的输入字符串。
• pattern：要搜索的正则表达式模式。

可选参数

• pos：开始搜索 expr 的位置。默认值为 1。
• occurrence：要查找的特定匹配项的出现次数。例如，2 搜索第二次匹配。默认值为 1。
• return_option：确定要返回的位置。0（默认）返回匹配子字符串的起始位置。1 返回匹配子字符串 之后 字符的位置。
• match_type：一个修改匹配行为的字符字符串：
• c：区分大小写的匹配（默认）。
• i：不区分大小写的匹配。
• m：多行模式。^ 和 $ 匹配字符串内的行首和行尾，而不仅仅是整个字符串的开头和结尾。
• n：允许点 (.) 元字符匹配行终止符。
• u：仅限 Unix 行尾。只有换行符被 ., ^ 和 $ 识别为行尾。

实际示例

基本搜索

查找字符串中单词 ‘Modern’ 的起始位置。默认情况下，搜索是区分大小写的。

SELECT REGEXP_INSTR('A Modern MySQL Tutorial', 'Modern') AS Result;

模式 ‘Modern’ 从索引 3 开始找到。

Result
3

不区分大小写的搜索

要查找不区分大小写的 ‘modern’，请使用 i 匹配类型。

SELECT REGEXP_INSTR('A Modern MySQL Tutorial', 'modern', 1, 1, 0, 'i') AS Result;

这也会在位置 3 找到匹配项。

Result
3

查找第 N 次出现

让我们查找字母 ‘o’ 的 第二次 出现的起始位置。

SELECT REGEXP_INSTR('A Modern MySQL Tutorial', 'o', 1, 2) AS Result;

第一个 ‘o’ 在 ‘Modern’ 中（位置 5）。第二个在 ‘Tutorial’ 中（位置 18）。

Result
18

使用高级模式

真正的强大之处来自于复杂的模式。此查询查找以 ‘T’ 开头并以 ‘l’ 结尾的第一个单词的位置。

SELECT REGEXP_INSTR('A Modern MySQL Tutorial', '\\bT[a-z]+l\\b') AS Result;

注意：在 SQL 字符串中，反斜杠 \ 是一个转义字符，因此您必须对其进行转义 (\\) 才能将字面反斜杠传递给正则表达式引擎。\b 是一个单词边界。

Result
16

处理无匹配和 NULL 值

如果未找到模式，函数返回 0。如果参数为 NULL，则返回 NULL。

SELECT
  REGEXP_INSTR('A Modern MySQL Tutorial', 'Legacy') AS NoMatch,
  REGEXP_INSTR('A Modern MySQL Tutorial', NULL) AS NullPattern;

NoMatch	NullPattern
0	NULL

实际应用：数据验证

假设一个 USERS 表，您需要查找电子邮件地址以非标准格式存储的用户。我们希望查找 EMAIL 列看起来 不像 有效电子邮件地址的记录。

-- Sample Table Setup
CREATE TABLE USERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    EMAIL VARCHAR(100)
);

INSERT INTO USERS(NAME, EMAIL) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob-at-example.org'),
('Charlie', 'charlie@example.net'),
('David', 'david@invalid');

-- Find users with emails that DON'T match a standard email pattern
-- We use REGEXP_LIKE here, which is often paired with REGEXP_INSTR
SELECT NAME, EMAIL
FROM USERS
WHERE NOT REGEXP_LIKE(EMAIL, '^[\\w.-]+@[\\w.-]+\\.([a-zA-Z]{2,})$');

此查询将把 Bob 和 David 识别为可能具有无效电子邮件格式的用户。REGEXP_INSTR 随后可用于查找无效字符的确切位置（如果需要）。

客户端程序集成（现代实践）

从应用程序执行 SQL 需要安全、现代的实践。始终使用参数化查询（预处理语句）来防止 SQL 注入 (SQL injection)，即使输入看起来很安全。以下是流行语言的更新示例。

语法概览

PHP
NodeJS
Java
Python



现代 PHP 应用程序应使用 PDO (PHP Data Objects) 扩展进行数据库交互，因为它在不同数据库驱动程序之间具有一致性，并对预处理语句提供了强大的支持。

$stmt = $pdo->prepare("SELECT REGEXP_INSTR(?, ?, 1, 1, 0, 'i') AS Result");
$stmt->execute(['A Modern MySQL Tutorial', 'modern']);
$result = $stmt->fetch(PDO::FETCH_ASSOC);



在 Node.js 中，`mysql2` 等库是标准配置。使用其基于 Promise 的 API 和 `async/await` 语法可以编写更清晰、更易读、非阻塞的代码。

const mysql = require('mysql2/promise');
const [rows] = await connection.execute(
  "SELECT REGEXP_INSTR(?, ?, 1, 1, 0, 'i') AS Result",
  ['A Modern MySQL Tutorial', 'modern']
);



现代 Java 应用程序使用 try-with-resources 确保数据库资源（如连接和语句）自动关闭，防止资源泄漏。使用 `PreparedStatement` 以确保安全性。

String sql = "SELECT REGEXP_INSTR(?, ?, 1, 1, 0, 'i') AS Result";
try (Connection conn = ...; PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, "A Modern MySQL Tutorial");
    pstmt.setString(2, "modern");
    ResultSet rs = pstmt.executeQuery();
    // ... process results
}



在 Python 中，`mysql-connector-python` 库是一个常用选择。使用 `with` 语句来管理连接和游标可确保它们正确关闭。查询必须是参数化的。

query = "SELECT REGEXP_INSTR(%s, %s, 1, 1, 0, 'i') AS Result"
params = ('A Modern MySQL Tutorial', 'modern')
with connection.cursor() as cursor:
    cursor.execute(query, params)
    result = cursor.fetchone()

完整示例：Python

Python




import mysql.connector
from mysql.connector import errorcode

# --- Configuration ---
config = {
    'user': 'root',
    'password': 'password',
    'host': '127.0.0.1',
    'database': 'TUTORIALS',
}

search_string = 'A Modern MySQL Tutorial'
pattern = 'tutorial'

# --- Database Interaction ---
try:
    # Using a `with` statement ensures the connection is closed
    with mysql.connector.connect(**config) as connection:
        print("Connection established.")
        with connection.cursor(dictionary=True) as cursor:
            # Parameterized query to prevent SQL injection
            query = "SELECT REGEXP_INSTR(%s, %s, 1, 1, 0, 'i') AS Result"

            cursor.execute(query, (search_string, pattern))

            result = cursor.fetchone()

            if result:
                print(f"Search string: '{search_string}'")
                print(f"Pattern: '{pattern}' (case-insensitive)")
                print(f"Pattern found at position: {result['Result']}")
            else:
                print("No result found.")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    print("Execution finished.")


Output
Following is the expected output:

Connection established.
Search string: 'A Modern MySQL Tutorial'
Pattern: 'tutorial' (case-insensitive)
Pattern found at position: 16
Execution finished.