英文:
Cast comma-separated string to multiple 'in' clause values
问题
将上面的SQL语句更改如下，以删除mytable中的三条记录：
SET @ids = '1,2,3';
SET @sql = CONCAT('DELETE FROM mytable WHERE id IN (', @ids, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这将通过动态构建SQL语句来解决错误，并且能够从mytable中删除id为1、2和3的记录。
英文:
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"
答案1
得分: 0
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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论