我的这个字段是text类型里边存的内容不多,数据量也不大暂时不用考虑效率问题,我的问题是这样的,比如我这个字段存了像这们的数据:3250, 5320, 5500, 5630, 5730, 5700, 5710, 6110, 6120c, 6121, 6124, 6210N, 6220c, 6290, 6760s, 6710N, 6720, E50, E51, E52, E55, E60, E61, E62, E63, E65, E66, E71, e72,e73,E90, N71, N73, N75, N76, N78, N79, N81, N82, n85, N86, N92, N93, N95, n96, e75, e55, n91,6700s, 6702s,c5,e5,n8
我想select * from talbe where filed like '%n8%'问题来了,这样会把n8,n81,n82等等都找出来,可我只想要单纯的n8,请问怎么实现
我想select * from talbe where filed like '%n8%'问题来了,这样会把n8,n81,n82等等都找出来,可我只想要单纯的n8,请问怎么实现
where FIND_IN_SET('n8',col);
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
`id` int(11) NOT NULL AUTO_INCREMENT,
`classid` smallint(6) NOT NULL DEFAULT '0',
`onclick` int(11) NOT NULL DEFAULT '0',
`newspath` varchar(50) NOT NULL DEFAULT '',
`keyboard` varchar(255) NOT NULL DEFAULT '',
`keyid` varchar(255) NOT NULL DEFAULT '',
`userid` int(11) NOT NULL DEFAULT '0',
`username` varchar(30) NOT NULL DEFAULT '',
`ztid` varchar(255) NOT NULL DEFAULT '',
`checked` tinyint(1) NOT NULL DEFAULT '0',
`istop` tinyint(1) NOT NULL DEFAULT '0',
`truetime` int(11) NOT NULL DEFAULT '0',
`ismember` tinyint(1) NOT NULL DEFAULT '0',
`dokey` tinyint(1) NOT NULL DEFAULT '0',
`userfen` int(11) NOT NULL DEFAULT '0',
`isgood` tinyint(1) NOT NULL DEFAULT '0',
`titlefont` varchar(50) NOT NULL DEFAULT '',
`titleurl` varchar(200) NOT NULL DEFAULT '',
`filename` varchar(60) NOT NULL DEFAULT '',
`filenameqz` varchar(28) NOT NULL DEFAULT '',
`fh` tinyint(1) NOT NULL DEFAULT '0',
`groupid` smallint(6) NOT NULL DEFAULT '0',
`newstempid` smallint(6) NOT NULL DEFAULT '0',
`plnum` int(11) NOT NULL DEFAULT '0',
`firsttitle` tinyint(1) NOT NULL DEFAULT '0',
`isqf` tinyint(1) NOT NULL DEFAULT '0',
`totaldown` int(11) NOT NULL DEFAULT '0',
`title` varchar(200) NOT NULL DEFAULT '',
`newstime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`titlepic` varchar(200) NOT NULL DEFAULT '',
`closepl` tinyint(1) NOT NULL DEFAULT '0',
`havehtml` tinyint(1) NOT NULL DEFAULT '0',
`lastdotime` int(11) NOT NULL DEFAULT '0',
`haveaddfen` tinyint(1) NOT NULL DEFAULT '0',
`infopfen` int(11) NOT NULL DEFAULT '0',
`infopfennum` int(11) NOT NULL DEFAULT '0',
`votenum` int(11) NOT NULL DEFAULT '0',
`softwriter` varchar(30) NOT NULL DEFAULT '',
`homepage` varchar(80) NOT NULL DEFAULT '',
`demo` varchar(120) NOT NULL DEFAULT '',
`softfj` varchar(255) NOT NULL DEFAULT '',
`language` varchar(30) NOT NULL DEFAULT '',
`softtype` varchar(30) NOT NULL DEFAULT '',
`softsq` varchar(30) NOT NULL DEFAULT '',
`star` tinyint(1) NOT NULL DEFAULT '0',
`filetype` varchar(10) NOT NULL DEFAULT '',
`filesize` varchar(16) NOT NULL DEFAULT '',
`downpath` text NOT NULL,
`softsay` text NOT NULL,
PRIMARY KEY (`id`),
KEY `checked` (`checked`),
KEY `newstime` (`newstime`),
KEY `truetime` (`truetime`),
KEY `classid` (`classid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=21108 ;mysql 是 5.1.16 windows版本,编码是gbk的,刚你说的办法,可惜的是,查不到任何东西
-- PHP 版本: 5.1.4select * from [!db.pre!]ecms_download where FIND_IN_SET('n8',downpath) and classid in(78,88,89,90,127,128,129,130,146,147,163) order by id desc
(21089, 93, 106, '', '反恐,电影', '21020,20949,20331,18995,20268,19221,19938,19129,19126,19076', 3, '老婆', '|', 1, 0, 1283393082, 0, 0, 0, 0, '', '', 'shoujizhuti-21089', '', 0, 0, 2, 0, 0, 0, 3, '反恐24小时', '2010-09-02 10:03:34', 'http://d.163.com/nokiazhuti/movie/8c57304b80f825c2eac16dc0037688d7.gif', 0, 1, 1283393082, 0, 0, 0, 0, '', 'http://', 'http://', '', '简体中文', '国产软件', '共享软件', 2, '.sis', '841.64 KB', 's60v5系列::::::http://d.163.com/nokiazhuti/movie/0226c8f0a6f82d923895ef295216727d.sis::::::0::::::0::::::支持机型:5530XM, 5800 , 5800i, 5802, n97, 5230, 5232, 5233, 5235, x6,5900,c6,5250等s60v5系列::::::\r\ns60v3系列::::::http://d.163.com/nokiazhuti/movie/478983aeb67ff2a61857357f5a3f9566.sis::::::0::::::0::::::支持机型:3250, 5320, 5500, 5630, 5730, 5700, 5710, 6110, 6120c, 6121, 6124, 6210N, 6220c, 6290, 6760s, 6710N, 6720, E50, E51, E52, E55, E60, E61, E62, E63, E65, E66, E71, e72,e73,E90, N71, N73, N75, N76, N78, N79, N81, N82, n85, N86, N92, N93, N95, n96, e75, e55, n91,6700s, 6702s,c5,e5等s60v3系列::::::', '反恐24小时'),
(21090, 80, 116, '', '单车,非主流', '21088,21087,20428,21081,21078,21076,20063,21070,21066,21052', 3, '老婆', '|', 1, 0, 1283393172, 0, 0, 0, 0, '', '', 'shoujizhuti-21090', '', 0, 0, 2, 0, 0, 0, 3, '单车倒影', '2010-09-02 10:04:57', 'http://d.163.com/nokiazhuti/feizhuliu/44cbab156d0264dfb6fd4d87dc626e64.gif', 0, 1, 1283393172, 0, 0, 0, 0, '', 'http://', 'http://', '', '简体中文', '国产软件', '共享软件', 2, '.sis', '806.36 KB', 's60v5系列::::::http://d.163.com/nokiazhuti/feizhuliu/7388898c36c1d64e7b88c57f6a296690.sis::::::0::::::0::::::支持机型:5530XM, 5800 , 5800i, 5802, n97, 5230, 5232, 5233, 5235, x6,5900,c6,5250等s60v5系列::::::\r\ns60v3系列::::::http://d.163.com/nokiazhuti/feizhuliu/f14ddab678c6019610cc4dbae13ed7e9.sis::::::0::::::0::::::支持机型:3250, 5320, 5500, 5630, 5730, 5700, 5710, 6110, 6120c, 6121, 6124, 6210N, 6220c, 6290, 6760s, 6710N, 6720, E50, E51, E52, E55, E60, E61, E62, E63, E65, E66, E71, e72,e73,E90, N71, N73, N75, N76, N78, N79, N81, N82, n85, N86, N92, N93, N95, n96, e75, e55, n91,6700s, 6702s,c5,e5等s60v3系列::::::', '单车倒影');
一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。
如果第一个参数是一个常数字符串,而第二个是type SET列,则 FIND_IN_SET() 函数被优化,使用比特计算。如果str不在strlist 或strlist 为空字符串,则返回值为 0 。如任意一个参数为NULL,则返回值为 NULL。 这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。 mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2 用起来很简单
symbian 3::::::http://d.163..net/softwaredown/liaotian/QQ1.0_Symbian3_Beta2_Build0399.sisx::::::0::::::0::::::支持机型:n8,c7-00,n81,n82等Symbian^3系列::::::
今天试了下SELECT *
FROM `2008ecms_download`
WHERE FIND_IN_SET( 'n8', downpath )
LIMIT 0 , 30 貌似不可以,不知是我用的不对,还是说这样的方法只能找在n8,在开头的
+------------------------------------------+
| find_in_set('N78',' N73, N75, N76, N78') |
+------------------------------------------+
| 0 |
+------------------------------------------+
1 row in set (0.00 sec)这样实际上是查不出来的;localhost~root@localhost~test>update ecms_download set downpath=replace(downpath,', ',',');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0localhost~root@localhost~test>select find_in_set('N78',downpath) from ecms_download
-> ;
+-----------------------------+
| find_in_set('N78',downpath) |
+-----------------------------+
| 48 |
| 48 |
+-----------------------------+
2 rows in set (0.00 sec)把空格去掉,才符合FIND_IN_SET语法,这样就查出来了。
SELECT *
FROM `2008ecms_download`
WHERE downpath regexp '%n8[^1-9]%' or downpath regexp '%n8,%'
LIMIT 0 , 30
+-------------------------------------------+
| find_in_set('N78','aaaa:N78,N75,N76,N79') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.00 sec)localhost~root@localhost~test>select find_in_set('N75','aaaa:N78,N75,N76,N79');
+-------------------------------------------+
| find_in_set('N75','aaaa:N78,N75,N76,N79') |
+-------------------------------------------+
| 2 |
+-------------------------------------------+
1 row in set (0.00 sec)
如果是你提的那样,symbian 3::::::http://d.163..net/softwaredown/liaotian/QQ1.0_Symbian3_Beta2_Build0399.sisx::::::0::::::0::::::支持机型:n8这相当于一个匹配项,肯定匹配不到n8了。