極客小俊

?一個專注于web技術(shù)的80后

你不用拼過聰明人，你只需要拼過那些懶人 你就一定會超越大部分人!

前言

今天很多小伙伴來問我find_in_set這個函數(shù)在MySQL中到底有什么用處 還有與這個函數(shù)相關(guān)的應(yīng)用場景會有哪些? 今天我就來給大家講解一下這個函數(shù)從基本的使用到實際應(yīng)用! 讓大家不再迷茫!

1.首先認(rèn)識一下find_in_set()函數(shù)

首先很多小伙伴一定會去查閱MySQL的官方手冊, 但可能有些新手朋友查閱出來可能看不明白，那好吧我也先來查下手冊幫助新手朋友如何來看手冊中的解釋: 例如下圖:

官方文檔解釋的語法是: FIND_IN_SET(str,strlist) ;

文檔解釋:

(假如字符串str在由N個子鏈組成的字符串列表strlist 中, 則返回值的范圍在 1 到 N 之間) ? ?

這句話的意思就是看str這個字符在不在或者可以說成是否包含這個strlist字符列表中, 如果有存在或包含在strlist這個字符列表中就返回一個位置的數(shù)字, 并且這個數(shù)字一定是大于0 的數(shù)字,

這里解釋一下strlist字符串列表就是一個由一些被 逗號‘,’ 分開的單一字符串

注意:如果str不在strlist 或strlist 為空字符串，則返回值為 0 , 并且如任意一個參數(shù)為空，則返回值為 0 也可以說返回NULL

2.find_in_set()函數(shù)的實際基本操作

案例1

好了上面的解釋如果還沒有聽明白 我們就來看看實際的操作案例吧! ?打開cmd進入MySQL命令行界面, 執(zhí)行如下SQL語句

?SELECT FIND_IN_SET('b', 'a,b,c,d');
?#--結(jié)果為2 , 因為b 在strlist集合中放在2的位置 并且起始數(shù)是從1開始計算起的!

如下圖: ?這個案例應(yīng)該很簡單看出這個函數(shù)的作用了吧! ?

不難看出只要存在于第二個字符串列中的話就返回一個位置信息!

案例2

?select FIND_IN_SET('重慶','重慶');
?#-- 這里返回的就是位置下標(biāo)1
?
?select FIND_IN_SET('重慶','北京');
?#-- 這里返回的就是0 ?因為第一個字符串 重慶 ?不存在于第二個字符串當(dāng)中
?
?select FIND_IN_SET('2', '1，2');
?#-- 這里返回的就是位置下標(biāo)2
?
?select FIND_IN_SET('6', '1');
?#-- 這里返回的就是0 ?因為第一個字符串 6 ?不存在于第二個字符串當(dāng)中

所以注意: 這時候的第二個參數(shù)的集合就有點特殊了 只有一個字符串的情況下, 如果前一個字符串 存在于在后一個字符串集合中才返回大于0的數(shù) , 但如果不存在就會返回0

如下圖:

通過案例2 小伙伴們是不是更加對find_in_set()這個函數(shù)有所了解了呢？

注意 使用find_in_set函數(shù)進行查詢整張表的時候, 一次返回多條記錄的情況

比如說: id是某一個表的主鍵字段，然后每條記錄分別是id等于1，2，3，4，5的時候, 就有點類似in() 的操作了

案例:

?select * from per where find_in_set(id,'2,3,4,5,6');

那么結(jié)果也就可能是這樣，如下圖

其實上面的SQL語句也等同于下面的in

?select * from per where id in(2,3,4,5,6);

小提示: find_in_set() 一般都寫在 where關(guān)鍵字的后面!

3.find_in_set()與in()的應(yīng)用場景區(qū)別

剛才我們也看到了

?select * from per where find_in_set(id,'2,3,4,5,6');
?與
?select * from per where id in(2,3,4,5,6);

上面兩句SQL結(jié)果是一樣的 ，但細(xì)心的小伙伴們不知道看出來有哪里不一樣了沒有呢？

對答案就在于 一個有單引號而另一個沒有單引號這里也要針對你所檢測的字段是數(shù)字類型 還是字符串類型的字段!

對于find_in_set()而言

第二個參數(shù)無論是字符串還是數(shù)字都必須用單引號括起來, 并且字符串列表是用逗號隔開,是一個字符串列表整體要是不這樣寫 會報語法錯誤!

