MySQL variable format for a “NOT IN” list of values

by

You can’t use the IN clause like that. It compiles to a single string in your IN clause. But an IN clause needs separate values.

WHERE id_campo not in (@idcamposexcluidos)

compiles to

WHERE id_campo not in ('817,803,495')

but it should be

WHERE id_campo not in ('817','803','495')

To overcome this either use dynamic SQL or in MySQL you could use FIND_IN_SET:

SET @idcamposexcluidos="817,803,495";
...
WHERE FIND_IN_SET(id_campo, @idcamposexcluidos) = 0

but using a function like FIND_IN_SET() can not make use of indexes.

More Related Contents:

  1. Empty IN clause parameter list in MySQL
  2. get first occurrence of last changed value of a column [closed]
  3. MySQL – Rows to Columns
  4. ROW_NUMBER() in MySQL
  5. multiple query same table but in different columns mysql
  6. How can I make SQL case sensitive string comparison on MySQL?
  7. 1052: Column ‘id’ in field list is ambiguous
  8. How can I temporarily disable a foreign key constraint in MySQL?
  9. How to do a batch insert in MySQL
  10. Count the occurrences of DISTINCT values
  11. Creating a “Numbers Table” in MySQL
  12. MySQL Group by SUM
  13. Cumulative sum over a set of rows in mysql
  14. Set NOW() as Default Value for datetime datatype?
  15. SQL: selecting rows where column value changed from previous row
  16. Why do results from a SQL query not come back in the order I expect?
  17. How to generate data in MySQL?
  18. MySQL combine two columns and add into a new column
  19. SQL query of multiple values in one cell
  20. How to skip certain database tables with mysqldump?
  21. MySQL error 1241: Operand should contain 1 column(s)
  22. How to set a MySQL row to READ-ONLY?
  23. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  24. Mysql: Order by like?
  25. Getting first day of the week in MySql using Week No
  26. How to format number with “.” as thousands separator and “,” as decimal separator?
  27. Using DISTINCT and COUNT together in a MySQL Query
  28. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment