在MySQL存储过程中创建临时表

Question

以下过程在使用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

错误信息显示"Unknown table 'performance' "，即“未知表'performance'”。

这是我第一次使用存储过程，我从谷歌上找到了一些资源。但我无法弄清楚自己错在哪里。

- burntblark

Answer 1

我稍微整理了一下，并添加了示例代码。我通常会将参数名与它们所代表的字段保持相同，但是在前面加上p_以避免出现问题。我在存储过程体中声明变量时也会这样做，但是前缀是v_。

你可以在这里找到我的另一个示例：

从MySQL中的分层数据生成基于深度的树（无CTE）

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');

- Jon Black

---

我创建临时表的目的是为了之后查询它。我刚刚尝试了你的代码，你创建的 tmp 表无法用于查询。相反，它给我返回了一个结果集。因此，该过程只是准备员工出勤情况。 - burntblark

你可以按照你建议的做，但是这个表只能在创建它或调用存储过程的连接中使用。我不建议采用这种方法，但如果你能详细说明你想做什么，我可能会有其他的想法。你也可以查看这个链接：http://pastie.org/1673574。 - Jon Black

你如何测试成员资格？如果临时表只有作者的ID，并且我想查询类似于“select * from books where author in (tmp)”这样的内容，该怎么办？MySQL会返回错误：“未知列tmp”。 - Uri London

关于并行性的另一个问题：似乎'tmp'是一个全局对象（或具有全局范围）。例如，如果我没有'删除临时...'这一行，则第二次执行将无法创建tmp。那么...当过程在并行执行两次时，服务器如何处理这种情况？ - Uri London

Answer 2

默认情况下，MySQL配置变量sql_notes设置为1。

这意味着DROP TEMPORARY TABLE IF EXISTS performance;会将warning_count增加一，并在存储过程完成时发出警告。

您可以在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

- brooNo