MySQL中临时表的使用示例

    这两天事情稍微有点多，公众号也停止更新了几天，结果有读者催更了，也是，说明还是有人关注，利己及人，挺好。

    今天分享的内容是MySQL中的临时表，对于临时表，之前我其实没有过多的研究，只是知道MySQL在某些特定场景下会使用临时表来辅助进行group by等一些列操作，今天就来认识下临时表吧。

1、首先。临时表是session级别的，当前session创建的表，在其他session中看不到。

session 1：

?

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

session 2：

?

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

2、临时表在session中，可以和正式的表重名。

?

1 2 3 4 5

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

   可以看到，创建同名的test2表的时候，并没有出现报错的情况。

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

?

1 2 3 4 5 6 7

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 create table查看的是物理表的内容。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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 tables命令，不能看到临时表。

6、不同的session中可以创建同名的临时表。

7、临时表保存方法

   在MySQL中，使用.frm来保存表结构，而使用.ibd来保存表数据，.frm文件一般是放在tmpdir这个参数指定的目录下面的。台式机windows平台下MySQL的如下：

?

1 2 3 4 5 6 7 8 9

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)

MySQL5.6版本下，会生成一个.ibd的文件来保存临时表。

MySQL5.7版本下，引入了临时文件表空间，专门用来存放临时文件的数据。

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

这些临时表在内存中是通过链表的方式来表示的，如果一个session中包含两个临时表，MySQL会创建一个临时表的链表，将这两个临时表连接起来，实际的操作逻辑中，如果我们执行了一条SQL，MySQL会遍历这个临时表的链表，检查是否有这个SQL中指定表名字的临时表，如果有临时表，优先操作临时表，如果没有临时表，则操作普通的物理表。

8、临时表在主从复制中的注意点

   临时表由于是session级别的，那么在session退出的时候，是会删除临时表的。但是主节点中并没有对临时表进行显示的操作，而是关闭session即可删除，那么从节点如何知道什么时候才能删除临时表呢？

假设主节点进行如下SQL：

?

1 2 3 4

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

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

   在binlog=row模式下，跟临时表有关的SQL，都不会记录到binlog里面，因为row模式下，数据的每个字段在binlog中都能找到，针对最后一个insert into select语句，binlog中会记录成往tbl表中插入(0,0)这条记录。

    binlog=row模式下，当主库上主动使用drop table tmp的命令来删除临时表的时候，此时因为binlog中不记录临时表的相关操作，所以这条记录也会被忽略。

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

   我们知道临时表是session级别的，而且不同session之间的临时表可以重名，在从库进行binlog回放的时候，从库是如何知道这些重名的临时表分别属于哪个事务的呢？

 这个概念的理解可以参考函数中的形参和实参的概念，形参和实参可能有同样的名字，进行赋值的时候，二者的指针值是不一样的，所以同名的参数，对编译器来讲，由于指针值不一样，所以不会出现错误。

   MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。而这个table_def_key的值是由"库名字+表名字+server_id+thread_id"组成的，因为thread_id不同，所以在从库中进行操作的时候，是不会冲突的。

以上就是详解MySQL中的内存临时表的详细内容，更多关于MySQL 内存临时表的资料请关注服务器之家其它相关文章！

原文链接：https://cloud.tencent.com/developer/article/1651591