【技术】MYSQL某字段中以逗号分隔的字符串的使用和查询方法
MYSQL查询某字段中以逗号分隔的字符串的方法
首先我们建立一张带有逗号分隔的字符串。CREATE TABLE test(id int(6) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),pname VARCHAR(20) NOT NULL,pnum VARCHAR(50) NOT NULL);
然后插入带有逗号分隔的测试数据
INSERT INTO test(pname,pnum) VALUES(‘产品1′,’1,2,4’);
INSERT INTO test(pname,pnum) VALUES(‘产品2′,’2,4,7’);
INSERT INTO test(pname,pnum) VALUES(‘产品3′,’3,4’);
INSERT INTO test(pname,pnum) VALUES(‘产品4′,’1,7,8,9’);
INSERT INTO test(pname,pnum) VALUES(‘产品5′,’33,4’);
查找pnum字段中包含3或者9的记录
mysql> SELECT * FROM test WHERE find_in_set(‘3’,pnum) OR find_in_set(‘9’,pnum);
+—-+——-+———+
| id | pname | pnum |
+—-+——-+———+
| 3 | 产品3 | 3,4 |
| 4 | 产品4 | 1,7,8,9 |
+—-+——-+———+
2 rows in set (0.03 sec)
使用正则
mysql> SELECT * FROM test WHERE pnum REGEXP ‘(3|9)’;
+—-+——-+———+
| id | pname | pnum |
+—-+——-+———+
| 3 | 产品3 | 3,4 |
| 4 | 产品4 | 1,7,8,9 |
| 5 | 产品5 | 33,4 |
+—-+——-+———+
3 rows in set (0.02 sec)
这样会产生多条记录，比如33也被查找出来了。
换一种方式
[sql][/sql]view plaincopy +—-+——-+———+
| id | pname | pnum |
+—-+——-+———+
| 3 | 产品3 | 3,4 |
| 4 | 产品4 | 1,7,8,9 |
+—-+——-+———+
2 rows in set (0.01 sec)
问题解决。。。
目录在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仅仅是个例子，如果你直接这么执行，是无法得到任何结果的。原因在以下
随着WEB技术的发展，相关搜索走SQL的情况越来越少，很多时候只需要用搜索引擎就可以了。但本文的目的并不只是讨论这种方法，而是体现实现这一结果的过程。
以下推文采用自研智能关联、组合专题技术…：