mysql怎么使用临时表（MySQL临时表）

mysql怎么使用临时表（MySQL临时表）临时表对主备复制的影响2.从5.7开始，引入一个临时文件表空间，专门用来存放临时文件的数据，无需再创建ibd文件了。1.普通表的table_def_key值是“库名 表名”；2.临时表table_def_key在“库名 表名”基础上又加入了“server_id thread_id”，所以同一session下临时表名可以和普通表名相同；同时也能解决主备复制不同session临时表同名问题。

临时表在使用上的几个特点：

• 建表语法 create temporary table ...；
• 一个临时表只能被创建它的session访问，对其他线程不可见；
• 临时表名可以与普通表相同；
• 一个session内有同名的临时表和普通表时，show create table 语句，以及增删改查语句（查询语句）访问的是临时表；
• show tables 命令不显示临时表；
• session结束时，会自动删除session内的临时表。

临时表的应用

• 复杂查询的优化过程（典型应用场景：分库分表系统的跨库查询）

为什么临时表可以重名

• 临时表结构存储：InnoDB表通过创建一个frm文件保存表结构定义，通过select @@tmpdir可以找到保存目录；它的命名规则是“#sql{进程 id}_{线程 id}_序列号”
• 临时表数据存储：

1.5.6及之前的版本，在MySQL临时文件目录下创建一个同frm文件相同前缀、以.ibd为后缀的文件，存放数据文件；

2.从5.7开始，引入一个临时文件表空间，专门用来存放临时文件的数据，无需再创建ibd文件了。

• MySQL维护数据表，除物理上要有文件外，内存里也有一套机制区别不同的表，每个表都对应一个table_def_key

1.普通表的table_def_key值是“库名 表名”；

2.临时表table_def_key在“库名 表名”基础上又加入了“server_id thread_id”，所以同一session下临时表名可以和普通表名相同；同时也能解决主备复制不同session临时表同名问题。

• 每个线程都有自己的临时表链表，每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在session结束时对链表里的每个临时表，执行“DROP TEMPORARY TABLE 表名”的操作。

临时表对主备复制的影响

• 在binlog_format=statement/mixed的时候，临时表的操作会记录到binlog中；在binlog_format=row时，跟临时表有关的语句不会被记录到binlog中。
• 在statement/mixed格式下，创建临时表的语句会传到备库执行，备库的同步线程会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续进行的。所以这个时候需要在主库上写一个DROP TEMPORARY TABLE 传给备库执行。
• 在binlog_format=row时，如果删除主库上的临时表，那么在传给备库之前，会将删除临时表的命令删除，并改写语句加上/* generated by server */ 说明了这是一个被服务端改写过的命令