I want to select from a table where a field is in a given csv string passed to a stored proc.
What is the fastest way to do this? Should I insert the values into a temporary table and join to that? Or is there a way to do it with one statement?
Thanks
Did some searching around and found a great answer.:)
Use MySql's string function FIND_IN_SET.
Example use:
SET @csvStr = "val1,val2,val3";
SELECT Col1
FROM Table1
WHERE FIND_IN_SET(Col2, @csvStr) > 0;
I edited the current answer with the "New Answer" and "Old Answer" pointing out that the new answer opens up your system to a SQL Injection vulnerability.
NEW ANSWER -
Well you have to do something like this as given below. I am assuming your csv string would be as given in variable @str below. Else you need to make sure that your string (or arraystring) should have this format with single quotes for every element -
set @str = "'some1','some2','some3'";
set @qry1 = CONCAT('select * from testing where col1 in (',@str,')');
prepare stmt1 from @qry1;
execute stmt1;
deallocate prepare stmt1;
OLD ANSWER -
I assume that you will pass the csv file path to stored proc and read the lines in csv in that stored proc. So basically you can store all those csv field values in a temp table and write query using IN -
select * from sourceTable
where fieldValue in (select csvFieldValue from #temptable)
