MySQL正则匹配字符类

Z时代
2024-01-10
分类：技术分享

我不懂MySQL中匹配字符类到底怎么理解？[:upper:]表示任意大写字母，同[A-Z]，通过下面这一句得到出现过大写字母的prod_name:

SELECT prod_name FROM Products WHERE prod_name REGEXP '[:upper:]';

使用[[:upper:]]则会全部输出。

同样的道理，在使用数字字符类[:digit:]时，需要双括号才能匹配到出现过数字的prod_name：

SELECT prod_name FROM Products WHERE prod_name REGEXP '[[:digit:]]';

而使用单括号则不行，请问这是为什么？该怎么理解？是否需要指定正则匹配" title="正则匹配">正则匹配出现的次数？如何指定？

mysql> SELECT prod_name FROM Products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)
mysql> SELECT prod_name FROM Products WHERE prod_name REGEXP '[:upper:]';
+--------------+
| prod_name    |
+--------------+
| Detonator    |
| Bird seed    |
| Carrots      |
| Fuses        |
| JetPack 1000 |
| JetPack 2000 |
| Safe         |
+--------------+
7 rows in set (0.01 sec)
mysql> SELECT prod_name FROM Products WHERE prod_name REGEXP '[[:upper:]]';
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.01 sec)
mysql> SELECT prod_name FROM Products WHERE prod_name REGEXP '[[:digit:]]';
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| JetPack 1000   |
| JetPack 2000   |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
7 rows in set (0.00 sec)
mysql> SELECT prod_name FROM Products WHERE prod_name REGEXP '[:digit:]';
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+12 rows in set (0.00 sec)