MySQL中臨時表的使用示例

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

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

1、首先。臨時表是session級別的，當(dāng)前session創(chuàng)建的表，在其他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)

   可以看到，創(chuàng)建同名的test2表的時候，并沒有出現(xiàn)報錯的情況。

3、當(dāng)數(shù)據(jù)庫中物理表和臨時表的時候，使用show create table查看的是臨時表的內(nèi)容：

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查看的是物理表的內(nèi)容。

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中可以創(chuàng)建同名的臨時表。

7、臨時表保存方法

   在MySQL中，使用.frm來保存表結(jié)構(gòu)，而使用.ibd來保存表數(shù)據(jù)，.frm文件一般是放在tmpdir這個參數(shù)指定的目錄下面的。臺式機(jī)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版本下，引入了臨時文件表空間，專門用來存放臨時文件的數(shù)據(jù)。

當(dāng)我們使用不同的session來創(chuàng)建相同名稱的臨時表的時候，會發(fā)現(xiàn)臨時表的目錄下面存在不同名稱的臨時表文件：

這些臨時表在內(nèi)存中是通過鏈表的方式來表示的，如果一個session中包含兩個臨時表，MySQL會創(chuàng)建一個臨時表的鏈表，將這兩個臨時表連接起來，實(shí)際的操作邏輯中，如果我們執(zhí)行了一條SQL，MySQL會遍歷這個臨時表的鏈表，檢查是否有這個SQL中指定表名字的臨時表，如果有臨時表，優(yōu)先操作臨時表，如果沒有臨時表，則操作普通的物理表。

8、臨時表在主從復(fù)制中的注意點(diǎn)

   臨時表由于是session級別的，那么在session退出的時候，是會刪除臨時表的。但是主節(jié)點(diǎn)中并沒有對臨時表進(jìn)行顯示的操作，而是關(guān)閉session即可刪除，那么從節(jié)點(diǎn)如何知道什么時候才能刪除臨時表呢？

假設(shè)主節(jié)點(diǎn)進(jìn)行如下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模式下，如果不記錄臨時表相關(guān)操作的binlog，則最后一條insert語句會報錯。因?yàn)檎也坏絫mp這個表。這種情況下，MySQL的binlog中會記錄臨時表的操作，當(dāng)主庫的session關(guān)閉的時候，自動的在binlog中添加drop temporary table的SQL語句，從而保證主從數(shù)據(jù)的一致。

   在binlog=row模式下，跟臨時表有關(guān)的SQL，都不會記錄到binlog里面，因?yàn)閞ow模式下，數(shù)據(jù)的每個字段在binlog中都能找到，針對最后一個insert into select語句，binlog中會記錄成往tbl表中插入(0,0)這條記錄。

    binlog=row模式下，當(dāng)主庫上主動使用drop table tmp的命令來刪除臨時表的時候，此時因?yàn)閎inlog中不記錄臨時表的相關(guān)操作，所以這條記錄也會被忽略。

9、不同線程的同名臨時表在從庫上如何同時存在？

   我們知道臨時表是session級別的，而且不同session之間的臨時表可以重名，在從庫進(jìn)行binlog回放的時候，從庫是如何知道這些重名的臨時表分別屬于哪個事務(wù)的呢？

 這個概念的理解可以參考函數(shù)中的形參和實(shí)參的概念，形參和實(shí)參可能有同樣的名字，進(jìn)行賦值的時候，二者的指針值是不一樣的，所以同名的參數(shù)，對編譯器來講，由于指針值不一樣，所以不會出現(xiàn)錯誤。

   MySQL維護(hù)數(shù)據(jù)表，除了物理上要有文件外，內(nèi)存里面也有一套機(jī)制區(qū)別不同的表，每個表都對應(yīng)一個table_def_key。而這個table_def_key的值是由"庫名字+表名字+server_id+thread_id"組成的，因?yàn)閠hread_id不同，所以在從庫中進(jìn)行操作的時候，是不會沖突的。

以上就是詳解MySQL中的內(nèi)存臨時表的詳細(xì)內(nèi)容，更多關(guān)于MySQL 內(nèi)存臨時表的資料請關(guān)注腳本之家其它相關(guān)文章！