mysql 正则表达式

                                                                    mysql 正则表达式

^     在字符串的开始进行匹配

$       在字符串的末尾进行匹配

.         匹配任意单个字符，包括换行符

[...]    匹配出括号内的任意字符

[^...]   匹配不出括号内的任意字符

a*     匹配0个或多个 a(包括空串）

a+    匹配1个或多个a (不包括空串）

a?     匹配1个或0个a 

a1 |a2  匹配a1或a2

a(m)   匹配m个a

a(m,)   匹配m个或更多个a

a(m,n)   匹配m到n 个a

a(,n)   匹配0到n个a

(...)   将模式元素组成单一元素

mysql> select 'abcdefg' regexp '^a';
+-----------------------+
| 'abcdefg' regexp '^a' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.01 sec)


mysql> select 'abcdefg' regexp 'g$';
+-----------------------+
| 'abcdefg' regexp 'g$' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)


mysql> select 'abcdefg' regexp '.h','abcdefg' regexp '.f';
+-----------------------+-----------------------+
| 'abcdefg' regexp '.h' | 'abcdefg' regexp '.f' |
+-----------------------+-----------------------+
|                     0 |                     1 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)


mysql> select 'abcdefg' regexp "[fhk]";
+--------------------------+
| 'abcdefg' regexp "[fhk]" |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)


mysql> select 'efg' regexp "[^xyz]",'x' regexp "[^xyz]";
+-----------------------+---------------------+
| 'efg' regexp "[^xyz]" | 'x' regexp "[^xyz]" |
+-----------------------+---------------------+
|                     1 |                   0 |
+-----------------------+---------------------+
1 row in set (0.00 sec)

mysql> create table customer(
    -> store_id int primary key not null auto_increment,
    -> first_name  varchar(10),
    -> last_name   varchar(10),
    -> address_id varchar(20),
    -> email  varchar(20)
    -> );
Query OK, 0 rows affected (0.07 sec)


mysql> insert into customer values(1,'100mail','beijing',600,'beijing@100.com');
Query OK, 1 row affected (0.02 sec)


mysql> insert into customer values(2,'163mail','beijing',605,'beijing@163.com');
Query OK, 1 row affected (0.01 sec)


mysql> select first_name,email from customer where email regexp "@163[,.]com$";
+------------+-----------------+
| first_name | email           |
+------------+-----------------+
| 163mail    | beijing@163.com |
+------------+-----------------+
1 row in set (0.01 sec)