Add List to a mysql parameter

Question

I have this question about the MySqlParameter from the .NET connector.

I have this query:

SELECT * FROM table WHERE id IN (@parameter)

And the MySqlParameter is:

intArray = new List<int>(){1,2,3,4};

...connection.Command.Parameters.AddWithValue("parameter", intArray);

This is possible? Is possible to pass an array of int to a single MySqlParameter? The other solution will be convert the array of int to a string such like "1,2,3,4", but this, when i pass it to the MySqlParameter and this is recognized as a string, it puts in the sql query like "1\,2\,3\,4" and this do not return the expected values.

@ UPDATE: Seems like the mysql connector team should work a little bit harder.

Answer 1

This doesn't work well for huge lists, but it is the only thing I have found that works if you have to pass a list in as a parameter.

Instead of

SELECT * FROM table WHERE id IN (@parameter)

You have to do this:

SELECT *
FROM table
WHERE INSTR(','+@parameter+',', ','+CAST(the_column AS CHAR) + ',')

Then you can pass in your list with string.Join(",", intArray)

It's a kludge, but it works.

Answer 2

Answer from Mud only works for the first int in the parameter list. This means '2,1,3,4' won't work if id is 1 for example.

See FIND_IN_SET() vs IN() .

No comment possible by now but also see answer from Matt Ellen. Would edit his answer but can't. INSTR doesn't seem to work in a WHERE case with more than one id (returns only on result).

But replacing INSTR with LOCATE make his solution work (with String.Join(",", intArray) as parameter added) ... UP VOTE from me:

LOCATE(CONCAT(',' , CAST(id AS CHAR) , ',') , CONCAT(',' , CAST(@paramter AS CHAR) , ',')) <> 0