MySQL中使用正则表达式查询
基本形式:
属性名 regexp '匹配方式'
属性名表示需要查询的字段的名称;匹配方式表示以哪种方式来进行匹配查询。
正则表达式模式字符:
^: 匹配字符串开始的部分
$: 匹配字符串结束的部分
.: 代表字符串中的任意一个字符,包括回车和换行
[字符集合]: 匹配“字符集合”中的任何一个字符
[^字符集合]: 匹配出了“字符集合”以外的任何一个字符
S1|S2|S3: 匹配S1、S2和S3中的任意一个字符串
*: 代表多个该符号之前的字符,包括0和1个
+: 代表多个该符号之前的字符,包括1个
字符串{N}: 字符串出现N次
字符串{M,N}: 字符串出现至少M次,最多N次
例子:
//(0).全表记录
mysql> select * from computer_stu;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | Lily | 85 |
| 1002 | Tom | 91 |
| 1003 | Jim | 87 |
| 1004 | Aric | 77 |
| 1005 | Lucy | 65 |
| 1006 | Andy | 99 |
| 1007 | Ada | 85 |
| 1008 | Jeck | 70 |
+------+------+-------+
8 rows in set (0.00 sec)
//(1).查询以特定字符或字符串开头的记录
mysql> select * from computer_stu where name regexp '^L';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | Lily | 85 |
| 1005 | Lucy | 65 |
+------+------+-------+
2 rows in set (0.00 sec)
//(2).查询以特定字符或字符串结尾的记录
mysql> select * from computer_stu where name regexp 'y$';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | Lily | 85 |
| 1005 | Lucy | 65 |
| 1006 | Andy | 99 |
+------+------+-------+
3 rows in set (0.00 sec)
//(3).用符号"."来代替字符串中的任意一个字符
mysql> select * from computer_stu where name regexp '^L..y$';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | Lily | 85 |
| 1005 | Lucy | 65 |
+------+------+-------+
2 rows in set (0.00 sec)
//其中^L表示以字母L开头,两个"."表示两个任意字符,y$表示以字母y结尾
//(4).匹配指定字符中的任意一个
mysql> select * from computer_stu where name regexp '[ci]';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | Lily | 85 |
| 1003 | Jim | 87 |
| 1004 | Aric | 77 |
| 1005 | Lucy | 65 |
| 1008 | Jeck | 70 |
+------+------+-------+
5 rows in set (0.00 sec)
//使用方括号可以指定集合的区间。[a-z]表示a~z的所有字母(mysql字段大小写不敏感),[0-9]表示从0~9的所有数字,[a-z0-9]表示包含所有的字母和数字。
//(5).匹配指定字符以外的字符
mysql> select * from computer_stu where name regexp '[^a-w0-9]';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | Lily | 85 |
| 1005 | Lucy | 65 |
| 1006 | Andy | 99 |
+------+------+-------+
3 rows in set (0.00 sec)
//(6).匹配指定字符串
mysql> select * from computer_stu where name regexp 'il|dy';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | Lily | 85 |
| 1006 | Andy | 99 |
+------+------+-------+
2 rows in set (0.00 sec)
//(7).使用"*"和"+"来匹配多个字符
mysql> select * from computer_stu where name regexp 'i*m';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1002 | Tom | 91 |
| 1003 | Jim | 87 |
+------+------+-------+
2 rows in set (0.00 sec)
mysql> select * from computer_stu where name regexp 'i+m';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1003 | Jim | 87 |
+------+------+-------+
1 row in set (0.00 sec)
//(8).使用{M}或者{M,N}来制定字符串连续出现的次数
mysql> insert into computer_stu values(1009,'aaabb',85),(1010,'aabbc',79),(1011,'abccc',98);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from computer_stu where name regexp 'a{3}';
+------+-------+-------+
| id | name | score |
+------+-------+-------+
| 1009 | aaabb | 85 |
+------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from computer_stu where name regexp 'a{2,3}';
+------+-------+-------+
| id | name | score |
+------+-------+-------+
| 1009 | aaabb | 85 |
| 1010 | aabbc | 79 |
+------+-------+-------+
2 rows in set (0.00 sec)