MySQL 过滤与匹配操作

一、过滤

1、WHERE 语句

MySQL 中的 WHERE 子句可以用于指定一个查询条件，只有满足条件的数据才会返回。在 SQL 定义中，WHERE 子句也被称为谓词（predicate），指定 WHERE 子句的语法如下：

SELECT
    col1,
    col2,
    ...
FROM
    table_name 
WHERE
    conditions;

其中，conditions 是一个逻辑表达式，它的结果可能为：

• TRUE（1）
• FALSE（0）
• UNKNOWN（NULL）

对于表中的数据行，只有表达式为 TRUE 的才会返回。例如，以下查询只返回年龄大于 30 的员工姓名：

SELECT
    emp_name,
    age 
FROM
    employee 
WHERE
    age > 30;


| emp_name | age |
| -------- | --- |
| Jobs     | 50  |
| Cook     | 40  |

以上示例中的大于号（>）是一个比较运算符，用于判断 age 是否大于 30。常用的比较运算符如下：

• > 大于
• >= 大于等于
• = 等于
• <= 小于等于
• < 小于
• <> 不等于

另外，BETWEEN 运算符可以用于判断数据是否位于某个范围之内。例如，以下查询返回 2022 年入职的员工：

SELECT
    emp_name,
    hire_date 
FROM
    employee 
WHERE
    hire_date BETWEEN '2022-01-01' AND '2022-12-31';


| emp_name | hire_date  |
| -------- | ---------- |
| Jobs     | 2022-08-10 |
| Cook     | 2022-09-10 |

除此之外，IN 运算符可以用于判断数据是否位于某个列表之中。例如，以下查询返回了编号为 1、2 的员工：

SELECT
    emp_name,
    hire_date 
FROM
    employee 
WHERE
    emp_id IN ( 1, 2 );


| emp_name | hire_date  |
| -------- | ---------- |
| Jobs     | 2022-08-10 |
| Cook     | 2022-09-10 |

只要数据和 IN 列表中的任意值相等，就表示满足条件，除了直接给出列表之外，还可以使用子查询返回一个结果集。

2、NULL 空值判断

在数据库中，空值（NULL）表示缺失或者未知的数据，它不等于 0 或空字符串。对于空值的判断，不能使用普通的等于或不等于，需要使用特殊的 IS NULL 和 IS NOT NULL 运算符。

以下查询返回了没有上级领导的员工：

SELECT
    emp_name,
    manager 
FROM
    employee 
WHERE
    manager IS NULL;


| emp_name | manager |
| -------- | ------- |
| Jobs     |         |

如果想查询存在上级领导的员工，可以使用 IS NOT NULL 运算符：

SELECT
    emp_name,
    manager 
FROM
    employee 
WHERE
    manager IS NOT NULL;

除了 IS NOT NULL 运算符外，MySQL 还提供了一个空值判断的函数：isnull(expr)。如果 expr 为空值，该函数返回 1；否则，返回 0。例如：

SELECT
    isnull( 0 ),
    isnull( NULL );


| isnull(0) | isnull(null) |
| --------- | ------------ |
| 0         | 1            |

另外，MySQL 还提供了一个支持 NULL 值的比较运算符：<=>。例如：

SELECT
    0 <=> 0,
    NULL <=> NULL,
    0 <=> NULL;


| 0 <=> 0 | NULL <=> NULL | 0 <=> NULL |
| ------- | ------------- | ---------- |
| 1       | 1             | 0          |

对于非空的数据，<=> 相当于普通的 = 运算符；对于两个 NULL 值，返回 1；对于一个 NULL 值，返回 0。

MySQL 中的 <=> 运算符等价于 SQL 标准中的 IS NOT DISTINCT FROM 运算符。

3、复合条件

除了使用单个查询条件之外，MySQL 还支持利用逻辑运算符将多个查询条件进行组合，常用的逻辑运算符如下：

• AND：逻辑与
  • 当两个表达式的值都为真时结果才为真；
• OR：逻辑或
  • 只要有一个表达式的值为真结果就为真；
