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