SQL回炉重造05_数据过滤_正则表达式

数据过滤——正则表达式

• 正则表达式（Regular Expression）：是用来匹配文本的特殊的串（字符集合）。作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。
  • 但要注意，MySQL中的正则表达式仅支持正则表达式中的一小部分内容！

1. 基本字符匹配——特殊字符（.）

• REGEXP关键字：正则表达式的标识符，用于告诉数据库后面所跟的内容为正则表达式处理。
  • 特殊字符**（.）**：作用是匹配任意一个字符
  • 匹配不区分大小写：MySQL中的正则表达式匹配默认不区分大小写，为了实现区分大小写的功能，可以使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'Jet'
  • 与LIKE的区别：LIKE会匹配整个列，如果被匹配的文本在列值中出现，LIKE将不能返回相应的行（除非使用通配符），但REGEXP是在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会返回相应的行。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+

SELECT prod_name 
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

2. OR匹配——特殊字符（|）

• 特殊字符（|）：匹配给定值其中之一，功能上类似于使用OR的SELECT语句，但多个OR可以并入单个使用|的正则表达式中。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000|3000'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name = '1000' OR prod_name = '2000' OR prod_name = '3000';

3. 匹配几个字符之一——特殊字符（[ ]）

• 特殊字符（[ ]）:另一种形式的OR语句，是使用多个|特殊字符的简写，例如[123]就是[1|2|3]的简写。
  • 字符集合也可以被否定，即，它们将匹配除了指定字符以外的任何字符
  • [ | ]配合使用，需要使用[ ]来指定OR语句查找的内容，例如’1|2|3 Ton’，MySQL会认为你查找的是1、2或3 Ton，除非将123和|括在集合中，否则它将应用于整个串。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+

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   |
| JetPack 1000  |
| JetPack 2000  |
| TNT (1 stick) |
+---------------+

4. 匹配范围——特殊字符（-）

• 特殊字符（-）：可以用来定义一个范围，一般与[ ]配合使用，例如[0-9]等同于[0123456789]
  • 范围不限于完整的集合，例如[1-3]、[5-9]也是合法的，此外，范围不一定是数值，如[a-z]可以匹配任意字母字符

5. 匹配特殊字符——转义字符\\

• 转义字符\\（escaping）：匹配特殊字符时，用来取消其特殊含义的字符，这种功能称为转义。如为了匹配-字符，需要使用’\\-’

  • 匹配反斜杠\：为了匹配反斜杠本身，需要使用\\\
  • \或\\：多数正则表达式的实现是使用单个反斜杠作为转义字符，但MySQL要求使用两个反斜杠作为转义字符（MySQL解释一个，正则表达式库解释另一个）

• 转义字符也可以用来引用元字符（具有特殊含义的字符），如下表：

  • 元字符	说明
    \\f	换页符
    \\n	换行符
    \\r	回车符
    \\t	制表符
    \\v	纵向制表符

6. 匹配字符类

• 字符类（character class）：预定义的字符集，方便实现对应的匹配

  • 字符类	说明
    [:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
    [:alpha:]	任意字符(同[a-zA-Z])
    [:blank:]	空格和制表符(同[\\t])
    [:cntrl:]	ASCII控制字符（ASCII 0到31和127）
    [:digit:]	任意数字(同[0-9])
    [:graph:]	与[:print:]相同，但不包括空格
    [:lower:]	任意小写字母(同[a-z])
    [:print:]	任意可打印字符
    [:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
    [:space:]	包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v])
    [:upper:]	任意大写字母(同[A-Z])
    [:xdigit:]	任意十六进制数字(同[a-fA-f0-9])

7. 匹配多个实例——限定符

• 匹配特定数量的文本

限定符	说明
*	0个或多个匹配
+	1个或多个匹配(同{1,})
?	0个或1个匹配(同{0,1})
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

• 对限定符的补充说明，限定符是表示前面的子表达式出现的次数，比如zo*可以匹配z，zoo，默认情况子表达式是前一个字符，如果要多个字符需要括起来，如do(es)?

• 使用正则表达式时，写法往往是多种多样的，如：

  • # 匹配连在一起的任意4个数字
    SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '[:digit:]{4}'
    ORDER BY prod_name;
    
    SELECT prod_name
    FROM products
    WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'
    ORDER BY prod_name;
    

8. 定位符

• 匹配特定位置的文本

  • 定位符	说明
    ^	文本的开始
    $	文本的结尾
    [[:<:]]（MySQL8之前）	词的开始
    [[:>:]]（MySQL8之前）	词的结尾

  • ^的双重用途：在集合中[ ]用来否定集合，否则就是定位符

  • 使REGEXP起于LIKE类似的作用：通过^开始每个表达式，用$结束表达式，可以使REGEXP作用于LIKE一样，即匹配整个串

• 测试正则表达式：可以在不使用数据库表的情况下测试正则表达式，REGEXP检查将总是返回0（没有匹配）或1（匹配），可以用带文字串的REGEXP来测试，如：SELECT 'hello' REGEXP '[0-9]';

# 匹配以数字或小数点开头的所有产品
SELECT prod_name 
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

• 注意！！！：MySQL 8.0.4之前，使用MySQL使用Henry Spencer正则表达式库来支持正则表达式操作。但MySQL8.0.4只会，MySQL开始使用Unicode的国际组件（ICU）来支持正则表达式，这导致MySQL正则表达式支持语法有一些改变：
  • Spencer库支持单词开头和单词结尾边界标记（[[:<:]]和 [[:>:]]表示法）。ICU没有。对于ICU，可以使用\b来匹配单词边界。
  • 对于重复计数（{n}和 {m,n}计数），Spencer库的最大值为255。ICU没有这样的限制，尽管可以通过设置regexp_time_limit系统变量来限制匹配引擎步骤的最大数量 。
  • ICU将圆括号()和方括号[]都解释为元字符，如果需要匹配他们则需要将其转义