分頁 + 模糊查詢有坑！

2022 年 9 月 14 日
筆記

前言

不知道你有沒有使用過Mysql的like語句，進行模糊查詢？

不知道你有沒有將查詢結果，進行分頁處理？

模糊查詢，加上分頁處理，會有意想不到的坑，不信我們繼續往下看。

我之前提供過一個品牌查詢接口，給前端品牌選擇控件使用的。

當時為了性能考慮，怕前端控件因為一次性加載太多的品牌，而導致頁面卡死。

因此，對品牌查詢接口做了分頁處理。

剛開始品牌表的數據比較少，沒有出現什麼問題。

後來，產品加需求了，在品牌下拉選擇控件中，讓用戶可以輸入自定義品牌。

在用戶添加品牌前，需要先查一下，如果該品牌存在，則使用已有品牌。如果不存在，則新增品牌。（這裡是精確匹配）

這個需求很簡單，很容易實現了。

後來，產品又加需求了，需要按名稱模糊查詢品牌。

該功能上線後，使用了很長一段時間，也沒啥問題。

突然，在不經意的某一天，這個功能卻出問題了。

到底怎麼回事呢？

1.案發現場

某一天下午，運營找到測試反饋一個問題說：明明品牌蘇三，已經存在了，但用戶輸入關鍵字：蘇三時，系統沒有讓用戶直接選擇已有品牌，而是添加了一個叫：蘇三的自定義的品牌。

我過去一看，還真的有問題。

不一會兒，就定位到原因了，初步判斷是分頁的問題。

搜索關鍵字：蘇三，竟然出現了好幾頁的數據，把我驚呆了，品牌表怎麼多了這麼多數據了。

我查了數據庫，其實數據量並不是特別多，但有些品牌名稱比較特殊，有些品牌名稱是多個品牌名稱拼接而成的，比如：蘇三,李四或者蘇三,李四,王五，這是一個品牌。

其實是品牌名稱建的不規範導致的問題，但已經沒法讓運營修改品牌了，只能通過技術手段解決目前的問題。

查詢第一頁的數據sql：

select * from brand where name like '%蘇三%' 
order by edit_date desc limit 5;

執行結果：

我們可以看到，圖中並沒有等於蘇三這兩個字的數據。

注意：為了好演示，這裡給的每頁大小是5，真實的場景並非如此。

查詢第二頁的數據sql：

select * from brand where name like '%蘇三%' 
order by edit_date desc limit 5,5;

執行結果：

從圖中看到，在第二行，出現了正好等於蘇三這兩個字的數據。

用戶搜索關鍵字：蘇三時，前端頁面在調用品牌查詢接口，pageNo默認是1。由於能夠匹配關鍵字的數據太多了，第一頁返回不完，需要多頁才能全部返回。

前端獲取到第一頁的數據後，跟關鍵字：蘇三做比較，發現沒有等於蘇三的品牌。

這樣就會在下拉控件中，自動添加一個品牌：蘇三，同時在右邊增加自定義標籤。

這樣就出問題了，明明蘇三這個品牌是有的，但用戶還能自定義一個蘇三，而不是直接選擇。

2.思考123

蘇三這個關鍵字，通過模糊查詢可以查詢出來，但由於品牌接口做了分頁，全匹配的品牌：蘇三，出現在第二頁了，才導致問題的產生。

如果要解決這個問題，讓它出現在第一頁不就OK了？

這時候，就有下面幾種解決方案。

2.1 方案1

分頁查詢品牌接口，pageSize是5。

我們為什麼不把pageSize調大一點？比如改成：200、500等。

這樣通過蘇三關鍵字，進行模糊查詢的時候，結果基本都在第一頁。

這樣就能非常快速的解決問題。

但有個缺點就是：如果這次調大了pageSize，但後面查詢關鍵字的品牌又出現在第二頁怎麼辦？

不可能一直改pageSize吧？

2.2 方案2

把分頁查詢接口的數據，拆分成兩部分：

精確查詢
模糊查詢

在代碼中做處理的時候，先根據關鍵字精確查詢，即sql中使用name='蘇三'，這種方式查詢一次數據。

如果沒查出數據，則再直接用like '%蘇三'進行模糊查詢。

如果查出了一條數據，則把它放在返回結果集合中的第一位置。接下來，使用like '%蘇三'進行模糊查詢的時候，再加上條件 name <> '蘇三'。將查出的結果，從第二個位置往後放。

