Like Share Discussion Bookmark Smile

J.J. Huang   2020-06-15   MySQL   瀏覽次數:

MySQL - 第八章 | 萬用字元進行過濾資料

LIKE操作符

不管是匹配一個還是多個值,測試大於還是小於已知值,或者檢查某個範圍的值,共同點是過濾中使用的值都是已知的。但是,這種過濾方法並不是任何時候都好用。例如,怎樣搜索產品名中包含文本short的所有產品?用簡單的比較操作符肯定不行,必須使用萬用字元。利用萬用字元可建立比較特定資料的搜索模式。在這個例子中,如果你想找出名稱包含short的所有產品,可構造一個萬用字元搜索模式,找出產品名中任何位置出現short的產品。

術語:萬用字元(wildcard) 用來匹配值的一部分的特殊字符。

術語:搜索模式(search pattern)1 由字面值、萬用字元或兩者組合構成的搜索條件。

百分號(%)萬用字元

最常使用的萬用字元是百分號(%)。在搜索串中,%表示任何字符出現任意次數。

1
2
3
4
5
6
7
8
mysql> select * from products where prod_name like '%short%';
+----+---------+-------+--------------------+---------------------+
| id | vend_id | price | prod_name | create_time |
+----+---------+-------+--------------------+---------------------+
| 16 | 1002 | 234 | short_sleeve_shirt | 2020-06-13 04:53:07 |
| 18 | 1006 | 877 | shorts | 2020-06-13 04:53:07 |
+----+---------+-------+--------------------+---------------------+
2 rows in set (0.00 sec)

此例子使用了搜索模式’%short%’。在執行這條子句時,將檢索任意文本中含有short的詞。%告訴MySQL接受含有short的任意字符,不 管它有多少字符。

如果要搜尋開頭為short的,不管後面的部分則使用’short%’,反之要找結尾為short的,使用’%short’。

說明:區分大小寫 根據MySQL的配置方式,搜索可以是區分大小寫的。如果區分大小寫,’Short%’與short_sleeve_shirt和shorts將不匹配。

另外萬用字元也可以出現在搜索模式的中間,雖然這樣做不太有用。下面的例子找出以s起頭以r結尾。

1
2
3
4
5
6
7
8
mysql> select * from products where prod_name like 's%r';
+----+---------+-------+-----------+---------------------+
| id | vend_id | price | prod_name | create_time |
+----+---------+-------+-----------+---------------------+
| 3 | 1002 | 111 | sleepwear | 2020-06-13 04:53:07 |
| 11 | 1001 | 9999 | sweater | 2020-06-13 04:53:07 |
+----+---------+-------+-----------+---------------------+
2 rows in set (0.00 sec)

重要的是要注意到,除了一個或多個字符外,%還能匹配0個字符。 % 代表搜索模式中給定位置的0個、1個或多個字符。

說明:注意尾空格 尾空格可能會干擾萬用字元匹配。例如,在保存詞shorts時,如果它後面有一個或多個空格,則子句WHERE prod_name LIKE ‘%shorts’將不會匹配它們,因為在最後的s後有多餘的字符。解決這個問題的一個簡單的辦法是在搜索模式最後附加一個%。一個更好的辦法是使用函數去掉首尾空格,後面會再提到函數部分。

注意:注意NULL 雖然似乎%萬用字元可以匹配任何東西,但有一個例外,即NULL。即使是WHERE prod_name LIKE ‘%’也不能匹配用值NULL作為產品名的行。

底線(_)萬用字元

另一個有用的萬用字元是底線(_)。底線的用途與%一樣,但底線只匹配單個字符而不是多個字符。

在開始之前,我們先挑整一下products這張表的內容,這個範例比較好呈現出來。

1
2
3
UPDATE `testdb`.`products` SET `prod_name` = '.3 tie' WHERE `id` = '19';
UPDATE `testdb`.`products` SET `prod_name` = '2 tie' WHERE `id` = '12';
UPDATE `testdb`.`products` SET `prod_name` = '1 tie' WHERE `id` = '1';
1
2
3
4
5
6
7
8
mysql> select * from products where prod_name like '_ tie';
+----+---------+-------+-----------+---------------------+
| id | vend_id | price | prod_name | create_time |
+----+---------+-------+-----------+---------------------+
| 1 | 1001 | 100 | 1 tie | 2020-06-13 04:53:07 |
| 12 | 1006 | 345 | 2 tie | 2020-06-13 04:53:07 |
+----+---------+-------+-----------+---------------------+
2 rows in set (0.00 sec)

分析:此WHERE子句中的搜索模式給出了後面跟有文本的兩個萬用字元。結果只顯示匹配搜索模式的行:第一行中底線匹配1,第二行中匹配2。.3 tie產品沒有匹配,因為搜索模式要求匹配兩個萬用字元而不是一個。

對照一下SELECT語句使用%萬用字元:

1
2
3
4
5
6
7
8
9
mysql> select * from products where prod_name like '% tie';
+----+---------+-------+-----------+---------------------+
| id | vend_id | price | prod_name | create_time |
+----+---------+-------+-----------+---------------------+
| 1 | 1001 | 100 | 1 tie | 2020-06-13 04:53:07 |
| 12 | 1006 | 345 | 2 tie | 2020-06-13 04:53:07 |
| 19 | 1001 | 236 | .3 tie | 2020-06-13 04:53:07 |
+----+---------+-------+-----------+---------------------+
3 rows in set (0.00 sec)

與%能匹配0個字符不一樣,_總是匹配一個字符,不能多也不能少。

使用萬用字元的技巧

MySQL的萬用字元很有用。但這種功能是有代價的:萬用字元搜索的處理一般要比前面討論的其他搜索所花時間更長

這裡給出一些使用萬用字元要記住的技巧:

  • 不要過度使用萬用字元。如果其他操作符能達到相同的目的,應該使用其他操作符。
  • 在確實需要使用萬用字元時,除非絕對有必要,否則不要把它們用在搜索模式的開始處。把萬用字元置於搜索模式的開始處,搜索起來是最慢的。
  • 仔細注意萬用字元的位置。如果放錯地方,可能不會返回想要的資料。

總之,萬用字元是一種極重要和有用的搜索工具,以後我們經常會用到它。

結語

這邊真的要細細觀看,我知道使用LIKE來做搜尋是比較慢的;這邊幾幾個重點我都也粗體顯現了,這邊特別在點出來。

萬用字元搜索的處理一般要比前面討論的其他搜索所花時間更長
把萬用字元置於搜索模式的開始處,搜索起來是最慢的。

另外這邊提到使用底線來做萬用字元;老實說我用的次數可以說為零,這邊有提到,我才知道,原來可以使用底線。

註:以上參考了
MySQL必知必会 MySQL Crash Course