删除MySQL中的记录,其中id为IN(@Variable)--(2,3,4)

2022-05-30 00:00:00 sql mysql sql-delete sql-in mysql-variables

是否有使用WHERE IN @VARIABLE删除记录的方法?

-- DEMO TABLE
CREATE TABLE people (
    id int AUTO_INCREMENT NOT NULL, 
    name varchar(100),
    age int,
    active smallint DEFAULT 0,
    PRIMARY KEY (id)
);

-- DEMO DATA
INSERT INTO people(id, name, age, active) 
VALUES
(1, 'Jon', 37, 1),
(2, 'Jack', 23, 0),
(3, 'Peter', 24, 0),
(4, 'Phil', 55, 0);

创建变量:

SELECT @REMOVE := GROUP_CONCAT(id) FROM people WHERE active < 1; -- (2,3,4)

我正在尝试从字符串中删除连接的变量。

DELETE FROM people WHERE id IN(@REMOVE); -- will delete only first id which is id nr 2

上述SQL只从列表中删除第一个元素。在本例中,List将包含:(2,3,4)。只有id=2的记录将被删除。ID为3、4的记录将保留在表中。见下图前后表:

我很清楚我可以使用以下两种解决方案之一:

子查询:

-- SOLUTION 1 - USEING NESTED SELECT SUB QUERY WITH AN ALIAS 
DELETE FROM people WHERE id IN(SELECT * FROM (SELECT id FROM people WHERE active < 1) as temp);

如果我们以后需要在不同的查询中运行相同子查询,并且希望在同一个表上运行INSERT、UPDATE或DELETE操作时保留原始输出,则解决方案1不理想。

临时表:

CREATE TEMPORARY TABLE temp_remove_people (id int NOT NULL PRIMARY KEY);
INSERT INTO temp_remove_people SELECT id FROM people WHERE active < 1;
DELETE FROM people WHERE id IN(SELECT id FROM temp_remove_people);

这将在同一会话中保留原始SELECT。

我想知道是否有可能以某种不同的方式使用串联变量以使其工作。


解决方案

建议FIND_IN_SET()会破坏任何使用索引优化该查询的机会。

您希望将变量视为离散整数列表,而不是恰好包含逗号和数字的字符串。这样,它就可以使用索引来优化匹配。

为此,您必须使用预准备语句:

SET @sql = CONCAT('DELETE FROM people WHERE id IN(', @REMOVE, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

相关文章