表名:[news]
字段:news_id,news_title,category如果有一条数据category存放的内容为1,2,3,4,5,6
为什么下面的SQL语句查询不出数据
select * from [news] where '1' in(category) and news_id=2本来在MSSQL里可以用:
Select * from [news] where CharIndex(',1,', ',' + category + ',') > 0 and news_id=2但是在MYSQL里不行啊!
字段:news_id,news_title,category如果有一条数据category存放的内容为1,2,3,4,5,6
为什么下面的SQL语句查询不出数据
select * from [news] where '1' in(category) and news_id=2本来在MSSQL里可以用:
Select * from [news] where CharIndex(',1,', ',' + category + ',') > 0 and news_id=2但是在MYSQL里不行啊!
select * from `news` where '1' in(category) and news_id=2我试了..可以的啊.
-> news_id int not null auto_increment primary key,
-> new_title varchar(64) not null,
-> category int(11)
-> )
-> ;
Query OK, 0 rows affected (0.13 sec)mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| [new] |
| a |
| auto_t |
| ip |
| song |
| song2 |
| song3 |
| song4 |
| t |
| t1 |
| t2 |
| test1 |
| total |
| v |
+----------------+
14 rows in set (0.02 sec)mysql> insert into `[new]`(new_title,category) values('test1',1),
-> ('test2',1),
-> ('test3',3),
-> ('test4',4),
-> ('test5',5),
-> ('test6',6);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> select * from `[new]`;
+---------+-----------+----------+
| news_id | new_title | category |
+---------+-----------+----------+
| 1 | test1 | 1 |
| 2 | test2 | 1 |
| 3 | test3 | 3 |
| 4 | test4 | 4 |
| 5 | test5 | 5 |
| 6 | test6 | 6 |
+---------+-----------+----------+
6 rows in set (0.00 sec)mysql> select * from `[new]` where find_in_set(1,category) and news_id = 2;
+---------+-----------+----------+
| news_id | new_title | category |
+---------+-----------+----------+
| 2 | test2 | 1 |
+---------+-----------+----------+
1 row in set (0.00 sec)mysql> select * from `[new]` where 1 in (select category from `[new]`) and news_
id = 2;
+---------+-----------+----------+
| news_id | new_title | category |
+---------+-----------+----------+
| 2 | test2 | 1 |
+---------+-----------+----------+
1 row in set (0.00 sec)mysql>