怎样使用 MySQL 正则表达式

介绍

MySQL 提供了一种强大的方式来通过正则表达式功能执行复杂的基于文本的搜索 正则表达式. 这种字符串匹配算法表示通过一系列符号和字符进行搜索以在文本中查找模式。

您可以使用正则表达式来匹配、定位和管理 MySQL 数据库中基于文本的记录,在大型数据库中进行复杂搜索时可以节省大量时间。

除了搜索之外,您还可以使用 REGEXP 与其他 MySQL 函数的组合来替换和重新排列数据库表中的字段。 您还可以从长文本中提取子字符串,将字符串拆分为人类可读的标记,并验证/验证字符串的结构以检查它是否符合您的设置格式。

本指南使用示例说明了 MySQL REGEXP 函数的强大功能。 完成后,您将能够在任何 MySQL 数据库中实现不同的正则表达式组合,以达到您想要的结果。

先决条件

要完成本教程,您需要具备以下条件:

一个 Ubuntu 20.04 服务器。 一个非 root 用户 sudo 特权。 一个 MySQL 服务器。

1. 创建测试数据库

通过 SSH 连接到您的服务器并以 root 身份登录到 MySQL。

$ sudo mysql -u root -p

输入您的 MySQL 根密码并按 ENTER 继续。 然后,一旦你得到 mysql> 在提示符下,键入以下命令以创建测试数据库。

mysql> CREATE DATABASE regex_db;

输出。

Query OK, 1 row affected (0.01 sec)

切换到数据库。

mysql> USE regex_db;

输出。

Database changed

接下来,创建一个 customers 桌子。

mysql> CREATE table customers (
         customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         first_name VARCHAR(50),
         last_name  VARCHAR(50),
         email      VARCHAR(255)
       ) ENGINE = InnoDB;

输出。

Query OK, 0 rows affected (0.04 sec)

插入一些记录到 customers 桌子。

mysql> INSERT INTO customers (first_name, last_name, email) VALUES ('JANE', 'SMITH', '[email protected]');
       INSERT INTO customers (first_name, last_name, email) VALUES ('MARY', 'ROE', '-');
       INSERT INTO customers (first_name, last_name, email) VALUES ('JOHN', 'DOE', '[email protected]');
       INSERT INTO customers (first_name, last_name, email) VALUES ('MARY', 'FREDRICK', '');
       INSERT INTO customers (first_name, last_name, email) VALUES ('BANNIE', 'ISAAC', '[email protected]');

输出。

...
Query OK, 1 row affected (0.01 sec)

执行 SELECT 以下声明以验证记录。

mysql> SELECT
       customer_id,
       first_name,
       last_name,
       email
       FROM customers;

您应该会看到以下客户列表。

+-------------+------------+-----------+--------------------------+
| customer_id | first_name | last_name | email                    |
+-------------+------------+-----------+--------------------------+
|           1 | JANE       | SMITH     | [email protected]         |
|           2 | MARY       | ROE       | -                        |
|           3 | JOHN       | DOE       | [email protected]     |
|           4 | MARY       | FREDRICK  |                          |
|           5 | BANNIE     | ISAAC     | [email protected] |
+-------------+------------+-----------+--------------------------+
5 rows in set (0.00 sec)

示例数据库和表就位后,您现在将测试 MySQL 支持的不同正则表达式。

2. 匹配字符串的开头

要匹配以特定模式开头的记录,请使用 Caret 字符 ^. 例如,要匹配所有 first_name's 以字母开头 J,执行下面的命令。

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE first_name REGEXP '^J';

您应该会看到与您的模式匹配的客户列表,如下所示。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           1 | JANE       | SMITH     |
|           3 | JOHN       | DOE       |
+-------------+------------+-----------+
2 rows in set (0.00 sec)

虽然这是出于演示目的,但您可以在实际场景中使用上述示例。 例如,如果您在制造业中,您可以使用这种方法从数据库中选择所有以特定字符开头的批号以召回有缺陷的产品。

在下一步中,您将看到如何匹配字符串的结尾。

3. 匹配字符串的结尾

使用美元 $ 字符以匹配字符串的最后一个字符之后的位置。 例如,要匹配所有 last_name's 以字母结尾 OE,运行下面的语句。

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE last_name REGEXP 'OE$';

仅有的 MARY ROE'sJOHN DOE's 名字以字母结尾 OE 它们应如下列出。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           2 | MARY       | ROE       |
|           3 | JOHN       | DOE       |
+-------------+------------+-----------+
2 rows in set (0.00 sec)

例如,如果客户的地址信息存储在单个列中,您可以使用此方法从特定邮政编码中检索所有客户的记录。

要对此进行测试,请添加 addresszip 列到 customers 桌子。

