mysql正则匹配

Question

我希望用mysql正则匹配这样的字符串：alpha alpha(1) alpha(2)
但是不能匹配alpha( 1) alpha(3c)这样的子串 也就是说括号里必须是数字 并且括号在最后出现也可以不出现
我写了一个但是不大对 麻烦各位大虾看一下 指正错误 ^alpha(\(\d+\))?$

mysql版本Ver 14.14 Distrib 5.5.47

Answer 1

mysql> create table alpha(alpha char(20));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into alpha values('alpha'),('alpha(1)'),('alpha(2)'),('alpha( 1)'),('alpha(3c)');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from alpha;
+-----------+
| alpha     |
+-----------+
| alpha     |
| alpha(1)  |
| alpha(2)  |
| alpha( 1) |
| alpha(3c) |
+-----------+
5 rows in set (0.00 sec)
mysql> select * from alpha where alpha regexp '^alpha(\\([0-9]+\\))?$';
+----------+
| alpha    |
+----------+
| alpha    |
| alpha(1) |
| alpha(2) |
+----------+
3 rows in set (0.00 sec)