分頁 + 模糊查詢 有坑！你知道嗎？
前言
不知道你有沒有使用過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了？
這時候，就有下面幾種解決方案。
（1）方案1
分頁查詢品牌接口，pageSize是5。
我們為什么不把pageSize調大一點？比如改成：200、500等。
這樣通過蘇三關鍵字，進行模糊查詢的時候，結果基本都在第一頁。
這樣就能非常快速的解決問題。
但有個缺點就是：如果這次調大了pageSize，但后面查詢關鍵字的品牌又出現在第二頁怎么辦？
不可能一直改pageSize吧？
（2）方案2
把分頁查詢接口的數據，拆分成兩部分：
- 精確查詢
- 模糊查詢
在代碼中做處理的時候，先根據關鍵字精確查詢，即sql中使用name='蘇三'，這種方式查詢一次數據。
如果沒查出數據，則再直接用like '%蘇三'進行模糊查詢。
如果查出了一條數據，則把它放在返回結果集合中的第一位置。接下來，使用like '%蘇三'進行模糊查詢的時候，再加上條件 name <> '蘇三'。將查出的結果，從第二個位置往后放。
這樣可以拼接出你想要的集合。
但有個缺點，就是代碼耦合性太大了。
（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函數等，來實現很多復雜的排序功能。
