MySQL存储过程记住过时的临时表模式，导致未知列错误

Question

在下面的 MySQL 代码中，前两个 block 删除并创建一个临时表 _temp(具有不同的列标签)并从中select * 没有问题。然后，我创建了一个执行相同操作的存储过程(即 select * from _temp)，它第一次工作，但第二次不工作，失败了

ERROR 1054 (42S22): Unknown column 'test._temp.f' in 'field list'

看起来 select * from _temp 本身可以正确处理表列中的更改，但在存储过程调用中会记住之前的列名称。我做错了什么，还是有解决方法？

MySQL代码

drop temporary table if exists _temp;
create temporary table _temp select 'first' as f;
select * from _temp;

drop temporary table if exists _temp;
create temporary table _temp select 'second' as s;
select * from _temp;

drop procedure if exists selectTemp;
create procedure selectTemp()
select * from _temp;

drop temporary table if exists _temp;
create temporary table _temp select 'first' as f;
call selectTemp();

drop temporary table if exists _temp;
create temporary table _temp select 'second' as s;
call selectTemp();

成绩单

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.2

mysql> source temp.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+-------+
| f     |
+-------+
| first |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

+--------+
| s      |
+--------+
| second |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+-------+
| f     |
+-------+
| first |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

ERROR 1054 (42S22): Unknown column 'test._temp.f' in 'field list'

Answer 1

将其简化为一个最小的工作示例并提炼出基本元素、搜索错误报告后，这就变得容易多了。事实证明，早在 2005 年就有这样的报道:

• Bug #12257 SELECT * inside PROCEDURE gives "Unknown column" on second loop if tbl changed

一些标记为重复的错误实际上更符合示例的思路:

• Bug #15766 select * from table inside stored procedure uses old field names
• Bug #49333 Unknown column 'test.TEMPTABLE.column1' in 'field list'
• Bug #62406 new cursor, on table with same name but different structure as used before fails

该错误已关闭，但显然尚未修复，尽管 5.6 提到了该行为。来自错误报告中的评论:

Noted in 5.6.6 changelog.

"Unknown column" errors or bad data could result from changing the set of columns in a table used within a stored program between executions of the program or while the table was used within a program loop.