MySQL学习足迹记录07--数据过滤--用正则表达式进行检索-CSDN博客

本文链接：https://blog.csdn.net/piniheaven/article/details/9289705

本文用到的检索数据
mysql> SELECT prod_name FROM products
-> ORDER BY prod_name;
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Bird seed |
| Carrots |
| Detonator |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)

1.基本字符匹配
eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '1000'    #匹配"1000"
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)

eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '.000' #'.'表示匹配任意一个字符
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

2.进行OR匹配
为了搜索N个串之一，使用 ‘|’
eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '1000 | 2000'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

3.匹配几个字符之一
*匹配特定的单字符，可以通过指定一组【】括起来的字符来完成
eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '[123] Ton'
-> ORDER BY prod_name;
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

等效于：
mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '[1|2|3] Ton'
-> ORDER BY prod_name;
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

4.否定一个字符集‘^'
eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '[^123] Ton'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
+--------------+
1 row in set (0.00 sec)

5.匹配范围【n-m】
eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '[1-5] Ton'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)

注：以下操作所用到的表格数据
mysql> SELECT vend_name FROM vendors ORDER BY vend_name;
+----------------+
| vend_name |
+----------------+
| ACME |
| Anvils R Us |
| Furball Inc. |
| Jet Set |
| Jouets Et Ours |
| LT Supplies |
+----------------+
6 rows in set (0.00 sec)

6.匹配特殊字符，需用\\为前导，即转义字符
*MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）
匹配'.'
eg:
mysql> SELECT vend_name FROM vendors
-> WHERE vend_name REGEXP '.' #未用转义字符，所以不是期望的结果
-> ORDER BY vend_name;
+----------------+
| vend_name |
+----------------+
| ACME |
| Anvils R Us |
| Furball Inc. |
| Jet Set |
| Jouets Et Ours |
| LT Supplies |
+----------------+
6 rows in set (0.00 sec)

正确的应为：
mysql> SELECT vend_name FROM vendors
-> WHERE vend_name REGEXP '\\.'
-> ORDER BY vend_name;
+--------------+
| vend_name |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.00 sec)

#以下7，8，9，10列出的仅作参考，无需记忆

7.匹配字符类
[:alnum:] ==> [a-zA-Z0-9]
[:alpha:] ==> [a-zA-Z]
[:blank:] ==>空格和制表符[\\t]
[:cntrl:] ==>ASCII控制字符(ASCII0到31和127）
[:digit:] ==>[0-9]
[:graph:] ==>与[:print:]相同，但不包括空格
[:lower:] ==>[a-z]
[:punct:] ==>即不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] ==>包括空格在内的任意空白字符[\\f\\n\\r\\t\\v]
[:upper:] ==>[A-Z]
[:xdigit:]==>任意十六进制数[a-fA-F0-9]
[:print:] ==>任意可打印字符

8.空白元字符
\\f ==>换页
\\r ==>回车
\\v ==>纵向制表

9.匹配多个实例
重复元字符
* ==> 0个或多个匹配
+ ==> 1个或多个匹配（等于{1,}）
? ==> 0个或1个匹配（等于{0,1}）
{n} ==> 指定数目的匹配
{n,} ==> 不小于指定数目的匹配
{n,m} ==> 匹配数目的范围（m<=255)

eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '\$[0-9] sticks?\$ '   #'?'匹配它前面的任何字符的0次或1次出现
-> ORDER BY prod_name;
+----------------+
| prod_name |
+----------------+
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)

mysql> SELECT prod_name FROM products
-> WHERE prod_name REGEXP '[[:digit:]]{4}'    #匹配连在一起的任意四位数字
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

10.定位符
*定位元字符
^ ==> 文本的开始
$ ==> 文本的结尾
[[:<:]] ==> 词的开始
[[:>:]] ==> 词的结尾

eg:
mysql> SELECT prod_name FROM products

-> WHERE prod_name REGEXP '^[0-9\\.]' #'^'定位到串开头，[0-9\\.]表示只有在'.'或任一数字为

-> ORDER BY prod_name; #串中的第一个字符，才匹配它