在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仅仅是个例子,如果你直接这么执行,是无法得到任何结果的。原因在以下

网上查了一下,有两种方法,第一种最土的方法:使用like语句第二种听涛哥说用全文索引,就在网上搜一下:
如何在MySQL中获得更好的全文搜索结果

你需要对tags字段建立fulltext索引
每个被标点符号分割的word长度必须在3个字符以上,这才是关键,我们的tag
id太短了,会被自动忽略掉,这个时候你可以考虑让id从一个比较大值开始自增,比如1000,这样它就够长了。
你撞到了stopwords,比如你的tags字段是这样的’hello,nobody’,nobody是mysql的一个默认的stop
words,它会被自动忽略。stop
words是英文中的一些无意义词,搜索的时候不需要它们,类似汉语中的助词等等。但在我们的使用中显然不是用来做搜索的,因此可以在my.cnf文件
里,加上ft_stopword_file=”来禁用它

很多互联网应用程序都提供了全文搜索功能,用户可以使用一个词或者词语片断作为查询项目来定位匹配的记录。在后台,这些程序使用在一个SELECT
查询中的LIKE语句来执行这种查询,尽管这种方法可行,但对于全文查找而言,这是一种效率极端低下的方法,尤其在处理大量数据的时候。

随着WEB技术的发展,相关搜索走SQL的情况越来越少,很多时候只需要用搜索引擎就可以了。但本文的目的并不只是讨论这种方法,而是体现实现这一结果的过程。

mysql针对这一问题提供了一种基于内建的全文查找方式的解决方案。在此,开发者只需要简单地标记出需要全文查找的字段,然后使用特殊的MySQL方法在那些字段运行搜索,这不仅仅提高了性能和效率(因为MySQL对这些字段做了索引来优化搜索),而且实现了更高质量的搜索,因为MySQL使用自然  语言来智能地对结果评级,以去掉不相关的项目。
这篇文章将向您讲述在MySQL中如何进行全文搜索。

在进行数据库查询时,有完整查询和模糊查询之分。
SELECT 字段 FROM 表 WHERE 某字段 Like 条件
其中关于条件,SQL提供了四种匹配模式:
1,%:表示任意0个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘%三%’
将会把u_name为“张三”,“张猫三”、“三脚猫”,“唐三藏”等等有“三”的记录全找出来。
另外,如果需要找出u_name中既有“三”又有“猫”的记录,请使用and条件
SELECT * FROM [user] WHERE u_name LIKE ‘%三%’ AND u_name LIKE
‘%猫%’
若使用 SELECT * FROM [user] WHERE u_name LIKE ‘%三%猫%’
虽然能搜索出“三脚猫”,但不能搜索出符合条件的“张猫三”。
2,_:
表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句:
比如 SELECT * FROM [user] WHERE u_name LIKE ‘_三_’
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的;
再比如 SELECT * FROM [user] WHERE u_name LIKE ‘三__’;
只找出“三脚猫”这样name为三个字且第一个字是“三”的;
3,[
]:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’
将找出“张三”、“李三”、“王三”(而不是“张李王三”);
如 [ ] 内有一系列字符(01234、abcde之类的)则可略写为“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE ‘老[1-9]’
将找出“老1”、“老2”、……、“老9”;
4,[^ ] :表示不在括号所列之内的单个字符。其取值和 []
相同,但它要求所匹配对象为指定字符以外的任一个字符。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘[^张李王]三’
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等;
SELECT * FROM [user] WHERE u_name LIKE ‘老[^1-4]’;
将排除“老1”到“老4”,寻找“老5”、“老6”、……
5,查询内容包含通配符时
由于通配符的缘故,导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现,而把特殊字符用“[
]”括起便可正常查询。据此我们写出以下函数:
function sqlencode(str)
str=replace(str,”[“,”[[]”) ‘此句一定要在最前
str=replace(str,”_”,”[_]”)
str=replace(str,”%”,”[%]”)
sqlencode=str
end function
在查询前将待查字符串先经该函数处理即可。