Assign query results to MySQL variable

Question

I'm querying a big mysql database with only read privileges, and I'd like to set some slow query results to a variable, 'foo', so I can use them again in other queries.

Basically, I want to have a variable for a cumbersome subquery, so I can reuse it without having the cost of running it every time I want to use it.

when I enter:

set @foo := (select *
            from table1 join table2 
            where bar = 0 
            group by id);

I get: ERROR 1241 (21000): Operand should contain 1 column(s) and if I restrict to 1 column, ERROR 1242 (21000): Subquery returns more than 1 row

Is there a way to store an array or a table in a variable? I don't have privileges to create temporary tables.

score 4 · Answer 1 · answered Dec 06 '12 at 05:01

4

it should be @ when you are doing in MySQL.

set @foo := (select *
            from table1 join table2 
            where bar = 0 
            group by id);

answered Dec 06 '12 at 05:01

John Woo

258,903
69
498
492

Thanks, but now I get: ERROR 1241 (21000): Operand should contain 1 column(s). When I restrict it to 1 column, I get: Subquery returns more than 1 row. – 5un5 Dec 06 '12 at 05:04
yes because `@foo` can only store one value. so in your `SELECT` statement, you should definitely know that it will return single value. – John Woo Dec 06 '12 at 05:05
Aah, I see. Thanks. Is there a way to store a table or at lease an array worth of query results in a variable? – 5un5 Dec 06 '12 at 05:09
hmmm, a subquery can atleast do `:D` – John Woo Dec 06 '12 at 05:10
Sounding better and better, Thanks – 5un5 Dec 06 '12 at 07:12

Saharsh Shah · Answer 2 · 2012-12-06T05:19:27.497

2

You can also try this:

You cant store full table in any variable but you can store column data in any variable using below query.

SELECT GROUP_CONCAT(col1 SEPARATOR '~~~'), GROUP_CONCAT(col2 SEPARATOR '~~~'), ... INTO @foo, @foo2, ...
FROM table1 JOIN table2 
WHERE bar = 0 
GROUP BY id;

or

select col1, col2, ... into @foo, @foo2, ...
from table1 join table2 
where bar = 0 
group by id

edited Dec 06 '12 at 05:19

answered Dec 06 '12 at 05:10

Saharsh Shah

28,687
8
48
83

Thanks-- way too much data to make this one work, but maybe for a smaller task. – 5un5 Dec 06 '12 at 05:11
It would be helpful, but I still get ERROR 1172 (42000): Result consisted of more than one row when I try it with:SELECT pid INTO @foo table1 join table 2 bar = 0 group by pid – 5un5 Dec 06 '12 at 05:23
Try group_concat function to get all rows data in one variable as I have already defined in my answer. – Saharsh Shah Dec 06 '12 at 05:35
From: SELECT GROUP_CONCAT(pid SEPARATOR '~~~') INTO @foo from table1 join table2 where bar=0; I got, ERROR 1172 (42000): Result consisted of more than one row. With 2 columns, I got the same error. – 5un5 Dec 06 '12 at 05:45
Please provide the output of this query (SELECT id, pid FROM table1 JOIN table2 WHERE bar = 0 GROUP BY id) and also provide the what the output you exactly want. – Saharsh Shah Dec 06 '12 at 05:50
The output would be a table that has 2 columns (id and pid) and 3000 rows. Each cell would have an id (or pid) number. The id and pid numbers range from 1 to 10 million. table1 and table 2 have millions of rows and about a dozen columns. (I'm simplifying, as the actual data is so much.) What I'm looking for is a way to hold the query output in a variable (rather than a subquery) because it takes about 5 minutes to run the subquery, and I have to run it a few dozen times in other various queries. If I can store the results (the 2x3000 table) then I save that 5 minutes in each query. – 5un5 Dec 06 '12 at 05:54
I want only sample data and output format what actually you want generate through query. So provide sample data such as 10 rows and what should be the output of that 10 rows. – Saharsh Shah Dec 06 '12 at 06:06
SELECT id, pid FROM table1 JOIN table2 WHERE bar = 0 GROUP BY id would generate 2 columns, each with numbers. You can paste this into a text editor to see the table: +------+-----+ | pid | id | +------+-----+ | 2345 | 678 | +------+-----+ | 2346 | 670 | +------+-----+ | 2355 | 378 | +------+-----+ | 21375| 5 | +------+-----+ | 25 | 608 | +------+-----+ – 5un5 Dec 06 '12 at 06:11
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/20677/discussion-between-5un5-and-saharsh-shah) – 5un5 Dec 06 '12 at 06:36

Assign query results to MySQL variable

2 Answers2