• NOT：逻辑非
  • 如果表达式的值为真，结果为假；
  • 如果表达式的值为假，结果为真；
  • 如果表达式的值为 NULL，结果为 NOT NULL；
• XOR：逻辑异或
  • 只要有一个表达式的值为 NULL，结果就为 NULL；
  • 如果一个表达式的值为假，另一个表达式为真，结果就为真；否则结果为假。

举个例子：

以下查询返回了 2022 年之后入职的女性员工：

SELECT
    emp_name,
    sex,
    hire_date 
FROM
    employee 
WHERE
    sex = '女' AND hire_date >= '2022-01-01';


| emp_name | sex | hire_date  |
| -------- | --- | ---------- |
| Mercedes | 女  | 2022-11-10 |

以下查询返回了所有女性员工以及 2022 年之后入职的员工：

SELECT
    emp_name,
    sex,
    hire_date 
FROM
    employee 
WHERE
    sex = '女' OR hire_date >= '2022-01-01';


| emp_name | sex | hire_date  |
| -------- | --- | ---------- |
| Aercedes | 女  | 2012-08-08 |
| Bercedes | 女  | 2012-08-08 |
| Jobs     | 男  | 2022-08-10 |
| Mercedes | 女  | 2022-11-10 |
| Cook     | 男  | 2022-09-10 |

以下查询返回了不是 2012 年入职的员工：

SELECT
    emp_name,
    hire_date 
FROM
    employee 
WHERE
    hire_date NOT BETWEEN '2012-01-01' AND '2012-12-31';


| emp_name | sex | hire_date  |
| -------- | --- | ---------- |
| Jobs     | 男  | 2022-08-10 |
| Mercedes | 女  | 2022-11-10 |
| Cook     | 男  | 2022-09-10 |

另外，MySQL 逻辑运算符可以使用短路运算（short-circuit）。只要左边的表达式可以决定最终的结果，就不会计算右边的表达式。例如：

SELECT 1 = 0 AND 1 / 0;

| 1 = 0 and 1 / 0 |
| --------------- |
| 0               |

因为 1 = 0 的结果为 False，AND 运算符的结果肯定就是 False；所以不会计算 1 / 0，也就不会返回除零的错误。

4、排除重复值

DISTINCT 是一个特殊的运算符，可以排除查询结果中的重复记录：

SELECT
    [ ALL | DISTINCT ] col1,
    col2,
    ...
FROM
    table_name;

• ALL：默认选项，表示返回全部结果；
• DISTINCT：表示返回字段组合结果中的不同值。

举个例子，默认 ALL 查询：

SELECT
    sex 
FROM
    employee;


| sex |
| --- |
| 男  |
| 男  |
| 男  |
| 女  |
| 女  |
| 女  |

使用 DISTINCT 去重查询：

SELECT DISTINCT
    sex 
FROM
    employee;


| sex |
| --- |
| 男  |
| 女  |

对于 DISTINCT 而言，所有的 NULL 值都相同。

二、匹配

1、LIKE 运算符

MySQL 中的 LIKE 运算符可用于判断字符串是否包含某个模式，返回 1（True）或者 0（False）。根据 SQL 标准，MySQL 支持两个通配符：

• %：匹配零个或多个任意字符；
• _：匹配一个任意字符。

例如，以下语句用于查询“关”姓员工：

SELECT
    emp_name 
FROM
    employee 
WHERE
    emp_name LIKE '关%';


| emp_name |
| -------- |
| 关兴     |
| 关平     |
| 关羽     |

其中，关% 表示以“关”字开始的字符串。另外，%xyz% 表示包含 xyz 的字符串；%xyz 表示以 xyz 结束的字符串。

下面用 _ 通配符查询名为“关XX”的员工：

SELECT
    emp_name 
FROM
    employee 
WHERE
    emp_name LIKE '关__';


| emp_name |
| -------- |
| 关云长   |
| 关二爷   |

1-1、转义字符的使用

由于百分号 % 和下划线 _ 是通配符，具有特殊的意义。当要判断字符串中是否包含这两个字符时，例如“50%”，需要用转义字符 \ 将模式中的通配符解释为普通字符。例如：

使用前：

