怪我没说清楚a表中不只有1,2,3还有6,7,8,9,90,.....所以 select * from a where id not in (1,2,3,4,5) 就不行了
循环把数组的值取出来拼接成sql语句就可以啦 sql= “select * from a where id not in (” for(i=0,,){ if(i=数组长度-1)sql+=数组(i) else sql+=数组(i)+“,” } sql+=“)” 循环里的判断意思是如果循环到最后一位不加那个“,”,否则加“,”
mysql> create table a (id int not null); Query OK, 0 rows affected (0.43 sec)mysql> insert into a values (1),(2),(3),(4),(5),(8),(30),(44),(40); Query OK, 9 rows affected (0.08 sec) Records: 9 Duplicates: 0 Warnings: 0 存在 mysql> select * from a where instr('1,2,3,4,5',id) > 0; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.00 sec) 不存在 mysql> select * from a where instr('1,2,3,4,5',id) = 0; +----+ | id | +----+ | 8 | | 30 | | 44 | | 40 | +----+ 4 rows in set (0.07 sec)mysql>
骑着上帝看戏 select * from a where instr('1,2,3,4,5',id) = 0; 好像和 select * from a where id not in (1,2,3,4,5)是一样的其实可能是我说的不清楚, 比如a中有1,2,3,4,5,6,7,8 b中有1,2,3,4,10,11 结果应该是取出b中的10,11而不是a中的5,6,7,8
select * from a where id not in (1,2,3,4,5)
就不行了
sql= “select * from a where id not in (”
for(i=0,,){
if(i=数组长度-1)sql+=数组(i)
else sql+=数组(i)+“,”
}
sql+=“)” 循环里的判断意思是如果循环到最后一位不加那个“,”,否则加“,”
mysql> create table a (id int not null);
Query OK, 0 rows affected (0.43 sec)mysql> insert into a values (1),(2),(3),(4),(5),(8),(30),(44),(40);
Query OK, 9 rows affected (0.08 sec)
Records: 9 Duplicates: 0 Warnings: 0
存在
mysql> select * from a where instr('1,2,3,4,5',id) > 0;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)
不存在
mysql> select * from a where instr('1,2,3,4,5',id) = 0;
+----+
| id |
+----+
| 8 |
| 30 |
| 44 |
| 40 |
+----+
4 rows in set (0.07 sec)mysql>
instr( ',11,22,33,',concat( ',',`id`,',') )
select * from a where instr('1,2,3,4,5',id) = 0;
好像和
select * from a where id not in (1,2,3,4,5)是一样的其实可能是我说的不清楚,
比如a中有1,2,3,4,5,6,7,8
b中有1,2,3,4,10,11
结果应该是取出b中的10,11而不是a中的5,6,7,8
第一步很好做,前面的方法就可以了。
第二步在程序中判断吧。