#### 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).
- 簡介
- 前言和法律條款
- 安裝和更新 MySQL
- 在 Linux 上安裝 MySQL
- 在 Linux 上使用 APT 庫安裝 MySQL
- 在 Linux 上使用 Docker 部署 MySQL
- 使用 Docker 部署 MySQL 服務器的基本步驟
- 使用 Docker 部署 MySQL 服務器的更多主題
- 教程
- 連接到服務器和從服務器斷開
- 輸入查詢
- 創建和使用數據庫
- 創建和選擇數據庫
- 創建表
- 將數據加載到表中
- 從表中檢索數據
- 選擇所有數據
- 選擇特定行
- 選擇指定列
- 行排序
- 日期計算
- 處理 NULL 值
- 模式匹配
- 計算行數
- 使用多個表
- 獲取數據庫和表的信息
- 在批處理模式使用 mysql
- 常見查詢示例
- 列的最大值
- 包含某一行最大值的記錄
- 每組中列的最大值
- 擁有某個字段的組間最大值的行
- 使用用戶自定義變量
- 使用外鍵
- 兩個鍵上搜索
- 計算每日訪問量
- 使用 AUTO_INCREMENT
- 在 Apache 中使用 MySQL
- MySQL 程序
- MySQL 客戶端程序
- mysql — MySQL 命令行客戶端
- 優化
- 優化概述
- 優化 SQL 語句
- 優化和索引
- 優化數據庫結構
- 優化 InnoDB 表
- 優化 MyISAM 表
- 優化 MEMORY 表
- 理解查詢執行計劃
- 控制查詢優化器
- 緩沖和緩存
- 優化鎖操作
- 優化 MySQL 服務器
- 測量性能 (Benchmarking)
- 檢查線程信息