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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Create table variable in MySQL

Examples related to mysql

Examples related to variables

Examples related to database-table