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)
3 Answers
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).
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;
2 Comments
David542
great, this is a really useful example. Do you know since what version the above dynamic sql is available from? I'm using 5.6.
Madhur Bhaiya
@David542 this is definitely available in version 5.5 and above. I think even version 4 and above has it as well.
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;
@studiosvariable about 5 times in the sql statement.