mysql> ALTER TABLE customers
       ADD address VARCHAR(255)
       AFTER email ,
       ADD zip VARCHAR(20)
       AFTER address;

输出。

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

接下来,更新客户的地址信息。

mysql> UPDATE customers SET address="123 FIRST RD. TOWN 1 AZ 8659" WHERE customer_id = '1';
       UPDATE customers SET address="456 2ND RD. TOWN 2 AZ 12345" WHERE customer_id = '2';
       UPDATE customers SET address="789 3RD RD. TOWN 3 AZ 8659" WHERE customer_id = '3';
       UPDATE customers SET address="555 FIRST RD. TOWN 1 AZ 8659" WHERE customer_id = '4';
       UPDATE customers SET address="987 FIFTH RD TOWN 2 1414" WHERE customer_id = '5';

输出。

...
Rows matched: 1  Changed: 1  Warnings: 0

确保表已更新。

mysql> SELECT * FROM customers;

输出

+-------------+------------+-----------+--------------------------+------------------------------+------+
| customer_id | first_name | last_name | email                    | address                      | zip  |
+-------------+------------+-----------+--------------------------+------------------------------+------+
|           1 | JANE       | SMITH     | [email protected]         | 123 FIRST RD. TOWN 1 AZ 8659 | NULL |
|           2 | MARY       | ROE       | -                        | 456 2ND RD. TOWN 2 AZ 12345  | NULL |
|           3 | JOHN       | DOE       | [email protected]     | 789 3RD RD. TOWN 3 AZ 8659   | NULL |
|           4 | MARY       | FREDRICK  |                          | 555 FIRST RD. TOWN 1 AZ 8659 | NULL |
|           5 | BANNIE     | ISAAC     | [email protected] | 987 FIFTH RD TOWN 2 1414     | NULL |
+-------------+------------+-----------+--------------------------+------------------------------+------+
5 rows in set (0.00 sec)

如您所见,地址详细信息(包括街道、城镇、州和邮编)保存在一列中。 例如,从邮政编码中获取所有客户 8659,执行下面的命令。

mysql> SELECT
       customer_id,
       first_name,
       last_name,
       address,
       zip
       FROM customers
       WHERE address REGEXP '8659$';

输出。

+-------------+------------+-----------+------------------------------+------+
| customer_id | first_name | last_name | address                      | zip  |
+-------------+------------+-----------+------------------------------+------+
|           1 | JANE       | SMITH     | 123 FIRST RD. TOWN 1 AZ 8659 | NULL |
|           3 | JOHN       | DOE       | 789 3RD RD. TOWN 3 AZ 8659   | NULL |
|           4 | MARY       | FREDRICK  | 555 FIRST RD. TOWN 1 AZ 8659 | NULL |
+-------------+------------+-----------+------------------------------+------+
3 rows in set (0.00 sec)

你也可以使用 MySQL REGEXP 结果是 UPDATE 语句来编辑记录。 例如,在这种情况下,您现在可以更新客户的 zip 使用下面的语句将代码列设置为正确的值。

mysql> UPDATE customers
       SET zip = '8659'
       WHERE address REGEXP '8659$';

输出。

Rows matched: 3  Changed: 3  Warnings: 0

确认新的更改。

mysql> SELECT
       *
       FROM customers;

输出

+-------------+------------+-----------+--------------------------+------------------------------+------+
| customer_id | first_name | last_name | email                    | address                      | zip  |
+-------------+------------+-----------+--------------------------+------------------------------+------+
|           1 | JANE       | SMITH     | [email protected]         | 123 FIRST RD. TOWN 1 AZ 8659 | 8659 |
|           2 | MARY       | ROE       | -                        | 456 2ND RD. TOWN 2 AZ 12345  | NULL |
|           3 | JOHN       | DOE       | [email protected]     | 789 3RD RD. TOWN 3 AZ 8659   | 8659 |
|           4 | MARY       | FREDRICK  |                          | 555 FIRST RD. TOWN 1 AZ 8659 | 8659 |
|           5 | BANNIE     | ISAAC     | [email protected] | 987 FIFTH RD TOWN 2 1414     | NULL |
+-------------+------------+-----------+--------------------------+------------------------------+------+
5 rows in set (0.00 sec)

如您所见,MySQL REGEXP 在查找和重新排列基于文本的列值时,函数是必不可少的。 在下一步中,您将搜索与方括号括起来的字符匹配的值。

4. 匹配方括号之间的字符

您可以使用正则表达式匹配 MySQL 中方括号之间列出的任何字符。 例如,要列出所有 first_name's 包含字符 H 或者 Y,使用下面的语法。

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE first_name REGEXP '[HY]';

输出。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           2 | MARY       | ROE       |
|           3 | JOHN       | DOE       |
|           4 | MARY       | FREDRICK  |
+-------------+------------+-----------+
3 rows in set (0.00 sec)

