分頁 + 模糊查詢 有坑！
前言
不知道你有沒有使用過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大廠的前輩交流和學習。