案例

?select * from per where find_in_set(id,'2,3,4,5,6');

對于in而言

如果是檢測的字段如果是數(shù)字類型的話就不能用單引號，因為這樣會讓結(jié)果出現(xiàn)偏差

案例

?select * from per where id in('2,3,4,5,6'); ?#--錯誤的
?
?select * from per where id in(2,3,4,5,6); ?#--正確的

如下圖:你會看到只有一個結(jié)果為什么呢？ 這是因為MySQL會自動的判斷字符串的第一個是否匹配，但是后面的就截掉了 沒有進行匹配!

正確的結(jié)果應(yīng)該是如下

如果是檢測的字符 那么每一個字符都需要用到單引號括起來 才可以, 就像下面的SQL語句, in與find_in_set()的比較 結(jié)果是一樣的,

但就是要注意一下以上我所講的注意一下數(shù)據(jù)類型細(xì)節(jié)區(qū)分問題就OK 了!

?select * from per where paddr in('重慶','北京','上海');
?#--比較
?select * from per where find_in_set(paddr,'重慶,北京,上海');

首先我們新建一個表 并插入一些數(shù)據(jù)來測試! SQL代碼語句如下:

CREATE TABLE `test1` ( ? `id` int(8) NOT NULL auto_increment, ? `name` varchar(255) NOT NULL, ? `list` varchar(255) NOT NULL, ? PRIMARY KEY ?(`id`) ); INSERT INTO `test1`(name,list) VALUES ('張三', '籃球,足球,羽毛球'); INSERT INTO `test1`(name,list) VALUES ('李四', '射箭,跳遠(yuǎn),足球'); INSERT INTO `test1`(name,list) VALUES ('王武', '跳遠(yuǎn),籃球'); INSERT INTO `test1`(name,list) VALUES ('小王', '射箭');

在使用in()的時候

比如說如下SQL語句

SELECT id,name,list from test1 WHERE '籃球' IN(list);

很多人原以為可以進行這樣的查詢,但結(jié)果確實返回的Empty 為什么呢?

原因:

因為只有當(dāng)list字段的內(nèi)容完全只等于 '籃球'這個值時（和IN前面的字符串才能完全匹配），查詢才有效果!

否則都得不到結(jié)果，即使'籃球'真的在list中 也不會有結(jié)果出來，這也體現(xiàn)了 in()對數(shù)據(jù)的精確性把控的不是很好!

也就是說 ''籃球'' 在張三、王武對應(yīng)的list字段里都存在, 但就是查詢不出來的原因, 就是除了''籃球' '還有其他的字符!

所以只有當(dāng)list字段里面的內(nèi)容完全只等于 in() 前面的字符串時 才能完全匹配，查詢才有效!

如下代碼

SELECT * from test1 WHERE '射箭' IN(list);

如下圖:

在使用find_in_set()的時候

再比如說 我們想查詢list字段中只包含籃球的數(shù)據(jù)行

SELECT id,name,list from test1 WHERE '籃球' IN('籃球','足球','羽毛球'); #-- 這里結(jié)果是全部都查詢出來了 而不是我們想要對應(yīng)的數(shù)據(jù) 不能正常的查詢出我們想要的結(jié)果! 為什么呢？

因為這句話的意思是 查詢 ''籃球'' 這個字符 在不在 這個in()所包含的字符常量里面，僅僅是如此， 然后如果成立, 有就把數(shù)據(jù)全部顯示出來, select后面跟的就是 這三個字段, 前面的條件成立了 所以這三個字段的所有數(shù)據(jù)也顯示出來了! ?這個應(yīng)該不難理解!

所以從需求語法的角度這句SQL就是不能正確給出結(jié)果的語句! 那么這樣查詢就顯然不合理!

但我們的需求 卻不是這樣，我們想的是查詢list字段中 只包含 '籃球',的數(shù)據(jù)行!

所以如果要讓以上的SQL能正確工作，需要用find_in_set()函數(shù)

代碼如下

SELECT id,name,list from test1 WHERE FIND_IN_SET('籃球',list);

小結(jié)

查字段固定的內(nèi)容時候, 比如: ?字段 in('籃球', '足球', '羽毛球'), ? 查是否有包含in中獨立存在的信息 就用in

