在MySQL字段中使用逗号分隔符

编程开发

大多数开发者应该都遇到过在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的情况越来越少,很多时候只需要用搜索引擎就可以了。但本文的目的并不只是讨论这种方法,而是体现实现这一结果的过程。

已有 13 条评论
  1. blankyao
    blankyao

    这个方法很帅气,曾经为tag这个东西很纠结

  2. 鸿枫业
    鸿枫业

    准备使用Typecho,可是能不能做个WAP插件呢?

  3. 王道中强流
    王道中强流

    哈哈 吸收了

  4. 博主用的是typecho?正想转过来呢,正在纠结中!

  5. SF的投票功能(好问题,坏问题)是不是就是这样实现的呢?现在很蛋疼这个,如果用关系表的话来来回回查询花费的确会有点大。

  6. 混蛋70
    混蛋70

    sf的mysql只做基础存储,所有查询都是通过搜索引擎完成的

  7. MySQL中不是Set类型吗,最多可以存储64个,转换为字符串的话,表现形式上也是逗号分割的,FIND_IN_SET原本就是给这种类型用的

  8. aerotian
    aerotian

    老大,typecho怎么样勒,我们等到花儿都谢了

  9. TE 评论分页时地址栏中,类似comment-page-10#comments
    这个10是怎么输出的,如何调用 。
    谢谢,谢谢。
    想在自定义评论列表中调用这个值(即某条评论处于所有评论页的第几页中)

  10. 太好了

  11. taogogo
    taogogo

    我还在避免使用这种格式,没想到mysql已经实现了set函数,看来有必要去复习一下mysql手册了

  12. rainzee
    rainzee

    为什么typecho的讨论区被墙了?还有typecho什么时候才能更新啊?