mysql> select * from test; +----+-------+---------+-----+ | id | class | name | age | +----+-------+---------+-----+ | 1 | A | aaa | 20 | | 2 | A | B | 25 | | 3 | B | ddd | 30 | | 4 | C | B | 40 | | 5 | B | sssffff | 50 | +----+-------+---------+-----+ 5 rows in set (0.00 sec)mysql> select * from test where class='B' or name='B'; +----+-------+---------+-----+ | id | class | name | age | +----+-------+---------+-----+ | 2 | A | B | 25 | | 3 | B | ddd | 30 | | 4 | C | B | 40 | | 5 | B | sssffff | 50 | +----+-------+---------+-----+ 4 rows in set (0.00 sec)
mysql> select * from test where class='B' union select * from test where name='B'; +----+-------+---------+-----+ | id | class | name | age | +----+-------+---------+-----+ | 3 | B | ddd | 30 | | 5 | B | sssffff | 50 | | 2 | A | B | 25 | | 4 | C | B | 40 | +----+-------+---------+-----+ 4 rows in set (0.02 sec) 用union效率会快些 不知楼主的意思是这意思不
用like啊 搜索查询,不是完整匹配
我要做的是模糊搜索比如同时在两个字段title和content里模糊搜索关键字“国庆”
mysql> select * from test where instr(class,'B')>=1 union select * from test where instr(name,'B')>=1; +----+-------+---------+-----+ | id | class | name | age | +----+-------+---------+-----+ | 3 | B | ddd | 30 | | 5 | B | sssffff | 50 | | 2 | A | B | 25 | | 4 | C | B | 40 | +----+-------+---------+-----+ 4 rows in set (0.00 sec)
select * from tt where find_in_set('国庆',title)+find_in_set('国庆',content)>0
+----+-------+---------+-----+
| id | class | name | age |
+----+-------+---------+-----+
| 1 | A | aaa | 20 |
| 2 | A | B | 25 |
| 3 | B | ddd | 30 |
| 4 | C | B | 40 |
| 5 | B | sssffff | 50 |
+----+-------+---------+-----+
5 rows in set (0.00 sec)mysql> select * from test where class='B' or name='B';
+----+-------+---------+-----+
| id | class | name | age |
+----+-------+---------+-----+
| 2 | A | B | 25 |
| 3 | B | ddd | 30 |
| 4 | C | B | 40 |
| 5 | B | sssffff | 50 |
+----+-------+---------+-----+
4 rows in set (0.00 sec)
+----+-------+---------+-----+
| id | class | name | age |
+----+-------+---------+-----+
| 3 | B | ddd | 30 |
| 5 | B | sssffff | 50 |
| 2 | A | B | 25 |
| 4 | C | B | 40 |
+----+-------+---------+-----+
4 rows in set (0.02 sec)
用union效率会快些 不知楼主的意思是这意思不
搜索查询,不是完整匹配
+----+-------+---------+-----+
| id | class | name | age |
+----+-------+---------+-----+
| 3 | B | ddd | 30 |
| 5 | B | sssffff | 50 |
| 2 | A | B | 25 |
| 4 | C | B | 40 |
+----+-------+---------+-----+
4 rows in set (0.00 sec)
2
与INSTR一样,查询子串的位置,如果没有子串,则返回0
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/01/4141864.aspx
mysql> select * from members;
+-----+-------+---------------------------------+
| uid | uname | hobby |
+-----+-------+---------------------------------+
| 1 | AAAA | 音乐,电影,网络,篮球,阅读,乒乓球 |
| 2 | BBBB | 音乐,阅读,乒乓球,发呆,围棋,参禅 |
| 3 | CCCC | 交友,乒乓球 |
| 4 | DDDD | 台球,网络,看书,旅游 |
| 5 | EEEE | 音乐,发呆,下围棋,参禅 |
+-----+-------+---------------------------------+
4 rows in set (0.00 sec)mysql> select * from members
-> where concat(',',hobby,',') regexp
-> concat(',(',replace('阅读,交友,围棋,足球,滑雪',',','|'),'),');
+-----+-------+---------------------------------+
| uid | uname | hobby |
+-----+-------+---------------------------------+
| 1 | AAAA | 音乐,电影,网络,篮球,阅读,乒乓球 |
| 2 | BBBB | 音乐,阅读,乒乓球,发呆,围棋,参禅 |
| 3 | CCCC | 交友,乒乓球 |
+-----+-------+---------------------------------+
3 rows in set (0.00 sec)
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/01/4141864.aspx建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
里面有字段title,content现在要同时模糊搜索两个字段title,content,关键字为“国庆”这样够详细了吗?单字段搜索我用的是like,但是多字段模糊搜索就不知道怎么搞了
select * from Products where title like '%国庆%' or content like '%国庆%';建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。