13、MySQL的正则表达式

1、基本字符串匹配 regexp

mysql> select * from products where prod_name regexp '1000';
+---------+---------+--------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                             |
+---------+---------+--------------+------------+---------------------------------------+
| JP1000  |    1005 | JetPack 1000 | 35.00      | JetPack 1000, intended for single use |
+---------+---------+--------------+------------+---------------------------------------+
1 row in set (0.08 sec)

.号代表任意一个字符 要加\转义符号变成原来的.

mysql> select * from products where prod_name regexp '.000';
+---------+---------+--------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                             |
+---------+---------+--------------+------------+---------------------------------------+
| JP1000  |    1005 | JetPack 1000 | 35.00      | JetPack 1000, intended for single use |
| JP2000  |    1005 | JetPack 2000 | 55.00      | JetPack 2000, multi-use               |
+---------+---------+--------------+------------+---------------------------------------+
2 rows in set (0.07 sec)

3、or的方式 |

mysql> select * from products where prod_name regexp '1000|2000';
+---------+---------+--------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                             |
+---------+---------+--------------+------------+---------------------------------------+
| JP1000  |    1005 | JetPack 1000 | 35.00      | JetPack 1000, intended for single use |
| JP2000  |    1005 | JetPack 2000 | 55.00      | JetPack 2000, multi-use               |
+---------+---------+--------------+------------+---------------------------------------+
2 rows in set (0.08 sec)

4、使用范围进行搜索 [1 2]

mysql> select * from products where prod_name regexp '[1 2]000';
+---------+---------+--------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                             |
+---------+---------+--------------+------------+---------------------------------------+
| JP1000  |    1005 | JetPack 1000 | 35.00      | JetPack 1000, intended for single use |
| JP2000  |    1005 | JetPack 2000 | 55.00      | JetPack 2000, multi-use               |
+---------+---------+--------------+------------+---------------------------------------+
2 rows in set (0.07 sec)

5、排它符 ……^…… 取反

mysql> select * from products where prod_name regexp '[^2]000';
+---------+---------+--------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                             |
+---------+---------+--------------+------------+---------------------------------------+
| JP1000  |    1005 | JetPack 1000 | 35.00      | JetPack 1000, intended for single use |
+---------+---------+--------------+------------+---------------------------------------+
1 row in set (0.06 sec)

6、特殊字符的匹配 \.

mysql> select * from products where prod_name regexp '\\.[1-9]';
+---------+---------+--------------+------------+-----------------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                                     |
+---------+---------+--------------+------------+-----------------------------------------------+
| ANV01   |    1001 | .5 ton anvil | 5.99       | .5 ton anvil, black, complete with handy hook |
+---------+---------+--------------+------------+-----------------------------------------------+
1 row in set (0.07 sec)

7、？代表任意一个字符，‘stick？\)’代表最后一个字符是）。

mysql> select * from products where prod_name regexp 'stick?\\)';
+---------+---------+---------------+------------+------------------------+
| prod_id | vend_id | prod_name     | prod_price | prod_desc              |
+---------+---------+---------------+------------+------------------------+
| TNT1    |    1003 | TNT (1 stick) | 2.50       | TNT, red, single stick |
+---------+---------+---------------+------------+------------------------+
1 row in set (0.07 sec)

8、字符类[0-9] [a-z] [A-Z] {4}

mysql> select * from products where prod_name regexp '[0-9]{4}';
+---------+---------+--------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                             |
+---------+---------+--------------+------------+---------------------------------------+
| JP1000  |    1005 | JetPack 1000 | 35.00      | JetPack 1000, intended for single use |
| JP2000  |    1005 | JetPack 2000 | 55.00      | JetPack 2000, multi-use               |
+---------+---------+--------------+------------+---------------------------------------+
2 rows in set (0.10 sec)

[[:digit:]]

mysql> select * from products where prod_name regexp '[[:digit:]]{4}';
+---------+---------+--------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name    | prod_price | prod_desc                             |
+---------+---------+--------------+------------+---------------------------------------+
| JP1000  |    1005 | JetPack 1000 | 35.00      | JetPack 1000, intended for single use |
| JP2000  |    1005 | JetPack 2000 | 55.00      | JetPack 2000, multi-use               |
+---------+---------+--------------+------------+---------------------------------------+
2 rows in set (0.07 sec)