快捷導(dǎo)航

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

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

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

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

復(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)很熟悉了。但是你在使用這種方法來處理實(shí)際問題時(shí)，內(nèi)心一定還是有些許忐忑，因?yàn)檫@種方法或多或少看上去有點(diǎn)像野路子。在那本厚厚的《數(shù)據(jù)庫》教材中，也沒有提到這種設(shè)計(jì)方法，標(biāo)準(zhǔn)的方法似乎是應(yīng)該使用一個(gè)關(guān)系映射表在這兩個(gè)表之間插一杠子，盡管這樣會(huì)使用效率低下的連接查詢。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

您可能感興趣的文章: