分頁 + 模糊查詢 有坑！你知道嗎？
前言
不知道你有沒有使用過Mysql的like語句，進(jìn)行模糊查詢？
不知道你有沒有將查詢結(jié)果，進(jìn)行分頁處理？
模糊查詢，加上分頁處理，會(huì)有意想不到的坑，不信我們繼續(xù)往下看。
我之前提供過一個(gè)品牌查詢接口，給前端品牌選擇控件使用的。
當(dāng)時(shí)為了性能考慮，怕前端控件因?yàn)橐淮涡约虞d太多的品牌，而導(dǎo)致頁面卡死。
因此，對(duì)品牌查詢接口做了分頁處理。
剛開始品牌表的數(shù)據(jù)比較少，沒有出現(xiàn)什么問題。
后來，產(chǎn)品加需求了，在品牌下拉選擇控件中，讓用戶可以輸入自定義品牌。
在用戶添加品牌前，需要先查一下，如果該品牌存在，則使用已有品牌。如果不存在，則新增品牌。（這里是精確匹配）
這個(gè)需求很簡(jiǎn)單，很容易實(shí)現(xiàn)了。
后來，產(chǎn)品又加需求了，需要按名稱模糊查詢品牌。
該功能上線后，使用了很長(zhǎng)一段時(shí)間，也沒啥問題。
突然，在不經(jīng)意的某一天，這個(gè)功能卻出問題了。
到底怎么回事呢？
1、案發(fā)現(xiàn)場(chǎng)
某一天下午，運(yùn)營(yíng)找到測(cè)試反饋一個(gè)問題說：明明品牌蘇三，已經(jīng)存在了，但用戶輸入關(guān)鍵字：蘇三時(shí)，系統(tǒng)沒有讓用戶直接選擇已有品牌，而是添加了一個(gè)叫：蘇三的自定義的品牌。
我過去一看，還真的有問題。
不一會(huì)兒，就定位到原因了，初步判斷是分頁的問題。
搜索關(guān)鍵字：蘇三，竟然出現(xiàn)了好幾頁的數(shù)據(jù)，把我驚呆了，品牌表怎么多了這么多數(shù)據(jù)了。
我查了數(shù)據(jù)庫，其實(shí)數(shù)據(jù)量并不是特別多，但有些品牌名稱比較特殊，有些品牌名稱是多個(gè)品牌名稱拼接而成的，比如：蘇三,李四 或者 蘇三,李四,王五，這是一個(gè)品牌。
其實(shí)是品牌名稱建的不規(guī)范導(dǎo)致的問題，但已經(jīng)沒法讓運(yùn)營(yíng)修改品牌了，只能通過技術(shù)手段解決目前的問題。
查詢第一頁的數(shù)據(jù)sql：
select * from brand where name like '%蘇三%'
order by edit_date desc limit 5;
執(zhí)行結(jié)果：
我們可以看到，圖中并沒有等于蘇三這兩個(gè)字的數(shù)據(jù)。
注意：為了好演示，這里給的每頁大小是5，真實(shí)的場(chǎng)景并非如此。
查詢第二頁的數(shù)據(jù)sql：
select * from brand where name like '%蘇三%'
order by edit_date desc limit 5,5;
執(zhí)行結(jié)果：
從圖中看到，在第二行，出現(xiàn)了正好等于蘇三這兩個(gè)字的數(shù)據(jù)。
用戶搜索關(guān)鍵字：蘇三 時(shí)，前端頁面在調(diào)用品牌查詢接口，pageNo默認(rèn)是1。由于能夠匹配關(guān)鍵字的數(shù)據(jù)太多了，第一頁返回不完，需要多頁才能全部返回。
前端獲取到第一頁的數(shù)據(jù)后，跟關(guān)鍵字：蘇三 做比較，發(fā)現(xiàn)沒有等于蘇三的品牌。
這樣就會(huì)在下拉控件中，自動(dòng)添加一個(gè)品牌：蘇三，同時(shí)在右邊增加自定義標(biāo)簽。
這樣就出問題了，明明蘇三這個(gè)品牌是有的，但用戶還能自定義一個(gè)蘇三，而不是直接選擇。
2、思考123
蘇三這個(gè)關(guān)鍵字，通過模糊查詢可以查詢出來，但由于品牌接口做了分頁，全匹配的品牌：蘇三，出現(xiàn)在第二頁了，才導(dǎo)致問題的產(chǎn)生。
如果要解決這個(gè)問題，讓它出現(xiàn)在第一頁不就OK了？
這時(shí)候，就有下面幾種解決方案。
（1）方案1
分頁查詢品牌接口，pageSize是5。
我們?yōu)槭裁床话裵ageSize調(diào)大一點(diǎn)？比如改成：200、500等。
這樣通過蘇三關(guān)鍵字，進(jìn)行模糊查詢的時(shí)候，結(jié)果基本都在第一頁。
這樣就能非常快速的解決問題。
但有個(gè)缺點(diǎn)就是：如果這次調(diào)大了pageSize，但后面查詢關(guān)鍵字的品牌又出現(xiàn)在第二頁怎么辦？
不可能一直改pageSize吧？
（2）方案2
把分頁查詢接口的數(shù)據(jù)，拆分成兩部分：
- 精確查詢
- 模糊查詢
在代碼中做處理的時(shí)候，先根據(jù)關(guān)鍵字精確查詢，即sql中使用name='蘇三'，這種方式查詢一次數(shù)據(jù)。
如果沒查出數(shù)據(jù)，則再直接用like '%蘇三'進(jìn)行模糊查詢。
如果查出了一條數(shù)據(jù)，則把它放在返回結(jié)果集合中的第一位置。接下來，使用like '%蘇三'進(jìn)行模糊查詢的時(shí)候，再加上條件 name <> '蘇三'。將查出的結(jié)果，從第二個(gè)位置往后放。
這樣可以拼接出你想要的集合。
但有個(gè)缺點(diǎn)，就是代碼耦合性太大了。
（3）方案3
之前，品牌蘇三在第二頁，最根本的原因是使用了edit_time字段進(jìn)行逆序的。
也就是說，修改時(shí)間越大的越排在前面，而品牌蘇三的修改時(shí)間很小，所以排在第二頁了。
如果想品牌蘇三，排在第一頁，修改一下排序規(guī)則，不就搞定了？
可以改成按：id或者name字段排序。
用id字段排序，不太合適，雖說用了雪花算法，但跟修改時(shí)間類似，先插入的數(shù)據(jù)，會(huì)越小。
select * from brand where name like '%蘇三%'
order by id desc limit 5;
用它排序的結(jié)果，跟使用修改時(shí)間排序差不了太多。
看來，只能使用name字段排序了。
3、如何排序？
我們?cè)趕ql中直接對(duì)name字段，進(jìn)行升序或者降序嗎？
顯然不是。
使用name字段降序：
select * from brand where name like '%蘇三%'
order by name desc limit 5;
執(zhí)行結(jié)果：
圖中并沒有看到我們想要的數(shù)據(jù)。
其實(shí)，使用name字段升序，也可能在第一頁查不出我們所想要的數(shù)據(jù)。
到底該如何處理呢？
假如，我們有這樣一種排序：
- 全匹配顯示在最前面，比如：蘇三。
- 數(shù)據(jù)左半部分匹配，右邊按字母排序，比如：蘇三1、蘇三2、蘇三說技術(shù)。
- 從中間開始匹配，比如：1蘇三、2蘇三。
- 第2步和第3步，還要根據(jù)字符長(zhǎng)度排序，字符短的排在前面，比如：1蘇三、1蘇三1、蘇三說技術(shù)。
如果我們能實(shí)現(xiàn)上面的這種排序方式，這個(gè)問題就能完美解決了。
說起來容易，做起來難。
難道要先全匹配：name='蘇三'，再有匹配：name like '蘇三%'，再左匹配：name like '%蘇三'，把查詢?nèi)蔚慕Y(jié)果組裝起來？
顯然這種做法有點(diǎn)low。
要實(shí)現(xiàn)上面我們?cè)O(shè)想的排序方式，在es中更好處理一下，但在mysql中要怎么處理呢？
4、解決方案
其實(shí)，我們可以換一種思路，根據(jù)字符的長(zhǎng)度排序。
mysql給我們提供了很多非常有用的函數(shù)，比如：char_length。
通過該函數(shù)就能獲取字符長(zhǎng)度。
sql調(diào)整如下：
select * from brand where name like '%蘇三%'
order by char_length(name) asc limit 5;
name字段使用關(guān)鍵字模糊查詢之后，再使用char_length函數(shù)，獲取name字段的字符長(zhǎng)度，然后按長(zhǎng)度升序。
僅這一個(gè)騷操作，就搞定需求了：
我們所期待的：蘇三，終于排在第一個(gè)了。同時(shí)由于該sql做了分頁的，即使name字段在查詢時(shí)丟失了索引，執(zhí)行效率也不會(huì)太低。
業(yè)務(wù)上的需求搞定了。
但追求完美的我們，好奇，想看看第二頁是什么情況：
select * from brand where name like '%蘇三%'
order by char_length(name) asc limit 5,5;
執(zhí)行結(jié)果：
并沒有按照我們?cè)O(shè)想的劇本進(jìn)行下去，我們之前假設(shè)的3條排序中，第2條和第3條都沒有滿足。
這時(shí)該怎么辦？
答：可以使用mysql中的locate函數(shù)，通過它可以匹配的關(guān)鍵字，在字符串中的位置。
使用locate函數(shù)改造之后sql如下：
select * from brand where name like '%蘇三%'
order by char_length(name) asc, locate('蘇三',name) asc limit 5,5;
執(zhí)行結(jié)果：
完美，終于出現(xiàn)我們想要的結(jié)果了。
除此之外，還可以使用：instr和position函數(shù)，它們的功能跟locate函數(shù)類似，在這里我就不一一介紹了，感興趣的小伙伴可以找我私聊。
5、總結(jié)
其實(shí)，模糊查詢和分頁，如果分開用，一般是沒問題的。
但如果它們要一起使用，一定要考慮排序問題。
如果只是按照簡(jiǎn)單的時(shí)間或者id排序，有些特殊的業(yè)務(wù)場(chǎng)景，沒辦法滿足，很容易出現(xiàn)bug。
當(dāng)然解決上面問題，還有其他辦法，比如：pageSize調(diào)大一點(diǎn)，或者把全匹配放到第一頁。
但更優(yōu)的方案，是通過mysql的函數(shù)來解決問題。
我們可以通過mysql提供的：char_length、locate、instr和position函數(shù)等，來實(shí)現(xiàn)很多復(fù)雜的排序功能。
