MySQL - NOT REGEXP 运算符

MySQL - NOT REGEXP 运算符

MySQL 中正则表达式简介

正则表达式（regex）是指定搜索模式的字符序列。它是一种强大的高级文本匹配工具，比 LIKE 运算符的简单通配符（% 和 _）灵活得多。

MySQL 中的 NOT REGEXP 运算符（及其别名 NOT RLIKE）用于过滤结果，只返回不匹配指定正则表达式模式的行。

NOT REGEXP 语法

SELECT column_list
FROM table_name
WHERE string_column NOT REGEXP 'pattern';

您经常会用到的一些基本正则表达式元字符：

• ^：将模式锚定到字符串的开头。
• $：将模式锚定到字符串的末尾。
• .：匹配任何单个字符。
• [...]：匹配方括号内的任何字符（例如，[aeiou]）。
• |：作为 OR 条件（例如，a|b 匹配 ‘a’ 或 ‘b’）。

NOT REGEXP 与 NOT LIKE

选择合适的工具：

• 对于简单的通配符搜索，请使用 NOT LIKE。它对于基本模式更快。示例：WHERE name NOT LIKE 'A%'（名称不以 ‘A’ 开头）。
• 对于 LIKE 无法处理的复杂模式匹配，请使用 NOT REGEXP。示例：WHERE name NOT REGEXP '^A|^B'（名称不以 ‘A’ 或 ‘B’ 开头）。

实际示例

首先，我们为示例创建并填充一个 users 表。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

INSERT INTO users (username, email)
VALUES
    ('alex', 'alex@example.com'),
    ('ben_carter', 'ben.carter@work.net'),
    ('charlie', 'charlie99@example.com'),
    ('david_lee', 'david.lee@web.org'),
    ('eva', 'eva@example.com');

我们的 users 表现在包含：

ID	用户名	邮箱
1	alex	alex@example.com
2	ben_carter	ben.carter@work.net
3	charlie	charlie99@example.com
4	david_lee	david.lee@web.org
5	eva	eva@example.com

示例 1：查找不包含数字的用户名

SELECT username FROM users
WHERE username NOT REGEXP '[0-9]';

用户名
alex
ben_carter
david_lee
eva

示例 2：查找不来自 ‘example.com’ 的邮箱

SELECT email FROM users
WHERE email NOT REGEXP 'example\.com$';

注意：点 . 是一个特殊的正则表达式字符，因此我们用反斜杠 \ 对其进行转义，以匹配字面意义上的点。

邮箱
ben.carter@work.net
david.lee@web.org

示例 3：查找不包含下划线或点的用户名

SELECT username FROM users
WHERE username NOT REGEXP '_|\.';

用户名
alex
charlie
eva

MySQL 8+ 中的现代正则表达式函数

从 MySQL 8.0 开始，引入了一组基于 ICU 的更强大的新正则表达式函数。它们提供了更好的 Unicode 支持和性能。虽然 NOT REGEXP 仍然完全有效，但对于 MySQL 8+ 上的新项目，您应该考虑使用这些函数。

-- 旧方式
SELECT username FROM users WHERE username NOT REGEXP '[0-9]';

-- 使用 REGEXP_LIKE() 的现代方式
SELECT username FROM users WHERE NOT REGEXP_LIKE(username, '[0-9]');

其他现代函数包括 REGEXP_INSTR()、REGEXP_REPLACE() 和 REGEXP_SUBSTR()。

在客户端程序中使用 NOT REGEXP

以下是使用现代安全实践，从各种编程语言执行 NOT REGEXP 查询的方法。

Python
Node.js
PHP
Java




# 使用带预处理语句的 mysql-connector-python

import mysql.connector

def find_users_without_numbers():
    config = {
        'user': 'root',
        'password': 'password',
        'host': 'localhost',
        'database': 'TUTORIALS' # 假设 'users' 表在这里
    }
    query = "SELECT id, username FROM users WHERE username NOT REGEXP %s"
    pattern = '[0-9]'

    try:
        with mysql.connector.connect(**config) as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute(query, (pattern,))
                results = cursor.fetchall()
                print("用户名中不包含数字的用户：")
                for row in results:
                    print(row)
    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")

find_users_without_numbers()




// 使用 mysql2/promise 和 async/await

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

async function findUsersWithoutNumbers() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS' // 假设 'users' 表在这里
        });

        const query = "SELECT id, username FROM users WHERE username NOT REGEXP ?";
        const pattern = '[0-9]';

        const [rows] = await connection.execute(query, [pattern]);
        console.log("用户名中不包含数字的用户：");
        console.log(rows);

    } catch (error) {
        console.error('执行查询时出错:', error);
    } finally {
        if (connection) await connection.end();
    }
}

findUsersWithoutNumbers();




// 使用 PDO 进行安全查询

<?php
$dsn = "mysql:host=localhost;dbname=TUTORIALS;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

try {
    $pdo = new PDO($dsn, 'root', 'password', $options);

    $query = "SELECT id, username FROM users WHERE username NOT REGEXP ?";
    $pattern = '[0-9]';

    $stmt = $pdo->prepare($query);
    $stmt->execute([$pattern]);

    $results = $stmt->fetchAll();

    echo "用户名中不包含数字的用户：\n";
    print_r($results);

} catch (PDOException $e) {
    die("数据库连接错误: " . $e->getMessage());
}




// 使用 JDBC 和 try-with-resources 及 PreparedStatement

import java.sql.*;

public class NotRegexpExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void findUsersWithoutNumbers() {
        String sql = "SELECT id, username FROM users WHERE username NOT REGEXP ?";
        String pattern = "[0-9]";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, pattern);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("用户名中不包含数字的用户：");
                while (rs.next()) {
                    System.out.printf("ID: %d, Username: %s%n",
                            rs.getInt("id"),
                            rs.getString("username"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        findUsersWithoutNumbers();
    }
}