查字段的內(nèi)容是否包含其中一個指定的關(guān)鍵內(nèi)容時 ,就要用find_in_set()函數(shù)

SELECT id,name,list from test1 WHERE list in('籃球','足球'); #-- 返回list字段內(nèi)容只是籃球 或 足球的數(shù)據(jù) SELECT id,name,list from test1 WHERE find_in_set('籃球',list); #-- 返回list字段內(nèi)容中包含了籃球的數(shù)據(jù)

4.find_in_set()和like的應(yīng)用場景區(qū)別

在mysql中，有時我們在做數(shù)據(jù)庫查詢時，需要得到某字段中包含某個值的記錄，但是它也不是用like能解決的，使用like可能查到我們不想要的記錄,這時候mysql的FIND_IN_SET函數(shù)就派上用場了

需求案例1

我們來看下面的應(yīng)用場景例子: 權(quán)限

代碼案例如下

#-- 創(chuàng)建表并插入語句 CREATE TABLE users( ? ? id int(6) NOT NULL AUTO_INCREMENT, ? ? name VARCHAR(20) NOT NULL, ? ? limits VARCHAR(50) NOT NULL, -- 權(quán)限字段 ? ? PRIMARY KEY (id) ); INSERT INTO users(name, limits) VALUES('張三','1,2,12'); ?INSERT INTO users(name, limits) VALUES('李四','11,22,32'); INSERT INTO users(name, limits) VALUES('王武','1,2,32');

其中l(wèi)imits表示用戶所擁有的權(quán)限（以逗號分隔）!

需求

現(xiàn)在想查詢擁有權(quán)限編號為2的用戶，則SQL查詢結(jié)果如下：

SELECT * FROM users WHERE limits LIKE '%2%';

如果用like關(guān)鍵字的話，則查詢結(jié)果如下：

這樣第二條數(shù)據(jù)結(jié)果是不具有權(quán)限'2'的用戶也查出來了，這種是完全不符合預(yù)期的結(jié)果!

那么現(xiàn)在我們利用find_in_set()函數(shù)來解決這個問題 ?SQL語句如下:

#-- 查詢用戶擁有權(quán)限編號為:2 的用戶 SELECT * FROM users WHERE FIND_IN_SET(2,limits);

這樣就能達(dá)到我們預(yù)期的效果，問題就解決了！

需求案例2

人有時會身兼數(shù)職，需要查找出其中擔(dān)任某一職務(wù)的都有哪些人，如下面position字段，不同的職務(wù)用數(shù)字表示，多個職務(wù)以逗號隔開。其實跟上面的案例是一樣的,

如下表: User信息表

需求

查找出擔(dān)任1職務(wù)的人員:

如果是用模糊查詢的方式 如下:

select * from 表名稱 where position like '%1%'

很顯然跟上面我說的也是一個道理，雖然把沒有包含1的過濾掉了，仔細(xì)觀察你會發(fā)現(xiàn)position為: '10' 的也被查出來了，但這個不符合業(yè)務(wù)要求 ?如下:

所以這種需求的解決方案跟上面完全一樣:采用MySQL的原生函數(shù)find_in_set(str,array)來查詢即可

select * from user where find_in_set(1,position);

結(jié)果如下圖:

小提示 FIND_IN_SET(str,strlist)函數(shù)，其中strlist字符串列表參數(shù)只識別英文逗號, 所以小伙伴們在使用PHP 或者JAVA插入數(shù)據(jù)的時候一定要記得使用逗號把字符串分割好在保存到字段中去哦!

小結(jié):

1. find_in_set(str1,strlist)字符串函數(shù)是返回strlist中str1所在的位置索引, strlist必須以","分割開。

1. like是廣泛的模糊匹配，字符串中沒有分隔符，

2. find_in_set是精確匹配，字段值以英文”,”分隔，find_in_set查詢的結(jié)果要比like查詢更加精確!

總結(jié)

關(guān)于find_in_set()函數(shù)我就先分享到這里! ?大家有問題的話可以私信 或 關(guān)注我的博客 我會盡量給大家提供解答意見!

如果喜歡話請 點贊 ?投幣 ?收藏 一鍵三連 ?

大家的支持就是我堅持下去的動力!

不要忘了?? 關(guān)注 ??哦！