Search code examples
amazon-auroramysql-8.0

Cast comma-separated string to multiple 'in' clause values

set @ids = '1,2,3';
delete from mytable where id in (@ids);

The sql statement above gives this error:

Truncated incorrect DOUBLE value: '1,2,3'

How can I cast @ids, or write the query in another way, so that it deletes the three records from mytable?

Sql mode is set to "IGNORE_SPACE,STRICT_TRANS_TABLES"

Solution

  • In end I went with a prepared statement.

    set @ids = '1,2,3';
set @query = concat('delete from mytable where id in (',@ids,');');
PREPARE sql_query from @query;
EXECUTE sql_query;