MySQL正则表达式

004-正则表达式

MySQL利用REGEXP命令提供给用户扩展的正则表达式功能，熟悉掌握REGEXP的功能可以使模式匹配工作事半功倍。

表达式     说明
^       字符串开始处进行匹配（以“后面字符串”开始匹配字符）
$       字符串结尾处进行匹配（以“前面字符串”结尾匹配字符）
.       匹配任意单个字符，包括换行符。
[…]     匹配括号内的任意字符
[^…]    不匹配出括号内的任意字符
a*      匹配零个或多个a（包括空串）同a?
a?      匹配零个或多个a（包括空串）同a*
a+      匹配1个或多个a（不包括空串）

a1|a2   匹配a1或a2
a{m}    匹配M个a
a{m,}   匹配m个或更多个a
a{m,n}  匹配m到n个a
a(,n)   匹配0到n个a，已经放弃。
(…)     将模式元素组成单一元素

示例：不区分大小写。跟字符集相关。collation_connection   utf8mb4_0900_ai_ci   ci不区分大小写。
加上binary表示改表达式强制区分大小写。

(1)以..开头，以..结尾

SELECT * FROM `emp` where ename rlike '^A';
等同
SELECT * FROM `emp` where ename regexp '^A';
等同
SELECT * FROM `emp` where ename regexp '^a';

^，$，. 在字符串开始处匹配字符，匹配成功返回1，否则为0.

select 'asdf' regexp 'f$';
select 'asdf' regexp '^a';
select 'asdf' regexp 'a.';
select 'asdf' regexp '.';

（2）匹配单个字符。
. 匹配任意单个字符。binary使用二进制匹配，区分大小写

SELECT * FROM `emp` where ename regexp '.c';

SELECT * FROM `emp` where ename regexp binary 'c.';

（3）匹配指定任意字符，排除指定字符串。

[…] 匹配中间任一字符，[^…] 不匹配任一字符。两个其他字符之间的-字符形成一个范围

SELECT * FROM `emp` where ename regexp '[3f]';

匹配到返回1，未匹配到返回0.
select 'er11' regexp '[r1213]';

SELECT * FROM `emp` where ename regexp '[a-d]';

注意：[^..] 这里除了[]中的串，其他都匹配出来，[]中的字符串是完整匹配，而不是任一字符。
SELECT * FROM `emp` where ename regexp '[^ALLEN]';

匹配多个：
SELECT * FROM `emp` where ename regexp '[^ALLEN|WARD]';

SELECT * FROM `emp` where ename regexp '[^a-d]';  这里只排除了一个字符串"abcd"，只有没有这样序列的字符串，都检索出来。

但是：
select 'asdf' regexp '[^sdf]';   1 没有匹配到，返回1，
select 'asdf' regexp '[^asdf]';    0 匹配了，所以返回0.

（4）任意匹配
a* = a? 匹配任意各个任意字符（包括0个，空串）。
a+      匹配1个或多个，不包括空串

select 'asdf' regexp 'a?';
select 'asdf' regexp 'a*';
select 'asdf' regexp 'a+';
三个一致。

select 'a sdf' regexp 'a?s';
select 'a\nsdf' regexp 'a*s';
select 'a sdf' regexp 'a+s';

最后一个没匹配上。它只能匹配as,aas,aaas....
而上面2个，能匹配a s,  s,adbfs,dbfs.....

SELECT * FROM `emp` where ename regexp 'c*t';
能够匹配c..t的，也能匹配带t字符串。

SELECT * FROM `emp` where ename regexp 'c*';
能匹配到全部14条，因为c*表示0个或多个，也就是说没有c也可以匹配，= 直接匹配*或者?

换成SELECT * FROM `emp` where ename regexp binary 'c.';
带c都能匹配，只有c结尾的无法匹配。
select 'a sdfc' regexp 'c.';

（5）指定匹配字符个数。

select 'aaaffc' regexp 'a{2}';          1
select 'aaaffc' regexp 'a{3}';          1
select 'aaaffc' regexp 'a{1,}';          1
select 'aaaffc' regexp 'a{,3}';          语法错误
select 'aaaffc' regexp 'a{1,3}';          1

a{1,3}表示1-3个，包含边界值。相当于>=1 <=3.

a*  a? 可以写成 a{0,}
a+  可以写成 a{1,}

SELECT * FROM `emp` where ename regexp 't{2}';

(…)将模式元素组成单一元素
将括号里面的字符做一个整体进行匹配，如果多个字符，只匹配上一个，返回0，必须都匹配上，也就是说匹配括号内的字符串。

当子串在字符串的开头或结尾的时候。(...)可以改写成：

SELECT * FROM `emp` where ename regexp '^sc';       SCOTT
SELECT * FROM `emp` where ename regexp 'tt$';       SCOTT

当一个字符串，需要匹配的子串，在中间，也可以这样。

SELECT * FROM `emp` where ename regexp 'co';        SCOTT

正例：
SELECT 'pihhhpi' REGEXP '^(pi)*$';          0
SELECT 'pipi' REGEXP '^(pi)*$';             1
SELECT 'pi' REGEXP '^(pi)*$';               1
SELECT 'pip' REGEXP '^(pi)*$';              0

^(pi)*$
表示，以pi为整体 开头,*不再表示任意个任意字符，而是匹配1+个pi，以pi结尾。
可以改写成： ^(pi){1,}(pi)$   语法稍显麻烦。而且无法匹配"pi" 这个单字符串。

因此这就是(...)的适用场景。

=============================正则表达式在日常中的应用 ===============================

（1）过滤163邮箱

例如我们需要过滤出，使用163邮箱的用户信息。
同时 要求: @和#都是合法的邮件用户名与域名分隔符。yy#163,com 也算163的邮箱
        ,和.都是合法的域名与域名后缀分隔符。yy@163,com 也算163的邮箱

方案1：like
select * from user_info where email like '%@163.com' or like '%@163,com' or like '%#163,com' or like '%#163.com'

方案二：正则
select * from user_info where email regexp '.*[@#]163[.,]com$'

(2)随机抽取test表数据的样本

oracle中用DBMS_RANDOM，MySQL中使用RAND()函数来实现，可以利用这个函数与order by 一起使用，来完成随机抽取某些行的功能。其实就是order by rand()能够把数据随机排序。