MySql select IN clause string comma delimited

Question

I need to perform a select query in the following manner:

select * from my_table where id NOT IN (comma_delimited_string);

What is the correct way to achieve that?

Considering the fact that I am in control of the client code which sends the string, is there a better approach? (the string will hold approximately 30 id's so I am trying to avoid sending 30 parameters, one for each id).

Thank you all

Answer 1

You can use the MySQL FIND_IN_SET function:

SELECT *
FROM my_table
WHERE FIND_IN_SET(id, comma_delimited_string) = 0

---

Addendum: Note that the query above is not optimizable, so if you have an index on id MySQL won't use it. You'll have to decide if the relative simplicity of using FIND_IN_SET is worth taking a potential performance hit (I say potential because I don't know if id is indexed or if your table is large enough for this to be a concern).