Menu
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
335
To perform where clause on comma separated string/values, MySQL has an inbuilt function called FIND_IN_SET which will search for values within a comma separated values. You can also use IN operator to achieve the same but there are some limitations with IN operator which I will show below.
A better answer: Don't store a list of comma separated values. Store one value per row, and use a SELECT query with GROUP_CONCAT to generate the comma separated value when you access the database.
SELECT TRIM(BOTH ',' FROM ',,,demo, ,xxxx,,,yyy,,,'); SELECT REPLACE(TRIM(TRIM(',' FROM ',,,demo, ,xxxx,,,yyy,,,')), ',,', ',');
To update values in multiple columns, you use a list of comma-separated assignments by supplying a value in each column's assignment in the form of a literal value, an expression, or a subquery. Third, specify which rows to be updated using a condition in the WHERE clause. The WHERE clause is optional.
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).
68
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