這樣可以拼接出你想要的集合。

但有個缺點，就是代碼耦合性太大了。

2.3 方案3

之前，品牌蘇三在第二頁，最根本的原因是使用了edit_time字段進行逆序的。

也就是說，修改時間越大的越排在前面，而品牌蘇三的修改時間很小，所以排在第二頁了。

如果想品牌蘇三，排在第一頁，修改一下排序規則，不就搞定了？

可以改成按：id或者name字段排序。

用id字段排序，不太合適，雖說用了雪花算法，但跟修改時間類似，先插入的數據，會越小。

select * from brand where name like '%蘇三%' 
order by id desc limit 5;

用它排序的結果，跟使用修改時間排序差不了太多。

看來，只能使用name字段排序了。

3.如何排序？

我們在sql中直接對name字段，進行升序或者降序嗎？

顯然不是。

使用name字段降序：

select * from brand where name like '%蘇三%' 
order by name desc limit 5;

執行結果：

圖中並沒有看到我們想要的數據。

其實，使用name字段升序，也可能在第一頁查不出我們所想要的數據。

到底該如何處理呢？

假如，我們有這樣一種排序：

全匹配顯示在最前面，比如：蘇三。
數據左半部分匹配，右邊按字母排序，比如：蘇三1、蘇三2、蘇三說技術。
從中間開始匹配，比如：1蘇三、2蘇三。
第2步和第3步，還要根據字符長度排序，字符短的排在前面，比如：1蘇三、1蘇三1、蘇三說技術。

如果我們能實現上面的這種排序方式，這個問題就能完美解決了。

說起來容易，做起來難。

難道要先全匹配：name='蘇三'，再有匹配：name like '蘇三%'，再左匹配：name like '%蘇三'，把查詢三次的結果組裝起來？

顯然這種做法有點low。

要實現上面我們設想的排序方式，在es中更好處理一下，但在mysql中要怎麼處理呢？

4.解決方案

其實，我們可以換一種思路，根據字符的長度排序。

mysql給我們提供了很多非常有用的函數，比如：char_length。

通過該函數就能獲取字符長度。

sql調整如下：

select * from brand where name like '%蘇三%' 
order by char_length(name) asc limit 5;

name字段使用關鍵字模糊查詢之後，再使用char_length函數，獲取name字段的字符長度，然後按長度升序。

僅這一個騷操作，就搞定需求了：

我們所期待的：蘇三，終於排在第一個了。同時由於該sql做了分頁的，即使name字段在查詢時丟失了索引，執行效率也不會太低。

業務上的需求搞定了。

但追求完美的我們，好奇，想看看第二頁是什麼情況：

select * from brand where name like '%蘇三%' 
order by char_length(name) asc limit 5,5;

執行結果：

並沒有按照我們設想的劇本進行下去，我們之前假設的3條排序中，第2條和第3條都沒有滿足。

這時該怎麼辦？

答：可以使用mysql中的locate函數，通過它可以匹配的關鍵字，在字符串中的位置。

使用locate函數改造之後sql如下：

select * from brand where name like '%蘇三%' 
order by char_length(name) asc, locate('蘇三',name) asc limit 5,5;

執行結果：

完美，終於出現我們想要的結果了。

除此之外，還可以使用：instr和position函數，它們的功能跟locate函數類似，在這裡我就不一一介紹了，感興趣的小夥伴可以找我私聊。

5. 總結

其實，模糊查詢和分頁，如果分開用，一般是沒問題的。

但如果它們要一起使用，一定要考慮排序問題。

如果只是按照簡單的時間或者id排序，有些特殊的業務場景，沒辦法滿足，很容易出現bug。

當然解決上面問題，還有其他辦法，比如：pageSize調大一點，或者把全匹配放到第一頁。

但更優的方案，是通過mysql的函數來解決問題。

我們可以通過mysql提供的：char_length、locate、instr和position函數等，來實現很多複雜的排序功能。

最後說一句(求關注，別白嫖我)

如果這篇文章對您有所幫助，或者有所啟發的話，幫忙掃描下發二維碼關注一下，您的支持是我堅持寫作最大的動力。
求一鍵三連：點贊、轉發、在看。
關注公眾號：【蘇三說技術】，在公眾號中回復：面試、代碼神器、開發手冊、時間管理有超贊的粉絲福利，另外回復：加群，可以跟很多BAT大廠的前輩交流和學習。