Mysql query reading a text file.

UNIX Solaris
1

Hi,
Is there any way that mysql query reads the content from a text file which has data in the below format:
1,2,3,4,5
and selects matched data from another table.
ie I have a table named xyz which has same ids as in that file.
I want this query to get count of the ids from xyz file by matching the ids.

At mysql prompt i run this command which gets me the count from xyz table.
mysql>select count(*) from xyz where id in (1,2,3,4,5);
Now i want the sql query to read the file and match these ids with ids in xyz table and get me the count.

2

No
Its not possible.
Mysql client only reads from the db and not from just any text file. You would have to use a mediator like perl to fetch data from both sources and then compare.

---------- Post updated at 04:27 PM ---------- Previous update was at 04:27 PM ----------

No
Its not possible.
Mysql client only reads from the db and not from just any text file. You would have to use a mediator like perl to fetch data from both sources and then compare.

3

I want to write a script which will connect to db and then in this script i give a query which shld read this data,match and get me the count.
can u tell me how will i use mediator like perl to get the count with some eg.

4

If it's really just this one line, use

echo "select count(*) from xyz where id in ($( cat test.txt ));" | mysql -u ....
5

cool one but a lot of struggle for further processing.
regards.

6

Thanks!! that was really exact what i wanted,without any struggle i could get the output which i required.
That was really helpful.:slight_smile:

7

Of course, you could also go an use the line from your other thread and create something compact but unreadable like

perl -le 'print "SELECT COUNT(*) FROM xyz WHERE id IN (".(join ",", map { chomp; $_ } <>).");";' file | mysql -u ....