MySQL中LIKE?BINARY和LIKE模糊查詢實例代碼
1. Django ORM id__startswith 查詢變慢
問題
項目接口請求耗時，3-4s
原因
隨著數(shù)據(jù)庫表數(shù)據(jù)的增加，原先的 ORM 查詢速度變慢
解決
排查到 sql 查詢語句使用的模糊查詢是區(qū)分大小的，導(dǎo)致查詢效率變慢，模糊匹配的字符不存在大小寫敏感，修改模糊匹配為大小寫不敏感
優(yōu)化前
Django ORM DQL
# ORM 示例 ( startswith == like binary)
XXXmodel.objects.filter(
region_id__startswith=region_id[:3],
nic_id__in=nic_code_list
).values('nic_id').annotate(Sum('amount'))
mysql 原生 DQL
# LIKE BINARY 區(qū)分大小寫
SELECT `leader_vt_patent`.`nic_id`, SUM(`leader_vt_patent`.`amount`) AS `amount__sum` FROM `leader_vt_patent` WHERE (`leader_vt_patent`.`nic_id` IN ('C13', 'C14', 'C15', 'C16', 'C17', 'C18', 'C19', 'C20', 'C21', 'C22', 'C23', 'C24', 'C25', 'C26', 'C27', 'C28', 'C29', 'C30', 'C31', 'C32', 'C33', 'C34', 'C35', 'C36', 'C37', 'C38', 'C39', 'C40', 'C41', 'C42', 'C43') AND `leader_vt_patent`.`region_id` LIKE BINARY '110%') GROUP BY `leader_vt_patent`.`nic_id`, `leader_vt_patent`.`tyear` ORDER BY `leader_vt_patent`.`tyear` DESC;
耗時 1.16s
優(yōu)化后
Django ORM DQL
# ORM 示例 ( istartswith == like )
XXXmodel.objects.filter(
region_id__istartswith=region_id[:3],
nic_id__in=nic_code_list
).values('nic_id').annotate(Sum('amount'))
mysql 原生 DQL
# LIKE 不區(qū)分大小寫
SELECT `leader_vt_patent`.`nic_id`, SUM(`leader_vt_patent`.`amount`) AS `amount__sum` FROM `leader_vt_patent` WHERE (`leader_vt_patent`.`nic_id` IN ('C13', 'C14', 'C15', 'C16', 'C17', 'C18', 'C19', 'C20', 'C21', 'C22', 'C23', 'C24', 'C25', 'C26', 'C27', 'C28', 'C29', 'C30', 'C31', 'C32', 'C33', 'C34', 'C35', 'C36', 'C37', 'C38', 'C39', 'C40', 'C41', 'C42', 'C43') AND `leader_vt_patent`.`region_id` LIKE '110%') GROUP BY `leader_vt_patent`.`nic_id`, `leader_vt_patent`.`tyear` ORDER BY `leader_vt_patent`.`tyear` DESC;
耗時 0.27s
2. MySQL 模糊匹配
簡介
MySQL 模糊搜索：
LIKE BINARY 是區(qū)分大小寫的
LIKE 是否區(qū)分大小寫,取決于表的 COLLATE，如果 COLLATE=utf8_bin 即二進(jìn)制形式就是區(qū)分大小寫的, 否則是不區(qū)分的
ORM 寫法
xxx.objects.filter(namecontains=‘sxn’) 和 xxx.objects.filter(name__icontains=‘sxn’)
SQL 寫法
LIKE BINARY 和 LIKE
用法
LIKE 關(guān)鍵字支持百分號**“ %”** 和下劃線 “_” 通配符
通配符是一種特殊語句，主要用來模糊查詢。當(dāng)不知道真正字符或者懶得輸入完整名稱時，可以使用通配符來代替一個或多個真正的字符。
帶有 “%” 通配符的查詢
“%” 是 MySQL 中最常用的通配符，它能代表任何長度的字符串，字符串的長度可以為 0。例如，a%b 表示以字母 a 開頭，以字母 b 結(jié)尾的任意長度的字符串。該字符串可以代表 ab、acb、accb、accrb 等字符串。
例 1
在 tb_students_info 表中，查找所有以字母 “T” 開頭的學(xué)生姓名，SQL 語句和運行結(jié)果如下。
mysql> SELECT name FROM tb_students_info
-> WHERE name LIKE 'T%';
+--------+
| name |
+--------+
| Thomas |
| Tom |
+--------+
2 rows in set (0.12 sec)
可以看到，查詢結(jié)果中只返回了以字母 “T” 開頭的學(xué)生姓名。
注意：匹配的字符串必須加單引號或雙引號。
NOT LIKE 表示字符串不匹配時滿足條件。
例 2
在 tb_students_info 表中，查找所有不以字母 “T” 開頭的學(xué)生姓名，SQL 語句和運行結(jié)果如下。
mysql> SELECT NAME FROM tb_students_info
-> WHERE NAME NOT LIKE 'T%';
+-------+
| NAME |
+-------+
| Dany |
| Green |
| Henry |
| Jane |
| Jim |
| John |
| Lily |
| Susan |
+-------+
8 rows in set (0.00 sec)
可以看到，查詢結(jié)果中返回了不以字母 “T” 開頭的學(xué)生姓名。
例 3
在 tb_students_info 表中，查找所有包含字母 “e” 的學(xué)生姓名，SQL 語句和運行結(jié)果如下。
mysql> SELECT name FROM tb_students_info
-> WHERE name LIKE '%e%';
+-------+
| name |
+-------+
| Green |
| Henry |
| Jane |
+-------+
3 rows in set (0.00 sec)
可以看到，查詢結(jié)果中返回了所有包含字母 “e” 的學(xué)生姓名。
帶有“_”通配符的查詢
“_” 只能代表單個字符，字符的長度不能為 0。例如，a_b 可以代表 acb、adb、aub 等字符串。
例 1
在 tb_students_info 表中，查找所有以字母 “y” 結(jié)尾，且 “y” 前面只有 4 個字母的學(xué)生姓名，SQL 語句和運行結(jié)果如下。
mysql> SELECT name FROM tb_students_info
-> WHERE name LIKE '____y';
+-------+
| name |
+-------+
| Henry |
+-------+
1 row in set (0.00 sec)
LIKE 區(qū)分大小寫
默認(rèn)情況下，LIKE 關(guān)鍵字匹配字符的時候是不區(qū)分大小寫的。如果需要區(qū)分大小寫，可以加入 BINARY 關(guān)鍵字。
例 1
在 tb_students_info 表中，查找所有以字母 “t” 開頭的學(xué)生姓名，區(qū)分大小寫和不區(qū)分大小寫的 SQL 語句和運行結(jié)果如下。
mysql> SELECT name FROM tb_students_info WHERE name LIKE 't%'; +--------+ | name | +--------+ | Thomas | | Tom | +--------+ 2 rows in set (0.00 sec) mysql> SELECT name FROM tb_students_info WHERE name LIKE BINARY 't%'; Empty set (0.01 sec)
由結(jié)果可以看到，區(qū)分大小寫后，“Tom” 和 “Thomas” 等記錄就不會被匹配到了。
使用通配符的注意事項和技巧
- 注意大小寫
- MySQL 默認(rèn)是不區(qū)分大小寫的，如果區(qū)分大小寫，像 “Tom” 這樣的數(shù)據(jù)就不能被 “t%” 所匹配到。
- 注意尾部空格
- 尾部空格會干擾通配符的匹配，例如，**“T% ”**就不能匹配到 “Tom”。
- 注意 NULL
- “%” 通配符可以到匹配任意字符，但是不能匹配 NULL，也就是說 “%” 匹配不到 tb_students_info 數(shù)據(jù)表中值為 NULL 的記錄。
- 不要過度使用通配符，如果其它操作符能達(dá)到相同的目的，應(yīng)該使用其它操作符，因為 MySQL 對通配符的處理一般會比其他操作符花費更長的時間。
- 在確定使用通配符后，除非絕對有必要，否則不要把它們用在字符串的開始處，把通配符置于搜索模式的開始處，搜索起來是最慢的。
- 仔細(xì)注意通配符的位置，如果放錯地方，可能不會返回想要的數(shù)據(jù)。
3. 拓展
如果查詢內(nèi)容中包含通配符，可以使用 “\” 轉(zhuǎn)義符。
例如，在 tb_students_info 表中，將學(xué)生姓名 “Dany” 修改為 “Dany%” 后，查詢以 “%” 結(jié)尾的學(xué)生姓名，SQL 語句和運行結(jié)果如下：
mysql> SELECT NAME FROM test.`tb_students_info` WHERE NAME LIKE '%\%'; +-------+ | NAME | +-------+ | Dany% | +-------+ 1 row in set (0.00 sec)
總結(jié)
到此這篇關(guān)于MySQL中LIKE BINARY和LIKE模糊查詢的文章就介紹到這了,更多相關(guān)MySQL LIKE BINARY和LIKE模糊查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本文介紹了兩種mysql對自增id重新從1排序的方法，簡少了對于某個項目初始化數(shù)據(jù)的工作量，感興趣的朋友可以參考下2015-07-07
解決mysql問題:由于找不到MSVCR120.dll,無法繼續(xù)執(zhí)行代碼
這篇文章給大家分享解決mysql問題：由于找不到MSVCR120.dll,無法繼續(xù)執(zhí)行代碼.通過重新安裝程序解決此問題，下面小編把問題記錄及解決過程分享給大家，需要的朋友參考下吧2021-06-06
mySQL count多個表的數(shù)據(jù)實例詳解
這篇文章通過實例給大家介紹了mySQL中count多個表的數(shù)據(jù)，也就是多個表如何聯(lián)合查詢，文中通過項目中遇到的一個問題進(jìn)行分析和實現(xiàn)，給出了詳細(xì)的示例代碼，相信對大家的理解和學(xué)習(xí)很有幫助，有需要的朋友們下面來一起看看吧。2016-11-11
mysql使用Haversine 公式和ST_Distance_Sphere 函數(shù)實現(xiàn)附近的人功能
使用 MySQL 來實現(xiàn)“附近的人”功能，可以通過Haversine 公式和ST_Distance_Sphere 函數(shù)兩種方式來實現(xiàn)，這篇文章主要介紹了mysql實現(xiàn)附近的人功能,需要的朋友可以參考下2023-08-08
在下面的練習(xí)中，將創(chuàng)建一個應(yīng)用程序，它包含的一個方法能夠計算一名顧問的收費金額——假定該顧問每天收取固定的費用，將根據(jù)工作了多少天來收費。首先要開發(fā)應(yīng)用程序的邏輯，然后利用“生成方法存根向?qū)А眮韺懗鲞@個邏輯使用的方法。接著，我們將在一個控制臺應(yīng)用程序中運行方法，以獲得對該程序的最終印象。最后，我們將使用Visual Studio 2005調(diào)試器來檢查方法調(diào)用。2008-04-04
MySQL5.7發(fā)布后，專門設(shè)計了JSON數(shù)據(jù)類型以及關(guān)于這種類型的檢索以及其他函數(shù)解析。 我們先看看MySQL老版本的JSON存取方式，具體內(nèi)容詳情大家參考下本文吧2017-10-10
這篇文章主要為大家詳細(xì)介紹了MySQL中B樹索引和B+樹索引的區(qū)別，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助2022-03-03
NTILE()函數(shù)用于將分區(qū)中的有序數(shù)據(jù)分為n個等級,本文主要介紹了Mysql中NTILE()函數(shù)的具體使用,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-07-07
這篇文章主要介紹了mysql允許遠(yuǎn)程連接的方法，內(nèi)容很詳細(xì)，感興趣的小伙伴們可以參考一下2015-12-12
雖然我們也可以通過篩選條件WHERE和HAVING，或者是限定返回記錄的關(guān)鍵字LIMIT返回一條記錄，但是，卻無法在結(jié)果集中像指針一樣，向前定位一條記錄、向后定位一條記錄，或者是隨意定位到某一條記錄，并對記錄的數(shù)據(jù)進(jìn)行處理2022-12-12