#### 3.3.4.7 模式匹配 MySQL 提供了标准的 SQL 模式匹配, 以及一种基于扩展正则表达式的模式匹配形式, 类似于 Unix 的工具, 如 `vi`, `grep`, 和 `sed`. SQL 模式匹配允许使用 `_` 匹配任何单个字符, `%` 匹配任意数量的字符(包含零个字符). 在 MySQL 中, SQL 匹配默认不区分大小写. 这里展示了一些示例. 当你使用 SQL 模式时不要使用 `=` 或者 `<>`. 而是使用 [`LIKE`](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like) 或者 [`NOT LIKE`](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_not-like) 比较运算符. 找出 `b`开头的 name: ```sql mysql> SELECT * FROM pet WHERE name LIKE 'b%'; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ ``` 找出 `fy` 结尾的 name: ```sql mysql> SELECT * FROM pet WHERE name LIKE '%fy'; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ ``` 找出包含 `w` 的 name: ```sql mysql> SELECT * FROM pet WHERE name LIKE '%w%'; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ ``` 找出恰好包含五个字符的 name, 使用五个 `_` 匹配字符集的实例: ```sql mysql> SELECT * FROM pet WHERE name LIKE '_____'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ ``` MySQL 提供的另一种模式匹配是使用扩展正则表达式. 当你测试这种类型模式的匹配时, 使用 [`REGEXP_LIKE()`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like) 函数 (或者 [`REGEXP`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp) 或者 [`RLIKE`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp) 操作符, 它们是 [`REGEXP_LIKE()`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like) 的同义词). 下列列表描述了正则表达式的一些特性: - `.` 匹配任何单个字符. - 字符类 `[...]` 匹配括号内的任何字符. 例如, `[abc]` 匹配 a, b, 或者 c. 要命名一个字符范围, 请使用破折号. `[a-z]` 匹配任何字母, `[0-9]` 匹配任何数字. - `*` 匹配前一项的零个或者多个实例. 例如, `x*` 匹配任意数量的 `x` 字符, `[0-9]*` 匹配任意数量的数字, `.*` 匹配任意数量的任何字符. - 如果正则表达式模式匹配正在测试的值中的任何位置, 则该模式匹配成功. (这与 [`LIKE`](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like) 模式匹配不同, 如果模式匹配真个值时才会成功.) - 如果要匹配被测试值的开头或者结束, 请在模式的开始添加 `^` 或者在结尾添加 `$`. 为了演示扩展正则表达式是如何工作的, 这里使用 [`REGEXP_LIKE()`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like) 重写了前面的 [`LIKE`](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like) 查询. 找出以 `b` 开头的 name, 使用 `^` 匹配 name 的开头: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b'); +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ ``` To force a regular expression comparison to be case sensitive, use a case-sensitive collation, or use the [`BINARY`](https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#operator_binary) keyword to make one of the strings a binary string, or specify the `c` match-control character. Each of these queries matches only lowercase b at the beginning of a name: ```sql SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs); SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b'); SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c'); ``` To find names ending with `fy`, use `$` to match the end of the name: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$'); +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ ``` To find names containing a `w`, use this query: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w'); +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ ``` Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard on either side of the pattern to get it to match the entire value as would be true with an SQL pattern. To find names containing exactly five characters, use `^` and `$` to match the beginning and end of the name, and five instances of `.` in between: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$'); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ ``` You could also write the previous query using the `{n}` (“repeat-`n`-times”) operator: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$'); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ ```` For more information about the syntax for regular expressions, see [Section 12.5.2, “Regular Expressions”](https://dev.mysql.com/doc/refman/8.0/en/regexp.html).