开发者

Use terms from text file in SQL WHERE ... IN clause

I'm running SQL query for MySQL server with

...
where name in ("term1","term2","term3")

I would like to get the list of terms from a text file with one 开发者_运维百科term per line. How can I do this?

What about if this list of terms will get large? 10K, for example. Will this be efficient or I should use another approach? May be temporary table and join? The database is read-only.


I am not so familiar with MySQL but I see something like this where you can load a text file in a table as you suggested in your question:-

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2, column3);

and then use joins for getting data.

Details here.


Generally, using a WHERE ... IN statement gets very slow after a couple of hundred/thousand terms.

Also, if the table is read-only, then you won't be able to add indexes to make it any faster, so, like astander said, I would suggest a temporary table with a join.


I think your best bet might be to read this into a temp table/ table used specifically for this look up.

This way you can index the table, and use an inner join/ where in sub query.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