在 MariaDB 中查询仅包含非字母数字字符的行的 2 种方法
介绍在 MariaDB 中使用 NOT REGEXP 结合 POSIX 字符类 [:alnum:] 或自定义字符范围 [A-Za-z0-9] 筛选仅包含标点、特殊符号和空格等非字母数字字符记录的实用方法。
非字母数字字符包括标点符号、特殊符号(例如 !@#&()–[{}]:;',?/*、`~$^+=<>“)以及空白字符(如空格、制表符、换行符等)。
在 MariaDB 中,如果需要筛选出完全不包含任何字母或数字(即仅由非字母数字字符构成)的记录,可以通过对正则表达式进行取反操作(NOT REGEXP)来实现。主要有以下 2 种方式:
- 使用 POSIX 字符类
[:alnum:]结合NOT REGEXP - 使用自定义字符范围
[A-Za-z0-9]结合NOT REGEXP
下面通过具体示例演示这两种方式的操作与差异。
准备示例数据
创建一个包含纯字母、数字、带重音字符、特殊符号及空值的测试表 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 字符类。我们可以匹配任意包含字母数字字符的记录,再使用 NOT REGEXP 进行逻辑取反,从而获得完全不包含任何字母和数字的行:
SELECT c1 FROM t1
WHERE c1 NOT REGEXP '[[:alnum:]]';
输出:
+----------------------+
| c1 |
+----------------------+
| () |
| !@#&()–[{}]:;',?/* |
| `~$^+=<>“ |
| |
+----------------------+从结果可以看出:
- 像
1 + 1、$1.50这类既有符号又有数字/字母的行已被过滤掉; - 像
é、É、ø等重音字母在 POSIX 字符类中被正确识别为字母,因而也被排除; - 剩下的行都是纯标点符号,或者是空字符串
''。
过滤空字符串
空字符串 '' 本身也不包含任何字母数字,因此默认会被匹配出来。如果希望在结果中同时排除空字符串,可以使用 NULLIF(c1, ''):
SELECT c1 FROM t1
WHERE NULLIF(c1, '') NOT REGEXP '[[:alnum:]]';
输出:
+----------------------+
| c1 |
+----------------------+
| () |
| !@#&()–[{}]:;',?/* |
| `~$^+=<>“ |
+----------------------+[!NOTE]
NULLIF(c1, '')在c1为空字符串时返回NULL。在 SQL 中,任何与NULL的正则比较都会返回UNKNOWN(逻辑上不满足WHERE条件),从而安全过滤掉空字符串。
方法 2:使用字符范围 [A-Za-z0-9]
另一种方式是直接使用 ASCII 字符范围 [A-Za-z0-9]:
SELECT c1 FROM t1
WHERE c1 NOT REGEXP '[A-Za-z0-9]';
输出:
+----------------------+
| c1 |
+----------------------+
| () |
| !@#&()–[{}]:;',?/* |
| `~$^+=<>“ |
| |
| é |
| É |
| ø |
+----------------------+排除空字符串后:
SELECT c1 FROM t1
WHERE NULLIF(c1, '') NOT REGEXP '[A-Za-z0-9]';
输出:
+----------------------+
| c1 |
+----------------------+
| () |
| !@#&()–[{}]:;',?/* |
| `~$^+=<>“ |
| é |
| É |
| ø |
+----------------------+[!WARNING] 注意观察此查询的输出:
é、É、ø出现在了结果集中! 这是因为[A-Za-z0-9]仅匹配 ASCII 英文字母和数字。对于带有重音符号的 Unicode 字符(如é、ø),它们不在A-Z或a-z范围内,因此被NOT REGEXP '[A-Za-z0-9]'误判定为“非字母数字”。 除非业务明确需要将非 ASCII 字符视作非标准字符,否则应优先使用方法 1 的[[:alnum:]]。
总结
- 推荐方案:使用
WHERE NULLIF(c1, '') NOT REGEXP '[[:alnum:]]',能准确兼顾 UTF-8 / 多字节字符集下的各种国际化语言字母,同时排除了空字符串。 - 自定义字符集:若只需严格排除标准 26 个英文字母和数字,可采用
[A-Za-z0-9]。