使用变量的 MySql IN 子句

Question

我有一个名为 Product 的表，其中包含 id 和 name 列。我想使用多个名称搜索产品。该列表来自另一个选择，我将其放入变量中。

我想要做的是:选择与列表元素同名的产品的id。

-- works but I have my values in a variable.
select distinct id from product where name IN ('Chair','Mattresses','Table');

-- doesn't work -  THIS IS WHAT I WANT
set @products = 'Chair,Mattresses,Table';
select distinct id from product where name IN (SELECT CONCAT("'", REPLACE(@products, ",", "','"), "'"));

-- doesn't work -- THIS IS ANOTHER ALTERNANATIVE FOR ME BUT IT DOESN'T WORK
set @products = 'Chair,Mattresses,Table';
set @commaSeparated = (SELECT CONCAT("'", REPLACE(@products, ",", "','"), "'"));
select distinct id from product where name IN (@commaSeparated);

编辑:下面提供了更多详细信息。

-- Temporary table to store the data from the FIRST query
CREATE TEMPORARY TABLE DataToProcess (
    productNames TEXT NOT NULL,
    locations TEXT NOT NULL
) CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Read data and store in Temporary table
INSERT INTO DataToProcess
    SELECT
      REPLACE(REPLACE(names, '\r\n', ','), "'", "\'") AS productNames,
      REPLACE(REPLACE(locations, '\r\n', ','), "'", "\'") AS locations
    FROM table1
      LEFT JOIN table2 USING (id)
    WHERE table1.startDate <= NOW()
          AND table2.endDate > DATE_ADD(NOW(), INTERVAL 15 MINUTE);

这里，我将遍历DataToProcess临时表的记录

-- Define curser_finished
DECLARE product_cursor_finished INTEGER DEFAULT 0;

-- declare cursor for DataToProcess
DECLARE product_cursor CURSOR FOR SELECT * FROM DataToProcess;

-- declare NOT FOUND handler for product_cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET product_cursor_finished = 1;

OPEN product_cursor;

get_productsAndLocations: LOOP

    FETCH product_cursor INTO products, locations;

    **** THIS IS WHERE I WANT TO USE `SELECT WHERE IN`

END LOOP get_ProductsAndLocations;

CLOSE product_cursor;

Answer 1

您可以使用FIND_IN_SET()来实现:

SET @products = 'Chair,Mattresses,Table';

SELECT DISTINCT
    `id`
FROM
    `product`
WHERE
    FIND_IN_SET(`name`, @products);

其他选项是使用REGEXP():

SET @products = 'Chair,Mattresses,Table';
SET @products = REPLACE(@products,",","|");

SELECT DISTINCT
    `id`
FROM
    `product`
WHERE
    `name` REGEXP @products