快捷導(dǎo)航

在MySQL字段中使用逗號(hào)分隔符的方法分享

更新時(shí)間：2012年06月17日 19:59:54 作者：

大多數(shù)開(kāi)發(fā)者應(yīng)該都遇到過(guò)在mysql字段中存儲(chǔ)逗號(hào)分割字符串的經(jīng)歷，無(wú)論這些被分割的字段代表的是id還是tag，這個(gè)字段都應(yīng)該具有如下幾個(gè)共性

被分割的字段一定是有限而且數(shù)量較少的，我們不可能在一個(gè)字符串中存儲(chǔ)無(wú)限多個(gè)字符
這個(gè)字段所屬的表與這個(gè)字段關(guān)聯(lián)的表，一定是一對(duì)多的關(guān)系
比如下面這個(gè)表結(jié)構(gòu)所代表的content與tag這兩個(gè)對(duì)象

復(fù)制代碼代碼如下:

 
mysql> SELECT * FROM content; 
+----+------+| id | tags | +----+------+| 1 | 1,2 | | 2 | 2,3 | +----+------+ 
2 rows in set (0.01 sec) 
mysql> SELECT * FROM tag; 
+----+-------+| id | name | +----+-------+| 1 | php | | 2 | mysql | | 3 | java | +----+-------+ 
3 rows in set (0.00 sec) 

這些原則問(wèn)題，相信大家在開(kāi)發(fā)過(guò)程中已經(jīng)很熟悉了。但是你在使用這種方法來(lái)處理實(shí)際問(wèn)題時(shí)，內(nèi)心一定還是有些許忐忑，因?yàn)檫@種方法或多或少看上去有點(diǎn)像野路子。在那本厚厚的《數(shù)據(jù)庫(kù)》教材中，也沒(méi)有提到這種設(shè)計(jì)方法，標(biāo)準(zhǔn)的方法似乎是應(yīng)該使用一個(gè)關(guān)系映射表在這兩個(gè)表之間插一杠子，盡管這樣會(huì)使用效率低下的連接查詢(xún)。

每個(gè)開(kāi)發(fā)者都曾糾結(jié)于標(biāo)準(zhǔn)與效率，但我想我們的努力能使這種方法的使用看起來(lái)更加標(biāo)準(zhǔn)。注意，以下討論的使用方法僅限于mysql，但其它數(shù)據(jù)庫(kù)應(yīng)該可以移植。

相關(guān)性檢索
很多開(kāi)發(fā)者還在使用古老的LIKE方法來(lái)實(shí)現(xiàn)相關(guān)性檢索，比如上面那個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)中，content表中的兩條記錄都有2這個(gè)tag，那么怎樣在我取出記錄1時(shí)，把與它tag相關(guān)的記錄也顯示出來(lái)呢。其實(shí)這也是CMS需要面對(duì)的一個(gè)基本問(wèn)題，也就是相關(guān)內(nèi)容的查詢(xún)。

如果你是一個(gè)菜鳥(niǎo)，你可能只會(huì)想到LIKE方法，比如先把記錄1取出來(lái)，然后再把tags字段按逗號(hào)分割，最后做一個(gè)循環(huán)用LIKE檢索content表中所有tags字段中包含2的記錄，類(lèi)似這樣

復(fù)制代碼代碼如下:

SELECT * FROM content WHERE tag LIKE '%2%' AND id <> 1

但這種方法實(shí)在是太慢了，查詢(xún)次數(shù)多不說(shuō)，LIKE查詢(xún)本來(lái)就是一個(gè)比較慢的方法。而且你還要處理前后逗號(hào)的問(wèn)題，總之麻煩是一大堆。

所以讓我們靜下心來(lái)翻翻mysql手冊(cè)，看看有沒(méi)有什么驚喜。這個(gè)時(shí)候，一個(gè)名為FIND_IN_SET的函數(shù)，會(huì)閃著金光映入你的眼簾。讓我們看看這個(gè)函數(shù)的定義

復(fù)制代碼代碼如下:

 
FIND_IN_SET(str,strlist) 
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character. 

哦，PERFECT! 簡(jiǎn)單說(shuō)來(lái)就是尋找一個(gè)字符串是否在另一個(gè)以逗號(hào)分割的字符串中存在的函數(shù)，這簡(jiǎn)直是為我們量身定做的。那么我們的sql就變成

復(fù)制代碼代碼如下:

SELECT * FROM content WHERE FIND_IN_SET('2', tags) AND id <> 1

在翻這些函數(shù)的過(guò)程中，你應(yīng)該已經(jīng)深深地體會(huì)到mysql的設(shè)計(jì)者對(duì)以逗號(hào)分割存儲(chǔ)字段方法的肯定，因?yàn)橛泻芏喾椒ň褪窃O(shè)計(jì)用來(lái)處理這種問(wèn)題的。

這樣看起來(lái)好多了，一切似乎完美了，是這樣嗎？其實(shí)還沒(méi)有，如果你的tag比較多，你需要?jiǎng)?chuàng)建多個(gè)sql語(yǔ)句，而且有的記錄關(guān)聯(lián)的tag比較多，有的比較少，怎么能按照相關(guān)性進(jìn)行排列呢。

這個(gè)時(shí)候，你可以關(guān)注mysql的全文檢索功能。這個(gè)詞你肯定看見(jiàn)過(guò)無(wú)數(shù)回了，但是這么使用的肯定很少，讓我們直接看語(yǔ)句吧

復(fù)制代碼代碼如下:

SELECT * FROM content WHERE MATCH(tags) AGAINST('1,2') AND id <> 1

這個(gè)語(yǔ)句的優(yōu)勢(shì)是顯而易見(jiàn)的，你不需要對(duì)tags字段做再次分割。那么這種查詢(xún)的原理是什么呢，稍微了解下MATCH AGAINST的用法就知道，全文檢索的默認(rèn)分隔符是標(biāo)點(diǎn)符號(hào)和stopwords，其中前者正是我們需要的特性。全文檢索按照逗號(hào)將MATCH和 AGAINST里的字符串做分割，然后將它們匹配。

需要注意的是上面sql僅僅是個(gè)例子，如果你直接這么執(zhí)行，是無(wú)法得到任何結(jié)果的。原因在以下

你需要對(duì)tags字段建立fulltext索引（如果僅僅是測(cè)試，可以不做，建索引只是提高性能，對(duì)結(jié)果沒(méi)有影響）
每個(gè)被標(biāo)點(diǎn)符號(hào)分割的word長(zhǎng)度必須在3個(gè)字符以上，這才是關(guān)鍵，我們的tag id太短了，會(huì)被自動(dòng)忽略掉，這個(gè)時(shí)候你可以考慮讓id從一個(gè)比較大值開(kāi)始自增，比如1000，這樣它就夠長(zhǎng)了。
你撞到了stopwords，比如你的tags字段是這樣的'hello,nobody'，nobody是mysql的一個(gè)默認(rèn)的stop words，它會(huì)被自動(dòng)忽略。stop words是英文中的一些無(wú)意義詞，搜索的時(shí)候不需要它們，類(lèi)似漢語(yǔ)中的助詞等等。但在我們的使用中顯然不是用來(lái)做搜索的，因此可以在my.cnf文件里，加上ft_stopword_file=''來(lái)禁用它

隨著WEB技術(shù)的發(fā)展，相關(guān)搜索走SQL的情況越來(lái)越少，很多時(shí)候只需要用搜索引擎就可以了。但本文的目的并不只是討論這種方法，而是體現(xiàn)實(shí)現(xiàn)這一結(jié)果的過(guò)程。

您可能感興趣的文章: