在 MariaDB 中查询仅包含字母与数字的行的 2 种方法
介绍在 MariaDB 中查询仅包含字母数字(Alphanumeric)字符记录的两种方法:使用 POSIX 字符类 [:alnum:] 与使用自定义字符范围 [A-Za-z0-9],并对比二者对重音字母和国际化字符集的支持差异。
在数据清洗与校验过程中,经常需要筛选出仅包含“字母和数字”(Alphanumeric)字符的记录。在 MariaDB 中,通常可以使用正则表达式(REGEXP)通过以下 2 种方式实现:
- 使用 POSIX 字符类
[:alnum:] - 使用自定义字符范围
[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]。