在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在建表时进行检测是否存在同名表，有则报错。
网友评论