【MySQL】浅析临时表和永久表同名引发的问题

在ORACLE中，无论是永久表还是临时表，在同一个Schema中，只能有一个表名，不会存在临时表和永久表同名的问题。 而在MySQL中却不是这样的。
在执行MYSQL默认建表语句时，不会去检测当前是否有同名的临时表和永久表。

一、来看如下实验：
1、先创建一张普通表

mysql> create table a ( a int ,b int);
Query OK, 0 rows affected (0.03 sec)

mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
| b     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2、插入测试数据

mysql> insert into a select 1,2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into a select 3,4;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from a;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

3、再创建一张同名的临时表（注意此时并未报错）

mysql> create temporary table a (t int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a select 9;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

4、现在当前会话中就存在了2张同名的a表。
那么问题来了，如果此时查询表a的数据，MYSQL返回的会是永久表的数据还是临时表的数据呢？
继续实验：

mysql> select * from a;
+------+
| t    |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| t     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

5、可见，如果此时查询a表，那么将返回的数据将是临时表a的数据，而查询不到永久表a的数据。
那么如何查询表a的数据呢？ 如果当前会话查不到，尝试新开一个会话查询下
新开会话查询：

mysql> select * from a;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

二、分析：

出现这种情况的原因在于：

在MySQL中存在两种临时表 外部临时表和内部临时表 。

通过CREATE TEMPORARY TABLE 创建的临时表被称为外部临时表。

这种临时表只对当前会话用户可见，当前会话结束的时候，该临时表会自动关闭。

这种临时表的表名与普通表可以同名，但是会导致同名后普通表将对当前会话不可见，直到临时表被删除。

内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种内部表对普通用户来说是不可见的。

三、解决方法：

1、定制开发和运维建表规范，采用不同表名，如所有临时表命名必须以tmp开头；

2、建表时统一采用建表参数【IF NOT EXISTS】，让MYSQL在建表时进行检测是否存在同名表，有则报错。