在 MySQL 中创建表变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1524858/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create table variable in MySQL
提问by ANIL MANE
回答by Preet Sangha
They don't exist in MySQL do they? Just use a temp table:
它们在 MySQL 中不存在，是吗？只需使用临时表：
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.)"
"创建表时可以使用 TEMPORARY 关键字。TEMPORARY 表仅对当前连接可见，并在连接关闭时自动删除。这意味着两个不同的连接可以使用相同的临时表名而不会相互冲突其他或具有同名的现有非临时表。（现有表是隐藏的，直到临时表被删除。）”
回答by Ewan Todd
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.
来自forge.mysql.com。另请参阅本文的临时表部分。
回答by numbsafari
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).
回答您的问题：不，MySQL 不支持 SQL Server (http://msdn.microsoft.com/en-us/library/ms188927.aspx) 提供的相同方式的表类型变量。Oracle 提供了类似的功能，但将它们称为 Cursor 类型而不是表类型 (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.
根据您的需要，您可以使用临时表模拟 MySQL 中的表/游标类型变量，其方式类似于 Oracle 和 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).
然而，临时表方法和表/游标类型变量方法之间有一个重要的区别，它有很多性能影响（这就是为什么 Oracle 和 SQL Server 提供此功能的原因超过临时表提供的功能表）。
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.
您可能想要尝试的是创建一个临时表，然后使用 LOAD DATA (http://dev.mysql.com/doc/refman/5.1/en/load-data.html) 命令将数据流式传输到临时表中桌子。然后，您可以将临时表的名称传递给您的存储过程。这仍然会导致对数据库服务器的两次调用，但如果您移动了足够多的行，则可能会节省一些费用。当然，这只有在更新目标表时在存储过程中执行某种逻辑时才有用。如果没有，您可能只想将数据直接加载到目标表中。
回答by Zafar Malik
If you don't want to store table in database then @Evan Todd already has been provided temporary table solution.
如果您不想将表存储在数据库中，那么@Evan Todd 已经提供了临时表解决方案。
But if you need that table for other users and want to store in db then you can use below procedure.
但是，如果您需要该表供其他用户使用并希望存储在 db 中，那么您可以使用以下过程。
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();
调用 sp_variable_table();
You can check new table after executing below command-
您可以在执行以下命令后检查新表 -
use test;show tables like ‘%zafar%'; — test is here ‘database' name.
使用测试；显示像“%zafar%”这样的表格；— 测试在这里是“数据库”名称。
You can also check more details at below path-
您还可以在以下路径中查看更多详细信息-
http://mydbsolutions.in/how-can-create-a-table-with-variable-name/
http://mydbsolutions.in/how-can-create-a-table-with-variable-name/
回答by Justin Levene
MYSQL 8 does, in a way:
MYSQL 8 在某种程度上做到了：
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.
MYSQL 8 支持 JSON 表，因此您可以将结果加载到 JSON 变量中，并使用 JSON_TABLE() 命令从该变量中进行选择。
