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

介绍在 MariaDB 中使用 NOT REGEXP 结合 POSIX 字符类 [:alnum:] 或自定义字符范围 [A-Za-z0-9] 筛选仅包含标点、特殊符号和空格等非字母数字字符记录的实用方法。

发布于

非字母数字字符包括标点符号、特殊符号(例如 !@#&()–[{}]:;',?/*、`~$^+=<>“)以及空白字符(如空格、制表符、换行符等)。

在 MariaDB 中,如果需要筛选出完全不包含任何字母或数字(即仅由非字母数字字符构成)的记录,可以通过对正则表达式进行取反操作(NOT REGEXP)来实现。主要有以下 2 种方式:

  1. 使用 POSIX 字符类 [:alnum:] 结合 NOT REGEXP
  2. 使用自定义字符范围 [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]。