這兩天事情稍微有點多，公眾號也停止更新了幾天，結果有讀者催更了，也是，說明還是有人關注，利己及人，挺好。

    今天分享的內容是MySQL中的臨時表，對于臨時表，之前我其實沒有過多的研究，只是知道MySQL在某些特定場景下會使用臨時表來輔助進行group by等一些列操作，今天就來認識下臨時表吧。

1、首先。臨時表是session級別的，當前session創建的表，在其他session中看不到。

session 1：

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

session 2：

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

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

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查看的是臨時表的內容：

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

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的如下：

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：

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 內存臨時表的資料請關注腳本之家其它相關文章！