在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在建表时进行检测是否存在同名表,有则报错。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/618091/viewspace-2144497/,