在 MariaDB 中查询仅包含字母与数字的行的 2 种方法

介绍在 MariaDB 中查询仅包含字母数字(Alphanumeric)字符记录的两种方法:使用 POSIX 字符类 [:alnum:] 与使用自定义字符范围 [A-Za-z0-9],并对比二者对重音字母和国际化字符集的支持差异。

发布于

在数据清洗与校验过程中,经常需要筛选出仅包含“字母和数字”(Alphanumeric)字符的记录。在 MariaDB 中,通常可以使用正则表达式(REGEXP)通过以下 2 种方式实现:

  1. 使用 POSIX 字符类 [:alnum:]
  2. 使用自定义字符范围 [A-Za-z0-9]

这两种方式在处理国际化字符(例如重音符号、非 ASCII 字母)时存在显著差异,下文将详细说明。

准备示例数据

创建一个包含纯字母、数字、带重音字符、特殊符号及空格的测试表 t1:

CREATE TABLE t1 (
    c1 VARCHAR(255) NULL
);

INSERT INTO t1 VALUES
    ('Music'),
    ('Live Music'),
    ('Café'),
    ('Café Del Mar'),
    ('100 Cafés'),
    ('homer@example.com'),
    ('1 + 1'),
    ('()'),
    ('!@#&()–[{}]:;'',?/*'),
    ('`~$^+=<>“'),
    ('$1.50'),
    ('Player 456'),
    ('007'),
    (NULL),
    (''),
    ('é'),
    ('É'),
    ('é 123'),
    ('ø'),
    ('ø 123');

SELECT c1 FROM t1;

数据输出如下:

+----------------------+
| c1                   |
+----------------------+
| Music                |
| Live Music           |
| Café                 |
| Café Del Mar         |
| 100 Cafés            |
| homer@example.com    |
| 1 + 1                |
| ()                   |
| !@#&()–[{}]:;',?/*   |
| `~$^+=<>“            |
| $1.50                |
| Player 456           |
| 007                  |
| NULL                 |
|                      |
| é                    |
| É                    |
| é 123                |
| ø                    |
| ø 123                |
+----------------------+

方法 1:使用 POSIX 字符类 [:alnum:]

MariaDB 的正则表达式引擎原生支持 POSIX 字符类。其中 [:alnum:] 表示所有字母和数字字符。它能够感知字符集和排序规则(Collation),因此会把 é、É、ø 等重音字母也正确视作字母字符。

筛选仅包含字母数字的行(不含空格)

使用起始符 ^ 和结束符 $,限制整串仅由 [:alnum:] 构成:

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[[:alnum:]]+$';

输出:

+-------+
| c1    |
+-------+
| Music |
| Café  |
| 007   |
| é     |
| É     |
| ø     |
+-------+

可以看到,Café、é、É、ø 均被正确匹配出来。

允许包含空格

空格字符在 POSIX 中不属于字母数字。如果业务上允许字符串中包含空格,可以将空格加入字符集合 [[:alnum:] ]:

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[[:alnum:] ]+$';

输出:

+---------------+
| c1            |
+---------------+
| Music         |
| Live Music    |
| Café          |
| Café Del Mar  |
| 100 Cafés     |
| Player 456    |
| 007           |
| é             |
| É             |
| é 123         |
| ø             |
| ø 123         |
+---------------+

包含任意字母数字字符即可

如果不加 ^ 和 $,表示只要字段中存在至少一个字母或数字即返回(即便同时包含特殊标点符号):

SELECT c1 FROM t1 
WHERE c1 REGEXP '[[:alnum:]]';

方法 2:使用字符范围 [A-Za-z0-9]

另一种传统方式是直接在正则表达式中指定 ASCII 字符范围 [A-Za-z0-9]。

筛选仅包含标准 ASCII 字母数字的行

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[A-Za-z0-9]+$';

输出:

+-------+
| c1    |
+-------+
| Music |
| 007   |
+-------+

[!WARNING] 与方法 1 相比,这种方式排除了 Café、é、É、ø 等字符。因为 A-Z 和 a-z 仅覆盖标准英文字母,不包含扩展 Latin 字符或 Unicode 重音字符。如果数据中包含国际化文本,直接使用此范围可能会误判漏掉数据。

允许包含空格

若允许空格,同样可以在方括号中追加空格字符:

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[A-Za-z0-9 ]+$';

输出:

+------------+
| c1         |
+------------+
| Music      |
| Live Music |
| Player 456 |
| 007        |
+------------+

总结

  • 推荐方案:优先使用 POSIX 字符类 [[:alnum:]],它对多语言字符集(如 UTF-8)支持更完善,能准确处理重音符号和多国语言字母。
  • 严格 ASCII 限制:如果业务规范要求必须是纯 ASCII 英文字符(例如用户名规则限制为只能是英文字母和数字),则应使用显式字符范围 [A-Za-z0-9]。