mysql 查询临时表列名_为什么mysql会缓存被删除的临时表的列名？

我已经将我的问题简化为这个简单的SP。列名在最后被缓存在SELECT *中。我不知道为什么或如何阻止它。我尝试添加SQL_NO_CACHE，但这没有什么区别。

DROP TABLE IF EXISTS foo;

CREATE TABLE foo(

col1 int,

col2 int);

INSERT INTO foo VALUES(1,2),(3,4),(5,6);

DROP PROCEDURE IF EXISTS mysp;

DELIMITER ;;

CREATE DEFINER=root@localhost PROCEDURE mysp(c INT)

BEGIN

DROP TABLE IF EXISTS mydata;

SET @mycol='col1';

IF c > 0 THEN SET @mycol:='col2';

END IF;

SET @s=CONCAT('CREATE TEMPORARY TABLE mydata AS SELECT ', @mycol, ' FROM foo');

PREPARE stmt FROM @s;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

-- The following select call fails on 2nd and subsequent executions of the SP

SELECT SQL_NO_CACHE * FROM mydata;

SELECT "Please see new temp table mydata" as Result;

END ;;

DELIMITER ;版

mysql> SELECT VERSION();

+------------+

| VERSION() |

+------------+

| 5.5.15-log |

+------------+

1 row in set (0.00 sec)首次运行如预期般运作良好

mysql> CALL mysp(0);

+------+

| col1 |

+------+

| 1 |

| 3 |

| 5 |

+------+

3 rows in set (0.17 sec)

+----------------------------------+

| Result |

+----------------------------------+

| Please see new temp table mydata |

+----------------------------------+

1 row in set (0.17 sec)

Query OK, 0 rows affected (0.17 sec)现在，如果我尝试使用另一列再次运行它

mysql> CALL mysp(1);

ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col1' in 'field list'

mysql> SELECT @mycol;

+--------+

| @mycol |

+--------+

| col2 |

+--------+

1 row in set (0.00 sec)如果我再次重新创建存储过程它的作品

mysql> CALL mysp(1);

+------+

| col2 |

+------+

| 2 |

| 4 |

| 6 |

+------+

3 rows in set (0.18 sec)

+----------------------------------+

| Result |

+----------------------------------+

| Please see new temp table mydata |

+----------------------------------+

1 row in set (0.18 sec)

Query OK, 0 rows affected (0.18 sec)但是，如果我尝试切换回第一列 - 即使我先尝试删除临时表 - 它仍然不起作用

mysql> CALL mysp(0);

ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col2' in 'field list'

mysql> DROP TABLE mydata;

Query OK, 0 rows affected (0.03 sec)

mysql> CALL mysp(0);

ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col2' in 'field list'

mysql>*附加信息请求eggyal。另外我在另一个mysql版本上尝试了这个结果。 *

mysql> CALL mysp(1);

+------+

| col2 |

+------+

| 2 |

| 4 |

| 6 |

+------+

3 rows in set (0.20 sec)

+----------------------------------+

| Result |

+----------------------------------+

| Please see new temp table mydata |

+----------------------------------+

1 row in set (0.20 sec)

Query OK, 0 rows affected (0.20 sec)

mysql> describe mydata;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| col2 | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> CALL mysp(0);

ERROR 1054 (42S22): Unknown column 'test.mydata.col2' in 'field list'

mysql> describe mydata;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| col1 | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)有趣的修补程序开发 - 将最后几行更改为准备好的语句 - 但使用与以前完全相同的查询。

-- The following select call fails on 2nd and subsequent executions of the SP

PREPARE stmt FROM 'SELECT SQL_NO_CACHE * FROM mydata';

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SELECT "Please see new temp table mydata" as Result;