在MySQL字段中利用逗号脱离符的办法分享[MySQL防范]

被分割的字段一定是有限并且数目较少的,我们不大概在一个字符串中存储无限多个字符
这个字段所属的表与这个字段关联的表,一定是一对多的关系
比方下面这个表构造所代表的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的情形越来越少,很多时刻只需求用搜索引擎便可以了.但本文的目的并不只是谈论这种办法,而是表实际现这一后果的历程.