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

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

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

1. mysql> create table a ( a int ,b int);
2. Query OK, 0 rows affected (0.03 sec)
3.  
4. mysql> desc a;
5. +-------+---------+------+-----+---------+-------+
6. | Field | Type | Null | Key | Default | Extra |
7. +-------+---------+------+-----+---------+-------+
8. | a | int(11) | YES | | NULL | |
9. | b | int(11) | YES | | NULL | |
10. +-------+---------+------+-----+---------+-------+
11. 2 rows in set (0.00 sec)

 2、插入测试数据
 

1. mysql> insert into a select 1,2;
2. Query OK, 1 row affected (0.02 sec)
3. Records: 1 Duplicates: 0 Warnings: 0
4.  
5. mysql> insert into a select 3,4;
6. Query OK, 1 row affected (0.02 sec)
7. Records: 1 Duplicates: 0 Warnings: 0
8.  
9. mysql> select * from a;
10. +------+------+
11. | a | b |
12. +------+------+
13. | 1 | 2 |
14. | 3 | 4 |
15. +------+------+
16. 2 rows in set (0.00 sec)

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

1. mysql> create temporary table a (t int);
2. Query OK, 0 rows affected (0.00 sec)
3.  
4. mysql> insert into a select 9;
5. Query OK, 1 row affected (0.00 sec)
6. Records: 1 Duplicates: 0 Warnings: 0

 

4、现在当前会话中就存在了2张同名的a表。

 那么问题来了，如果此时查询表a的数据，MYSQL返回的会是永久表的数据还是临时表的数据呢？  
继续实验：
 

1. mysql> select * from a;
2. +------+
3. | t |
4. +------+
5. | 9 |
6. +------+
7. 1 row in set (0.00 sec)
8.  
9. mysql> desc a;
10. +-------+---------+------+-----+---------+-------+
11. | Field | Type | Null | Key | Default | Extra |
12. +-------+---------+------+-----+---------+-------+
13. | t | int(11) | YES | | NULL | |
14. +-------+---------+------+-----+---------+-------+
15. 1 row in set (0.00 sec)

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

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

 
二、分析：
 出现这种情况的原因在于： 
在MySQL中存在两种临时表 外部临时表和内部临时表 。

通过CREATE TEMPORARY TABLE 创建的临时表被称为外部临时表。  
这种临时表只对当前会话用户可见，当前会话结束的时候，该临时表会自动关闭。
这种临时表的表名与普通表可以同名，但是会导致同名后普通表将对当前会话不可见，直到临时表被删除。
   
内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种内部表对普通用户来说是不可见的。    

三、解决方法：  
1、定制开发和运维建表规范，采用不同表名，如所有临时表命名必须以tmp开头；
2、建表时统一采用建表参数【IF NOT EXISTS】，让MYSQL在建表时进行检测是否存在同名表，有则报错。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/618091/viewspace-2144497/，