SELECT
    '完成进度：50% 已完成。' LIKE '%50%%' AS like1,
    '日期 20150101' LIKE '%50%%' AS like2;


| like1 | like2 |
| ----- | ----- |
| 1     | 1     |

-- 这里没有使用转义字符，会直接使用 50 进行匹配，因此 20150101 也满足条件

使用后：

SELECT
    '完成进度：50% 已完成。' LIKE '%50\%%' AS like1,
    '日期 20150101' LIKE '%50\%%' AS like2;


| like1 | like2 |
| ----- | ----- |
| 1     | 0     |

除了使用 \，还可以使用 # 作为转义字符。

1-2、REGEXP_LIKE 区分大小写

MySQL 中 LIKE 运算符不区分大小写，如果要实现区分大小写的匹配，可以使用 REGEXP_LIKE 函数。

2、MySQL 正则表达式匹配

MySQL 提供以下三种的正则表达式匹配函数和运算符：

REGEXP_LIKE(expr, pat[, match_type])
expr REGEXP pat
expr RLIKE pat

如果字符串 expr 匹配模式 pat 指定的正则表达式，返回 True（1），否则返回 False（0）。如果 expr 或者 pat 为 NULL，返回 NULL。

其中可选参数 match_type 可以用于指定匹配方式，可以是以下选项之一或全部：

• c：区分大小写；
• i：不区分大小写（默认）；
• m：多行匹配，可识别字符串内部的行终止符，默认情况下只在字符串的开始或结束匹配行终止符；
• n：点号 . 匹配行终止符，默认情况下，点号遇到一行的结束时会终止匹配；
• u：只匹配 Unix 行终止符，此时只有换行符被 .、^ 和 $ 看作一行的结束。

REGEXP 和 RLIKE 运算符不支持 match_type 匹配选项。

以区分大小写的匹配为例：

SELECT
    REGEXP_LIKE ( 'Tony', 'tony' ) AS regexp1,      -- 不区分大小写
    REGEXP_LIKE ( 'Tony', 'tony', 'c' ) AS regexp2; -- 区分大小写


| regexp1 | regexp2 |
| ------- | ------- |
| 1       | 0       |

MySQL 支持的正则表达式元字符有：

2-1、^ 匹配字符串的开始

SELECT REGEXP_LIKE('fo\nfo', '^fo$');                   -> 0
SELECT REGEXP_LIKE('fofo', '^fo');                      -> 1

2-2、$ 匹配字符串的结束

SELECT REGEXP_LIKE('fo\no', '^fo\no$');                 -> 1
SELECT REGEXP_LIKE('fo\no', '^fo$');                    -> 0

2-3、. 匹配任意单个字符

任何字符包括回车符和换行符：

SELECT REGEXP_LIKE('fofo', '^f.*$');                    -> 1
SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$');                -> 0
SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$', 'm');           -> 1
SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.*$');            -> 1

2-4、a* 匹配零次或多次字符 a

SELECT REGEXP_LIKE('Ban', '^Ba*n');                     -> 1
SELECT REGEXP_LIKE('Baaan', '^Ba*n');                   -> 1
SELECT REGEXP_LIKE('Bn', '^Ba*n');                      -> 1

2-5、a+ 匹配一次或多次字符 a

SELECT REGEXP_LIKE('Ban', '^Ba+n');                     -> 1
SELECT REGEXP_LIKE('Bn', '^Ba+n');                      -> 0

2-6、a? 匹配零次或一次字符 a

SELECT REGEXP_LIKE('Bn', '^Ba?n');                      -> 1
SELECT REGEXP_LIKE('Ban', '^Ba?n');                     -> 1
SELECT REGEXP_LIKE('Baan', '^Ba?n');                    -> 0

2-7、a|b 匹配 a 或 b

SELECT REGEXP_LIKE('pi', 'pi|apa');                     -> 1
SELECT REGEXP_LIKE('axe', 'pi|apa');                    -> 0
SELECT REGEXP_LIKE('apa', 'pi|apa');                    -> 1
SELECT REGEXP_LIKE('apa', '^(pi|apa)$');                -> 1
SELECT REGEXP_LIKE('pi', '^(pi|apa)$');                 -> 1
SELECT REGEXP_LIKE('pix', '^(pi|apa)$');                -> 0

