CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110562

How to set a local list/tuple variable in mysql

Is there a way to do the following in mysql?

SET @studios = ('Disney', 'Warner Bros.', 'Fox');
SELECT * FROM movies WHERE provider IN @studios;

When I try doing the above I get the error:

Operand should contain 1 column(s)

Upvotes: 5

Views: 6725

Answers (3)

ixe013
ixe013

Reputation: 10191

You could concatenate your list to a string, and use FIND_IN_SET as your criteria. Might not be super efficient, but makes the code quite easy to read and maintain.

Looks like this:

SET @studios = CONCAT_WS(',',
  'Disney',
  'Warner Bros.',
  'Fox'
);
SELECT * FROM movies 
WHERE FIND_IN_SET(provider, @studios) <> 0;

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15961

The error is coming from your initial assignment. You cannot assign lists to variables.

The only way of doing this in MySQL is to either create a temp table to hold the values, and then do ... IN (SELECT someVal FROM thatTemp), or to dynamically create the query with the values directly in the query string.

Example temp table creation:

CREATE TEMPORARY TABLE `someTemp` ( someVal VARCHAR(16) );
INSERT INTO `someTemp` (someVal) VALUES ('a'), ('b'), ('c');
SELECT * FROM myTable WHERE myField IN (SELECT someVal FROM someTemp);
DELETE TEMPORARY TABLE `someTemp`;

Alternatively, there is also FIND_IN_SET, which could be used like this:

SET @list = 'a,b,c';
SELECT * FROM myTable WHERE FIND_IN_SET(myField, @list) <> 0;

but this method probably has extremely poor performance (and may not be useable if your "myField" values may contain commas).

Upvotes: 3

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

It is not possible to set a tuple/list/array in a user-defined variable in MySQL. You can use Dynamic SQL for the same:

-- we use single quotes two times to escape it
SET @studios = '(''Disney'', ''Warner Bros.'', ''Fox'')';

-- generate the query string
SET @query = CONCAT('SELECT * FROM movies WHERE provider IN ', @studios);

-- prepare the query
PREPARE stmt FROM @query;

-- execute it
EXECUTE stmt;

-- deallocate it
DEALLOCATE PREPARE stmt;

Upvotes: 2

Related Questions