MariaDB如何管理临时表? [英] How MariaDB administrates temporary tables?
问题描述
我想了解MariaDB如何管理临时表.例如，如果MariaDB具有相同的名称，那么它们如何处理临时表和非临时表.
I would like to understand how MariaDB administrates temporary tables. For example, how MariaDB cope with a temporary and non-temporary table if they have both the same name.
在下面的示例中，我创建了一个具有相同名称的临时和非临时表(步骤A)，并对该表进行了更新.现在，哪个更新了(B)?
In the example below I created a temporary and non-temporary table with the same name (step A) and did an update of the table. Now, which one was updated (B)?
当我放置非临时表(C1)时，该表没有内容，但仍然存在(C1).只有在第二个删除表之后，才会删除该表.
When I drop the non-temporary table (C1) the table has no content but still exists (C1). Only after a second dropping table will be dropped.
如果我删除临时表(C2)，也会发生同样的情况.
The same happens if I drop the temporary table (C2).
似乎如果有一个临时表和一个非临时表DROP TEMPORARY TABLE x0，并且DROP TABLE x0会删除该表的内容，但该表仍然存在.在第二次删除非临时表之后，该表也将被删除.
It seems like that if there are a temporary and a non-temporary table DROP TEMPORARY TABLE x0 and DROP TABLE x0 deletes the content of the table but the table still exists. After a second dropping of the non-temporary table the table is dropped.
但是如果我两次删除临时表(C3)，第二个DROP就会意识到没有临时表.
But if I drop twice the temporary table (C3) the second DROPrealizes that there is no temporary table.
很明显，在存在删除临时表的情况下，非临时表只会删除该表(不会删除).在第二次删除之后，该表将被删除.
Obviously, in the presence of a temporary table dropping the non-temporary table does only delete the table (and not dropping). After the second dropping the table is dropped.
是否有一种方法可以合理化这种行为? 教程提供了一些提示:
Is there a way to rationale this behaviour? The Tutorial gives some hints:
注意-临时表可以与现有的非临时表具有相同的名称，因为MariaDB会将其视为差异引用.
Note − Temporary tables are permitted to have the same name as an existing non-temporary table because MariaDB views it as a difference reference.
但这并不能确认下面显示的内容.
But this does not confirm what is shown below.
-- ****************************************************
-- (A) Create table
-- ****************************************************
DROP TABLE IF EXISTS x0;
DROP TEMPORARY TABLE IF EXISTS x0;
CREATE TABLE x0 (
id INTEGER
, v FLOAT
);
CREATE TEMPORARY TABLE x0 (
id INTEGER
, v FLOAT
);
INSERT INTO x0 VALUES
(1,1)
, (2,1)
;
SELECT * FROM x0;
-- ****************************************************
-- (B) Update
-- ****************************************************
UPDATE x0 SET v = 2 WHERE id = 1;
SELECT * FROM x0;
/*
+----+---+
| id | v |
+----+---+
| 1 | 2 |
| 2 | 1 |
+----+---+
*/
-- ****************************************************
-- (C1) Dropping non-temporary table (A->B->C1)
-- ****************************************************
DROP TABLE x0;
SELECT * FROM x0;
/*
+----+---+
| id | v |
+----+---+
*/
-- ****************************************************
-- (C2) Dropping temporary table (A->B->C2)
-- ****************************************************
DROP TEMPORARY TABLE x0;
SELECT * FROM x0;
/*
+----+---+
| id | v |
+----+---+
*/
DROP TABLE x0;
SELECT * FROM x0;
/* SQL Fehler (1146): Table 'test0.x0' doesn't exist */
-- ****************************************************
-- (C3) Dropping temporary table (A->B->C3)
-- ****************************************************
DROP TEMPORARY TABLE x0;
SELECT * FROM x0;
/*
+----+---+
| id | v |
+----+---+
*/
DROP TEMPORARY TABLE x0;
/* SQL Fehler (1051): Unknown table 'test0.x0' */
SELECT * FROM x0;
/*
+----+---+
| id | v |
+----+---+
*/
DROP TABLE x0;
SELECT * FROM x0;
/* SQL Fehler (1146): Table 'test0.x0' doesn't exist */
推荐答案
Thanks to Georg Richter who answered a similar question here I can explain what happened. The crucial sentence of his answer is:
如果临时表的名称与现有非临时表的名称相同，则临时表将隐藏非临时表的名称.
In case a temporary table has the same name as an existing non temporary table the temporary table will shadow the name of a non temporary table.
因此，只要存在临时表，就会对临时表执行所有select，insert，update.甚至drop table都会首先删除临时表，而drop temporary table只会删除临时表，并且在没有临时表时会给出错误.
So, as long a temporary table exists, all select, insert, update will be performed on the temporary table. Even drop table will drop first the temporary table whereas drop temporary table drops only temporary tables and gives an error when there is no temporary table.
所有这些说明了我的脚本发生了什么.当我在创建持久表和临时表后将其插入表中时，只有临时表获得了记录.第一次删除后，临时表被删除，并且select显示空的持久表.
All this explains what happened with my script. When I made the insert into the table after creating persistent and temporary table only temporary table got the records. After first dropping, the temporary table was dropped and select shows the empty persistent table.
这篇关于MariaDB如何管理临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
