在MySQL字段中使用逗號分隔符的方法分享

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

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

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

復(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) 

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

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

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

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

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

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

但這種方法實在是太慢了，查詢次數(shù)多不說，LIKE查詢本來就是一個比較慢的方法。而且你還要處理前后逗號的問題，總之麻煩是一大堆。

所以讓我們靜下心來翻翻mysql手冊，看看有沒有什么驚喜。這個時候，一個名為FIND_IN_SET的函數(shù)，會閃著金光映入你的眼簾。讓我們看看這個函數(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! 簡單說來就是尋找一個字符串是否在另一個以逗號分割的字符串中存在的函數(shù)，這簡直是為我們量身定做的。那么我們的sql就變成

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

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

在翻這些函數(shù)的過程中，你應(yīng)該已經(jīng)深深地體會到mysql的設(shè)計者對以逗號分割存儲字段方法的肯定，因為有很多方法就是設(shè)計用來處理這種問題的。

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

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

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

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

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

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

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

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

您可能感興趣的文章: