MYSQL-LIKE和REGEXP的区别

使用的数据

mysql> select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
|  1 | Google       | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝         | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟教程     | http://www.runoob.com/    |   300 | CN      |
|  4 | 微博         | http://weibo.com/         |    20 | CN      |
|  5 | Facebook     | https://www.facebook.com/ |     3 | USA     |
|  6 | 百度         | www.baidu.com             |   223 | CN      |
+----+--------------+---------------------------+-------+---------+
6 rows in set (0.00 sec)

相同点

like 与 regexp 均默认不区分大小写, 如果要区分, 增加关键字binary.

-- where name like binary '%a%'
-- where name regexp binary '.*a.*'
mysql> select * from websites where name like binary "g%";
Empty set (0.00 sec)
mysql> select * from websites where name like binary "G%";
+----+--------+------------------------+-------+---------+
| id | name   | url                    | alexa | country |
+----+--------+------------------------+-------+---------+
|  1 | Google | https://www.google.cm/ |     1 | USA     |
+----+--------+------------------------+-------+---------+
1 row in set (0.00 sec)

重要区别

like 整个字段匹配表达式成功才返回
regexp 部分字符匹配表达式成功即可返回

差别类似于python中re模块的两个方法
like  --> re.match
regexp --> re.search

-- 下面like匹配表达式只能匹配name字段有且只有字母g的行
mysql> select * from websites where name like binary "g";
Empty set (0.00 sec)
-- 下面regexp匹配表达式能匹配name字段中包含字母g的行
mysql> select * from websites where name regexp binary "g";
+----+--------+------------------------+-------+---------+
| id | name   | url                    | alexa | country |
+----+--------+------------------------+-------+---------+
|  1 | Google | https://www.google.cm/ |     1 | USA     |
+----+--------+------------------------+-------+---------+
1 row in set (0.00 sec)    

/* 如果 regexp 需要匹配整个字段, 最好使用^$
* 比如下面想匹配name为6个字母的行, 期望结果是只显示"Google"行
* 但实际却把"Facebook"行也匹配出来,因为也满足包含6个字母的表达式.
* /
mysql> select * from websites where name regexp binary "[a-zA-z]{6}";
+----+----------+---------------------------+-------+---------+
| id | name     | url                       | alexa | country |
+----+----------+---------------------------+-------+---------+
|  1 | Google   | https://www.google.cm/    |     1 | USA     |
|  5 | Facebook | https://www.facebook.com/ |     3 | USA     |
+----+----------+---------------------------+-------+---------+
2 rows in set (0.00 sec)
-- 如果只想匹配"Google"行, 按regexp的特性, 则使用"^[a-zA-z]{6}$", 限定整个字段匹配6个字母即可.
mysql> select * from websites where name regexp binary "^[a-zA-z]{6}$";
+----+--------+------------------------+-------+---------+
| id | name   | url                    | alexa | country |
+----+--------+------------------------+-------+---------+
|  1 | Google | https://www.google.cm/ |     1 | USA     |
+----+--------+------------------------+-------+---------+
1 row in set (0.00 sec)

regexp 测试

可以使用 select 语句来测试正则表达式, 匹配返回1, 否则返回0

mysql> select "123" regexp "^\\d{1}$";
+-------------------------+
| "123" regexp "^\\d{1}$" |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select "123" regexp "^\\d{1,3}$"; -- 注意 {1,3} 中间如有空格会报错
+---------------------------+
| "123" regexp "^\\d{1,3}$" |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

regexp 拓展

1. 如使用反斜杠转义, 则需要使用双反斜杠\\ (mysql解析一个, 正则引擎解析另一个) , 比如表示数字 \\d, 字母 \\w.
2. 匹配字符类

[:word:] —> 字母、数字或下划线字符 \\w
[:alpha:] —> [a-zA-Z]
[:alnum:] —> [0-9a-zA-Z]
[:blank:] —> 空格或制表符[ \t]
[:digit:] —> \\d
[:lower:] —> [a-z]
[:upper:] —> [A-Z]
[:space:] —> 任意空白字符 \\s
[:xdigit:] —> 16进制字符 [a-fA-F0-9]