在 MySQL 存储过程中创建临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5308969/
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
Creating temporary tables in MySQL Stored Procedure
提问by burntblark
The following procedure gives me an error when I invoke it using the CALL statement:
当我使用 CALL 语句调用它时，以下过程给了我一个错误：
CREATE DEFINER=`user`@`localhost` PROCEDURE `emp_performance`(id VARCHAR(10))
BEGIN
DROP TEMPORARY TABLE IF EXISTS performance;
CREATE TEMPORARY TABLE performance AS
SELECT time_in, time_out, day FROM attendance WHERE employee_id = id;
END
The error says "Unknown table 'performance' ".
错误显示“未知表'性能'”。
This is my first time actually using stored procedures and I got my sources from Google. I just cant figure out what I am doing wrong.
这是我第一次实际使用存储过程，我从谷歌那里得到了我的资源。我只是无法弄清楚我做错了什么。
回答by Jon Black
I've tidied it up a little for you and added example code. I always keep my parameter names the same as the fields they represent but prefix with p_ which prevents issues. I do the same with variables declared in the sproc body but prefix with v_.
我已经为您整理了一下并添加了示例代码。我总是让我的参数名称与它们代表的字段相同，但前缀为 p_ 以防止出现问题。我对 sproc 主体中声明的变量执行相同的操作，但前缀为 v_。
You can find another one of my examples here:
你可以在这里找到我的另一个例子：
Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
drop procedure if exists emp_performance;
delimiter #
create procedure emp_performance
(
in p_employee_id varchar(10)
)
begin
declare v_counter int unsigned default 0;
create temporary table tmp engine=memory select time_in, time_out
from attendance where employee_id = p_employee_id;
-- do stuff with tmp...
select count(*) into v_counter from tmp;
-- output and cleanup
select * from tmp order by time_in;
drop temporary table if exists tmp;
end#
delimiter ;
call emp_performance('E123456789');
回答by brooNo
By default MySQL config variable sql_notesis set to 1.
默认情况下，MySQL 配置变量sql_notes设置为 1。
That means that DROP TEMPORARY TABLE IF EXISTS performance;increments warning_countby one and you get a warning when a stored procedure finishes.
这意味着DROP TEMPORARY TABLE IF EXISTS performance;增量WARNING_COUNT一个，你会得到一个警告，当一个存储过程完成。
You can set sql_notesvariable to 0 in my.cnf or rewrite stored procedure like that:
您可以在 my.cnf中将sql_notes变量设置为 0 或像这样重写存储过程：
CREATE DEFINER=`user`@`localhost` PROCEDURE `emp_performance`(id VARCHAR(10))
BEGIN
SET @@session.sql_notes = 0;
DROP TEMPORARY TABLE IF EXISTS performance;
CREATE TEMPORARY TABLE performance AS
SELECT time_in, time_out, day FROM attendance WHERE employee_id = id;
SET @@session.sql_notes = 1;
END
