初识Mysql(part7)--我需要知道的5个关于正则的小知识

学习笔记，以代码和例子堆砌而成，方便查阅。
参考书籍：《Mysql必知必会》等
要点：REGEXP关键字、BINARY关键字、转义、字符类、正则测试

因为正则表达式笔者专门写了一系列Blog，所以这里对正则表达式的用法，不做过多阐述，主要看正则在Mysql中的应用。

---

此Blog里的例子都会用下面store数据表：

# id, product, price, grade, pro_date, note
'1', 'Snob201', '2300.23', 'B', 1998, 'Snob 2000 expand'
'2', 'Snob301', '1289.21', 'C', 1996, 'Snob 3000 expand'
'3', 'Snob401', '4876.34', 'A', 2002, '2.0 Snob 3000 expand'
'4', 'GibeX01', '3232.76', 'A', 2000, 'X serise 1'
'5', 'GibeX02', '1200.00', 'D', 2000, 'X serise 2'
'6', 'GibeU200', '5010.12', 'B', 2005, '2.0 X serise 2'
'7', 'GibeU300', '3000.78', 'A', 2010, '2.1 X serise 2'
'8', 'SnobR001', '6321.54', 'B', 2010, 'R (no.1)'
'9', 'SnobR002', '3245.54', 'A', 2011, 'R (no.2)'
'10', 'AdroitX001', '6543.90', 'B', 2014, 'Lose'
'11', 'AdroitX002', '1050.00', 'D', 2015, 'Lose'

注意！Mysql仅支持多数正则表达式实现的一个很小的子集。

• 关键字LIKE与关键字REGEXP

首先，看下面这行sql语句：

SELECT * from store WHERE product REGEXP '200';

输出：

# id, product, price, grade, pro_date, note
'6', 'GibeU200', '5010.12', 'B', 2005, '2.0 X serise 2'

我们看到拿到了product='GibeU200’的记录。

现在我想理顺REGEXP和LIKE的区别。

我们把上面的sql语句中的REGEXP替换成LIKE：

SELECT * from store WHERE product LIKE '200';

输出：

# id, product, price, grade, pro_date, note

啥都没匹配到。

这是因为REGEXP将找出product中包含’200’的记录，而LIKE则要找的是product='200’的记录。如果LIKE想要匹配部分值(与REGEXP语句相同的效果)，则需要使用通配符%或通配符_(%200%)。那么怎样使REGEXP匹配全部值(与LIKE有相同的效果)呢，这时就要用元字符^和元字符$(^200$)

• 匹配大小写

首先，来一段代码：

SELECT * FROM store WHERE product REGEXP 'snob';

输出：

# id, product, price, grade, pro_date, note
'1', 'Snob201', '2300.23', 'B', 1998, 'Snob 2000 expand'
'2', 'Snob301', '1289.21', 'C', 1996, 'Snob 3000 expand'
'3', 'Snob401', '4876.34', 'A', 2002, '2.0 Snob 3000 expand'
'8', 'SnobR001', '6321.54', 'B', 2010, 'R (no.1)'
'9', 'SnobR002', '3245.54', 'A', 2011, 'R (no.2)'

可以看到，匹配到了5条记录，但是这里我们用小写的’snob’就匹配到了大写的’Snob’，我不想这样，我想大写匹配大写，小写匹配小写，这里可以使用BINARY关键字来实现：

SELECT * FROM store WHERE product REGEXP BINARY 'snob';

输出：

# id, product, price, grade, pro_date, note

嗯！不错，这样小写的’snob’就啥也匹配不到了。

• 转义

如果我想匹配.可咋整，我们知道.是正则表达式中的元字符，所以我们需要对其进行转义，方法就是在.之前加\\。来个例子先：

SELECT * FROM store WHERE note REGEXP '2\\.0';

输出：

# id, product, price, grade, pro_date, note
'3', 'Snob401', '4876.34', 'A', 2002, '2.0 Snob 3000 expand'
'6', 'GibeU200', '5010.12', 'B', 2005, '2.0 X serise 2'

多数正则表达式用\单斜杠就能实现转义的功能，那为啥mysql要用\\双斜杠呢？这里和python用\\双斜杠进行转义的原因差不多，详见：正则表达式的转义。

• 匹配字符类

因为笔者实在懒得打字了，就放图片吧：

来个字符类的例子：

SELECT * FROM store WHERE note REGEXP '[[:digit:]]{4}';

输出：

# id, product, price, grade, pro_date, note
'1', 'Snob201', '2300.23', 'B', 1998, 'Snob 2000 expand'
'2', 'Snob301', '1289.21', 'C', 1996, 'Snob 3000 expand'
'3', 'Snob401', '4876.34', 'A', 2002, '2.0 Snob 3000 expand'

再来个字符类例子，加深一下印象呗：

SELECT * FROM store WHERE note REGEXP '^[[:digit:]]\\.[0-9]';

输出：

# id, product, price, grade, pro_date, note
'3', 'Snob401', '4876.34', 'A', 2002, '2.0 Snob 3000 expand'
'6', 'GibeU200', '5010.12', 'B', 2005, '2.0 X serise 2'
'7', 'GibeU300', '3000.78', 'A', 2010, '2.1 X serise 2'

• 对正则表达式的简单测试

可以在不使用数据库的情况下，用SELECT来测试正则表达式，REGEXP检查将返回0(没有匹配)或1(匹配)，来个例子：

SELECT '应用回归分析第4版' REGEXP '第[1-9]版';

输出：

'1'