Deleting records in MySQL WHERE id IN (@VARIABLE) — (2,3,4)

Is there is a way to delete records using 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);

Create variable:

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

I’m trying to remove concatenated variables from string.

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

The above SQL removes only first element from the list. In this example, list will contain: (2,3,4). Only the record with id = 2 will be removed. Records with id 3, 4 will remain in the table. See the table before and after in the image below:

I am well aware that I could use on of two solutions like:

Subquery:

-- 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);

Solution 1 is not ideal if we need to run same subquery in different query at a later point, wanting to preserve the original output while running insert, update or delete operations on the same table.

or

Temp table:

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);

This will preseve original select within same session.

I would like to know if it is possible to use concatenated variable in some different way to make it work.

Answer

The suggestion of FIND_IN_SET() spoils any opportunity to optimize that query with an index.

You would like to treat the variable as a list of discrete integers, not as a string that happens to contain commas and digits. This way it can use an index to optimize the matching.

To do this, you have to use a prepared statement:

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