在MySQL欄位中使用逗號分隔字元

文章目錄

• 相關性檢索

轉載自：https://joyqi.com/information-tech/use-comma-in-mysql-columns.html

大多數開發人員應該都遇到過在mysql欄位中儲存逗號分割字串的經曆，無論這些被分割的欄位代表的是id還是tag，這個欄位都應該具有如下幾個共性。

• 被分割的欄位一定是有限而且數量較少的，我們不可能在一個字串中儲存無限多個字元
• 這個欄位所屬的表與這個欄位關聯的表，一定是一對多的關係

比如下面這個表結構所代表的content與tag這兩個對象

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)

 

這些原則問題，相信大家在開發過程中已經很熟悉了。但是你在使用這種方法來處理實際問題時，內心一定還是有些許忐忑，因為這種方法或多或少看上去有點像野路子。在那本厚厚的《資料庫》教材中，也沒有提到這種設計方法，標準的方法似乎是應該使用一個關係映射表在這兩個表之間插一杠子，儘管這樣會使用效率低下的串連查詢。

每個開發人員都曾糾結於標準與效率，但我想我們的努力能使這種方法的使用看起來更加標準。注意，以下討論的使用方法僅限於mysql，但其它資料庫應該可以移植。

相關性檢索

很多開發人員還在使用古老的LIKE方法來實現相關性檢索，比如上面那個資料庫結構中，content表中的兩條記錄都有2這個tag，那麼怎樣在我取出記錄1時，把與它tag相關的記錄也顯示出來呢。其實這也是CMS需要面對的一個基本問題，也就是相關內容的查詢。

如果你是一個菜鳥，你可能只會想到LIKE方法，比如先把記錄1取出來，然後再把tags欄位按逗號分割，最後做一個迴圈用LIKE檢索content表中所有tags欄位中包含2的記錄，類似這樣

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

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

所以讓我們靜下心來翻翻mysql手冊，看看有沒有什麼驚喜。這個時候，一個名為FIND_IN_SET的函數，會閃著金光映入你的眼帘。讓我們看看這個函數的定義

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! 簡單說來就是尋找一個字串是否在另一個以逗號分割的字串中存在的函數，這簡直是為我們量身定做的。那麼我們的sql就變成

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

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

這樣看起來好多了，一切似乎完美了，是這樣嗎？其實還沒有，如果你的tag比較多，你需要建立多個sql語句，而且有的記錄關聯的tag比較多，有的比較少，怎麼能按照相關性進行排列呢。

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

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

這個語句的優勢是顯而易見的，你不需要對tags欄位做再次分割。那麼這種查詢的原理是什麼呢，稍微瞭解下MATCH AGAINST的用法就知道，全文檢索索引的預設分隔符號是標點符號和stopwords，其中前者正是我們需要的特性。全文檢索索引按照逗號將MATCH和AGAINST裡的字串做分割，然後將它們匹配。

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

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

隨著WEB技術的發展，相關搜尋走SQL的情況越來越少，很多時候只需要用搜尋引擎就可以了。但本文的目的並不只是討論這種方法，而是體現實現這一結果的過程。