2-8、a{n} 匹配 n 次字符 a

SELECT REGEXP_LIKE('Ban', '^Ba{2}n');                   -> 0
SELECT REGEXP_LIKE('Baan', '^Ba{2}n');                  -> 1

2-9、a{m,n} 匹配 m 次到 n 次字符 a

SELECT REGEXP_LIKE('Ban', '^Ba{0,1}n');                 -> 1
SELECT REGEXP_LIKE('Ban', '^Ba{2,3}n');                 -> 0
SELECT REGEXP_LIKE('Baan', '^Ba{2,3}n');                -> 1

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

2-10、[abc] 匹配 a、b、c 中任意字符

SELECT REGEXP_LIKE('abcde', 'a[bcd]{2}e');              -> 0
SELECT REGEXP_LIKE('abcde', 'a[bcd]{3}e');              -> 1
SELECT REGEXP_LIKE('abcde', 'a[bcd]{1,10}e');           -> 1

支持使用连字符 - 指定一个范围：

SELECT REGEXP_LIKE('aXbc', '[a-dXYZ]');                 -> 1

2-11、[^abc] 匹配非 a、b、c 的任意字符

SELECT REGEXP_LIKE('abcd', '[^a-dXYZ]+');               -> 0
SELECT REGEXP_LIKE('ghei', '[^a-dXYZ]+');               -> 1

2-12、[:character_class:] 匹配属于该类

可用的字符类如下：

字符类名称	意义
alnum	字母数字字符
alpha	字母字符
blank	空白字符
cntrl	控制字符
digit	数字字符
graph	图形字符
lower	小写字母字符
print	图形或空格字符
punct	标点符号
space	空格、制表符、换行符和回车
upper	大写字母字符
xdigit	十六进制数字字符

使用如下：

SELECT REGEXP_LIKE('abc', '[:alpha:]+');                -> 1
SELECT REGEXP_LIKE('123', '[:alpha:]+');                -> 0
SELECT REGEXP_LIKE('abc', '[:digit:]+');                -> 0
SELECT REGEXP_LIKE('123', '[:digit:]+');                -> 1
SELECT REGEXP_LIKE('abc123', '[:alnum:]+');             -> 1

2-13、应用场景

常见的邮箱地址合法性验证，可以采用以下简单的规则：

• 以字母或者数字开头；
• 后面是一个或者多个字母、数组或特殊字符 . _ -；
• 然后是一个 @ 字符；
• 之后包含一个或者多个字母、数组或特殊字符 . -；
• 最后是域名，即 . 以及 2 到 4 个字母。

使用正则表达式可以表示为：

^[a-zA-Z0-9]+[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$

其中：

• ^ 匹配字符串的开头；
• [a-zA-Z0-9\] 匹配大小写字母或数字；
• + 表示匹配前面的内容一次或多次；
• . 匹配任何一个字符，\. 匹配点号自身；
• {2,4} 匹配前面的内容 2 次到 4次；
• $ 匹配字符串的结束。

创建一个测试表如下：

CREATE TABLE t_regexp (
  email VARCHAR(50)
);

INSERT INTO t_regexp VALUES ('TEST@leophen.com');
INSERT INTO t_regexp VALUES ('test@leophen');
INSERT INTO t_regexp VALUES ('.123@leophen.com');
INSERT INTO t_regexp VALUES ('test+email@leophen.cn');
INSERT INTO t_regexp VALUES ('me.me@leophen.com');
INSERT INTO t_regexp VALUES ('123.test@leophen-xx.org');

使用以下语句查找合法的邮箱地址：

SELECT
    email 
FROM
    t_regexp 
WHERE
    REGEXP_LIKE ( email, '^[a-zA-Z0-9]+[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,4}$' );

| email                   |
| ----------------------- |
| TEST@leophen.com        |
| 123.test@leophen-xx.org |

查询返回了两个合法的邮箱地址。注意其中的转义字符需要使用两个反斜线 \\，因为 MySQL 解析器会解析一个反斜线，正则表达式会解析另一个。