Create table variable in MySQL

mysql variables database-table
ANIL MANE picture ANIL MANE · Oct 6, 2009 · Viewed 117.5k times · Source

I need a table variable to store the particular rows from the table within the MySQL procedure. E.g. declare @tb table (id int,name varchar(200))

Is this possible? If yes how?

Answer

Preet Sangha picture Preet Sangha · Oct 6, 2009

They don't exist in MySQL do they? Just use a temp table:

CREATE PROCEDURE my_proc () BEGIN 

CREATE TEMPORARY TABLE TempTable (myid int, myfield varchar(100)); 
INSERT INTO TempTable SELECT tblid, tblfield FROM Table1; 

/* Do some more stuff .... */

From MySQL here

"You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)"

Related questions

MySQL: @variable vs. variable. What's the difference?

In another question I posted someone told me that there is a difference between: @variable and: variable in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?

Read More
How can I simulate an array variable in MySQL?

It appears that MySQL doesn't have array variables. What should I use instead? There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to …

Read More
PHP MySQL Query Where x = $variable

I have this code (I know that the email is defined) <?php $con=mysqli_connect($host,$user,$pass,$database); if (mysqli_connect_errno($con)) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $result = mysqli_query($con,"SELECT `note` …

Read More