MySql IN clause using variable

Question

I have a table called product with id and name columns. I want to search for product using multiple names. This list is coming from another select and I put it in a variable.

What I want to do is: SELECT the id of product that has the same name with the list elements.

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

EDIT: More detail information is presented below.

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

Here, I will iterate through the records of DataToProcess temporary table

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

@PierreDuc Good point! I thought I saw some PHP there earlier :-$ — Strawberry, Mar 18 '16 at 14:01
@GordonLinoff I already tried FIND_IN_SET() but it is very slow. That's why I want to use IN (which works fine from my php script, but I am getting rid of the php) — biniam, Mar 18 '16 at 14:14
Where is your "variable" coming from? Can't you just use a `JOIN` or a subquery? — gen_Eric, Mar 18 '16 at 14:23

mitkosoft · Accepted Answer · 2016-03-18T14:45:40.247

3

You can use FIND_IN_SET() for that:

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

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

Other option is to use REGEXP():

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

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

edited Mar 18 '16 at 14:45

answered Mar 18 '16 at 14:03

mitkosoft

5,262
1
13
31

I already tried FIND_IN_SET() but it is very slow. That's why I want to use IN (which works fine from my php script, but I am getting rid of the php) – biniam Mar 18 '16 at 14:14
is there any index(es) on your table? will be great to put some onto `name` column. – mitkosoft Mar 18 '16 at 14:33
yes I have index but I am querying a lot of data like: `@products` contains > 60 comma separated values and selecting this from > 500,000 records – biniam Mar 18 '16 at 14:51
Nice idea. But, it is still slow. The query took 1000 seconds to respond. – biniam Mar 18 '16 at 15:43
Hi I have a similar question, what is I want to define `products` list in Python code, how can I parameterize it in the MySQL in CLAUSE? https://stackoverflow.com/questions/67925365/parameterize-an-mysql-in-clause-in-python-code – wawawa Jun 11 '21 at 08:18

score 0 · Answer 2 · answered Mar 18 '16 at 14:01

0

You can just put it in one SELECT, if that is of course an option..

SELECT DISTINCT
   id 
FROM 
   product p
WHERE 
   p.name IN (SELECT include_names FROM include_this)

Or use the FIND_IN_SET function

answered Mar 18 '16 at 14:01

Poul Kruijt

69,713
12
145
149

MySql IN clause using variable

2 Answers2