Create table variable in MySQL

Question

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?

Topographer

Actually, Aleksey is right, you can store a table in a variable, but you'll need to use a function, not a procedure. MySQL docs are very clear on that. – Sufficient

Not the solution to the question but another simple alternative. if the table is a single column table you want then derive a string param by concatenating the values and extract inside the SP. – Wish

Answer 1

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.)"

Sf

What if multiple user try to run same procedure, will it create any concurrency problem ? – Topographer

temporary tables are session specific and not user specific – Pampero

Depending on how you're application is using the session, it could be multithreaded using the same DB session. In that scenario wouldn't you run into concurrency problems? i.e. Thread A started adding records and then so did Thread B and now the table is corrupt because Thread A needed only the records it inserted, not also Thread B's @Pampero – Danzig

Answer 2

Perhaps a temporary table will do what you want.

CREATE TEMPORARY TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL
, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MEMORY;

INSERT INTO SalesSummary
(product_name, total_sales, avg_unit_price, total_units_sold)
SELECT 
  p.name
  , SUM(oi.sales_amount)
  , AVG(oi.unit_price)
  , SUM(oi.quantity_sold)
FROM OrderItems oi
INNER JOIN Products p
    ON oi.product_id = p.product_id
GROUP BY p.name;

/* Just output the table */
SELECT * FROM SalesSummary;

/* OK, get the highest selling product from the table */
SELECT product_name AS "Top Seller"
FROM SalesSummary
ORDER BY total_sales DESC
LIMIT 1;

/* Explicitly destroy the table */
DROP TABLE SalesSummary; 

From forge.mysql.com. See also the temporary tables piece of this article.

Cumulus

Answer 3

TO answer your question: no, MySQL does not support Table-typed variables in the same manner that SQL Server (http://msdn.microsoft.com/en-us/library/ms188927.aspx) provides. Oracle provides similar functionality but calls them Cursor types instead of table types (http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems012.htm).

Depending your needs you can simulate table/cursor-typed variables in MySQL using temporary tables in a manner similar to what is provided by both Oracle and SQL Server.

However, there is an important difference between the temporary table approach and the table/cursor-typed variable approach and it has a lot of performance implications (this is the reason why Oracle and SQL Server provide this functionality over and above what is provided with temporary tables).

Specifically: table/cursor-typed variables allow the client to collate multiple rows of data on the client side and send them up to the server as input to a stored procedure or prepared statement. What this eliminates is the overhead of sending up each individual row and instead pay that overhead once for a batch of rows. This can have a significant impact on overall performance when you are trying to import larger quantities of data.

A possible work-around:

What you may want to try is creating a temporary table and then using a LOAD DATA (http://dev.mysql.com/doc/refman/5.1/en/load-data.html) command to stream the data into the temporary table. You could then pass them name of the temporary table into your stored procedure. This will still result in two calls to the database server, but if you are moving enough rows there may be a savings there. Of course, this is really only beneficial if you are doing some kind of logic inside the stored procedure as you update the target table. If not, you may just want to LOAD DATA directly into the target table.

Procora

Answer 4

MYSQL 8 does, in a way:

MYSQL 8 supports JSON tables, so you could load your results into a JSON variable and select from that variable using the JSON_TABLE() command.

Pericline

Answer 5

If you don't want to store table in database then @Evan Todd already has been provided temporary table solution.

But if you need that table for other users and want to store in db then you can use below procedure.

Create below ‘stored procedure’:

————————————

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_variable_table`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_variable_table`()
BEGIN

SELECT CONCAT(‘zafar_’,REPLACE(TIME(NOW()),’:',’_')) INTO @tbl;

SET @str=CONCAT(“create table “,@tbl,” (pbirfnum BIGINT(20) NOT NULL DEFAULT ’0′, paymentModes TEXT ,paymentmodeDetails TEXT ,shippingCharges TEXT ,shippingDetails TEXT ,hypenedSkuCodes TEXT ,skuCodes TEXT ,itemDetails TEXT ,colorDesc TEXT ,size TEXT ,atmDesc TEXT ,promotional TEXT ,productSeqNumber VARCHAR(16) DEFAULT NULL,entity TEXT ,entityDetails TEXT ,kmtnmt TEXT ,rating BIGINT(1) DEFAULT NULL,discount DECIMAL(15,0) DEFAULT NULL,itemStockDetails VARCHAR(38) NOT NULL DEFAULT ”) ENGINE=INNODB DEFAULT CHARSET=utf8″);
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT ‘Table has been created’;
END$$

DELIMITER ;

———————————————–

Now you can execute this procedure to create a variable name table as per below-

call sp_variable_table();

You can check new table after executing below command-

use test;show tables like ‘%zafar%’; — test is here ‘database’ name.

You can also check more details at below path-

http://mydbsolutions.in/how-can-create-a-table-with-variable-name/

Novercal