MariaDB:如果存在一个具有相同名称的持久表，我该如何选择一个临时表? [英] MariaDB: How can I select a temporary table if there is a persistent table with the same name?

问题描述

我创建了一个持久表 temp 和一个临时表 temp ，即它们的名称相同.如何将select/update/insert专门用于持久表或临时表?我该如何区分它们?

I created a persistent table temp and a temporary table temp, that is, both have the same name. How can I use select/update/insert specifically to the persistent or the temporary table? How can I differ between them?

MariaDB 教程说:

MariaDB Tutorial says:

注意-临时表可以与现有的非临时表具有相同的名称，因为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.

因此，我认为应该可以引用这些表之一.这个问题与我在SO中提出的问题有关，但又往后退了一步.

So, I suppose it should be possible to refer to one of these tables. This question is related to this question I posed in SO, but goes one step back.

推荐答案

如果临时表的名称与现有非临时表的名称相同，则临时表将隐藏非临时表的名称.

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.

这意味着在SQL语句中，您将无法引用非临时表.

That means in a SQL statement you will not be able to reference the non temporary table.

一种解决方法是，在创建临时表之前在非临时表上创建视图，因为该视图在内部保留对非临时表的引用:

A work around would be, to create a view on a non temporary table before creating the temporary table, since the view internally keeps the reference to the non temporary table:

CREATE TABLE t1 (a VARCHAR(100));
INSERT INTO t1 VALUES ("foo");
CREATE VIEW v_t1 AS SELECT a FROM t1;
CREATE TEMPORARY TABLE t1 (b VARCHAR(100));
INSERT INTO t1 VALUES ("bar");
SELECT * FROM v_t1;
SELECT * FROM t1;

这篇关于MariaDB:如果存在一个具有相同名称的持久表，我该如何选择一个临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！