内存表和临时表的区别_MySQL中临时表的使用示例

这两天比较忙，公众号也停了几天，结果有读者催我再更新，嗯，说明还有人在关注，对己对人都有好处，很好。

今天要分享的是MySQL中的临时表，之前对临时表没有做过太多的研究，只是知道MySQL在一些特定的场景下会使用临时表来辅助group by等操作，今天就来认识一下临时表吧。

1.首先临时表是有层次的，并且是当前创建的表，在其他表中是看不到的。

1：

mysql> create temporary table test3 (id_tmp int)engine=innodb;
Query OK, 0 rows affected (0.00 sec)

2：

mysql> show create table test3\G
ERROR 1146 (42S02): Table 'test.test3' doesn't exist

2.临时表可以与正式表同名。

mysql> create table test2 (id int)engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> crehttp://www.cppcns.comate temporary table test2 (id_tmp int)engine=innodb;
Query OK, 0 rows affected (0.00 sec)

可见创建同名表test2并没有出错。

3、当数据库中存在物理表和临时表时，使用show table查看临时表的内容：

mysql> show create table test2\G
*************************** 1. row ***************************
    Table: test2
Create Table: CREATE TEMPORARY TABLE `test2` (
 `id_tmp` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4. 临时表drop掉之后，show table检查物理表的内容。

mysql> show tables like "test2";
+------------------------+
| Tables_in_test (test2) |
+------------------------+
| test2         |
+------------------------+
1 row in set (0.00 sec)
mysql> drop table test2;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables like "test2";
+------------------------+
| Tables_in_test (test2) |
+------------------------+
| test2         |
+------------------------+
1 row in set (0.00 sec)

5.show命令看不到临时表。

6、可以在不同的目录下创建同名的临时表。

7.临时表保存方法

在MySQL中，.frm用于保存表结构，.ibd用于保存表数据，.frm文件一般放在该参数指定的目录中，桌面平台的MySQL如下：

mysql> show variables like "%tmpdir%";
+--编程客栈-----------------+-------------------------------------------------+
| Variable_name   | Value                      |
+-------------------+-------------------------------------------------+
| innodb_tmpdir   |                         |
| slave_load_tmpdir | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp |
| tmpdir      | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp |
+-------------------+-------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

在.6版本中会生成一个.ibd文件来保存临时表。

在.7版本中引入了临时文件表空间，专门用于存储临时文件数据。

当我们使用不同的方法创建相同名称的临时表时，会发现临时表目录下存在不同名称的临时表文件：

这些临时表在内存中表现为链表，如果一张表包含两个临时表，那么MySQL会创建一个临时表链表来连接这两个临时表。在实际的操作逻辑中，如果我们执行一条SQL，MySQL会遍历临时表链表，检查是否存在该SQL中指定的表名的临时表，如果有，则优先操作临时表，如果没有，则操作普通的物理表。

8.主从复制中临时表的注意事项

由于临时表是分层的，退出时会被删除。但是主节点中没有显示临时表的操作，只能通过关闭临时表来删除。那么从节点如何知道什么时候删除临时表呢？

假设主节点执行如下SQL：

crete table tbl;
create temporary table tmp like tbl;
insert into tmp values (0,0);
insert into tbl select * from tmp;

=/mixed模式下，如果没有记录临时表相关操作，最后一条语句会因为找不到tmp表而报错，这种情况下MySQL会记录临时表操作，在主库关闭时会自动添加drop table的SQL语句，保证主从数据的一致性。

=row模式下，临时表相关的SQL不会记录在其中，因为row模式下，数据的每个字段都可以在中找到。对于最后的into语句，它会被记录为向tbl表中插入(0,0)。

=row模式，当主库主动使用drop table tmp命令删除临时表时，由于没有记录临时表的相关操作，所以这条记录也会被忽略。

9、从库上如何能同时存在不同线程的同名临时表？

我们知道临时表是有层次的，不同的临时表可以有相同的名字，那么在从数据库重放的时候，数据库如何知道这些同名的临时表是属于哪个事务的呢？

要理解这个概念，可以参考函数中形式参数和实际参数的概念。形式参数和实际参数可能具有相同的名称，但是在赋值时，它们的指针值是不同的。因此，对于具有相同名称的参数，编译器不会因为指针值不同而产生错误。

MySQL 对数据表进行维护，除了物理文件之外，在内存中也有机制来区分不同的表，每个表对应一个，这个的值由“数据库名+表名++”组成，因为不同，所以从数据库操作时不会发生冲突。

编程