同样,使用下面的命令来定位所有 last_name's 包含字符 R 或者 D 其次是 OE.

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE last_name REGEXP '[RD]OE';

输出。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           2 | MARY       | ROE       |
|           3 | JOHN       | DOE       |
+-------------+------------+-----------+
2 rows in set (0.00 sec)

当您不确定要在数据库中查找的确切名称时,上面的两个搜索很有帮助,但您至少碰巧知道它包含的某些字符。 接下来,您将搜索一系列字符。

5. 匹配一系列字符

您可以使用一系列字符之间的破折号来定位字符串 REGEXP. 例如,要检索所有 first_name's 包含字母 a-d,使用下面的语法。

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE first_name REGEXP '[a-d]';

输出。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           1 | JANE       | SMITH     |
|           2 | MARY       | ROE       |
|           4 | MARY       | FREDRICK  |
|           5 | BANNIE     | ISAAC     |
+-------------+------------+-----------+
4 rows in set (0.00 sec)

匹配任何 zip 包含数字的代码 0-9, 运行下面的 SQL 命令。

mysql> SELECT
       customer_id,
       first_name,
       last_name,
       zip
       FROM customers
       WHERE zip REGEXP '[0-9]';

输出。

+-------------+------------+-----------+------+
| customer_id | first_name | last_name | zip  |
+-------------+------------+-----------+------+
|           1 | JANE       | SMITH     | 8659 |
|           3 | JOHN       | DOE       | 8659 |
|           4 | MARY       | FREDRICK  | 8659 |
+-------------+------------+-----------+------+
3 rows in set (0.00 sec)

在生产环境中,您可以使用上述方法来验证记录。 例如,如果您想向客户发送短信,您可以过滤仅包含数字电话号码的记录。 接下来,您将匹配子字符串。

6. 匹配子串

您可以使用正则表达式匹配 MySQL 中任何位置的子字符串。 例如,查找包含子字符串的所有名称 AN 通过运行下面的查询以该顺序。

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE first_name REGEXP 'AN';

输出。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           1 | JANE       | SMITH     |
|           5 | BANNIE     | ISAAC     |
+-------------+------------+-----------+
2 rows in set (0.00 sec)

接下来,使用以下语法在特定位置查找子字符串。

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE first_name REGEXP '^..NE';

输出。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           1 | JANE       | SMITH     |
+-------------+------------+-----------+
1 row in set (0.00 sec)

在大多数情况下,您将使用 substring 来定位和替换 MySQL 中错误输入的数据。 接下来,您将看到如何使用字符类。

7. 匹配 POSIX 字符类

MySQL REGEXP 子句支持按字符类搜索。 这意味着您可以搜索和返回包含字母或数字字符甚至两者的记录。

例如,使用下面的语法在 first_name 场地。

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE first_name REGEXP '[:alnum:]';

由于所有 first_name's 包含字母数字字符,它们应如下列出。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           1 | JANE       | SMITH     |
|           2 | MARY       | ROE       |
|           3 | JOHN       | DOE       |
|           4 | MARY       | FREDRICK  |
|           5 | BANNIE     | ISAAC     |
+-------------+------------+-----------+
5 rows in set (0.01 sec)

要仅搜索字母字符,请使用以下语法。

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers
       WHERE first_name REGEXP '[:alpha:]' ;

输出。

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           1 | JANE       | SMITH     |
|           2 | MARY       | ROE       |
|           3 | JOHN       | DOE       |
|           4 | MARY       | FREDRICK  |
|           5 | BANNIE     | ISAAC     |
+-------------+------------+-----------+
5 rows in set (0.00 sec)

仅搜索数字,例如,定位 zip 带有数字字符的代码,请运行下面的 SQL 命令。

mysql> SELECT
       customer_id,
       first_name,
       last_name,
       zip
       FROM customers
       WHERE zip REGEXP '[:digit:]';

输出。

+-------------+------------+-----------+------+
| customer_id | first_name | last_name | zip  |
+-------------+------------+-----------+------+
|           1 | JANE       | SMITH     | 8659 |
|           3 | JOHN       | DOE       | 8659 |
|           4 | MARY       | FREDRICK  | 8659 |
+-------------+------------+-----------+------+
3 rows in set (0.00 sec)

尽管这不是所有 MySQL 正则表达式的最终列表,但它应该可以帮助您掌握基本概念。

结论

在本指南中,您已经创建了一个示例数据库和一个表。 您还使用不同的组合测试了 MySQL REGEXP 函数。 当您想在下一个 MySQL 项目中执行复杂搜索时,请使用本指南中的知识。

注:本教程在Vultr VPS上测试通过,如需部署请前往Vultr.com