美文网首页
【MySQL】浅析临时表和永久表同名引发的问题

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

作者: DATA无界 | 来源:发表于2017-09-04 18:49 被阅读0次

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

    相关文章

      网友评论

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

          本文链接:https://www.haomeiwen.com/subject/swpddxtx.html