我是 PHP 程序员

背景

基于某些业务开发过程中，会遇到这样的查询语句：SELECT * FROM table where id in(1, 2, 3)， 但是当IN里面的数据量非常大的时候，有些数据库会对IN 的数据量有大小限制，比如Oracel大小限制是1000，因此如果能有一种替代IN 大量数据，并且性能还不错的方案就好了。

实战

1、使用IN方案查询结果，查询时间为：0.078s，总数据量60多万。

select * from designmaterial ds where ds.materialid in(
“00010448”,
“00010450”,
“00010454”,
“00010455”,
“00010457”,
“00010460”,
“00010466”,
“00010470”,
“00010474”,
“00010475”,
“00010477”,
“00010484”,
“00010487”,
“00010489”,
“00010491”,
“00010492”,
“00010493”,
“00011018”,
“00011019”,
“00011020”,
“00011071”,
“00011072”,
“00011073”)

2、改用替代方案，查询时间为：0.053S左右，总数据量60多万

SELECT *
FROM (
SELECT “00010448” cid UNION ALL
SELECT “00010450” UNION ALL
SELECT “00010454” UNION ALL
SELECT “00010455” UNION ALL
SELECT “00010457” UNION ALL
SELECT “00010460” UNION ALL
SELECT “00010466” UNION ALL
SELECT “00010470” UNION ALL
SELECT “00010474” UNION ALL
SELECT “00010475” UNION ALL
SELECT “00010477” UNION ALL
SELECT “00010484” UNION ALL
SELECT “00010487” UNION ALL
SELECT “00010489” UNION ALL
SELECT “00010491” UNION ALL
SELECT “00010492” UNION ALL
SELECT “00010493” UNION ALL
SELECT “00011018” UNION ALL
SELECT “00011019” UNION ALL
SELECT “00011020” UNION ALL
SELECT “00011071” UNION ALL
SELECT “00011072” UNION ALL
SELECT “00011073”
) AS tmp,designmaterial t
WHERE tmp.cid = t.materialid;

先数据变成一张临时表，然后再去匹配，里头id的双引号要变成英文双引号。然后第一行必须要有字段号，如cid，循环开始后不需要有字段。末尾不要UNION ALL

总结

经过mysql数据库大量测试结果替代方案比IN语句快了不少，特别是数据量比较大的时候，所以以后如果遇到IN查询过程可以使用该替代方案。