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.

Solution 1

it should be @ when you are doing in MySQL.

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

Solution 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

Comments

[5un5]

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.

[John Woo]

yes because @foo can only store one value. so in your SELECT statement, you should definitely know that it will return single value.

[5un5]

Aah, I see. Thanks. Is there a way to store a table or at lease an array worth of query results in a variable?

[John Woo]

hmmm, a subquery can atleast do :D

[5un5]

Thanks-- way too much data to make this one work, but maybe for a smaller task.

[5un5]

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

[Saharsh Shah]

Try group_concat function to get all rows data in one variable as I have already defined in my answer.

[5un5]

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.

[Saharsh Shah]

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.

[5un5]

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.

[Saharsh Shah]

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.

[5un5]

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]

let us continue this discussion in chat

[5un5]

Sounding better and better, Thanks