寫在前面

本文一起看下MySQL的臨時(shí)表。

1：什么是臨時(shí)表

通過(guò)create temporary table t語(yǔ)句創(chuàng)建的表，就是臨時(shí)表，臨時(shí)表的臨時(shí)體現(xiàn)在其生命周期是和會(huì)話一樣的，當(dāng)會(huì)話結(jié)束，即連接關(guān)閉時(shí)MySQL會(huì)自動(dòng)將創(chuàng)建的臨時(shí)表執(zhí)行刪除操作，如下：

mysql> create temporary table t_tmp(age int)engine=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql> show create table t_tmp;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                       |
+-------+----------------------------------------------------------------------------------------------------+
| t_tmp | CREATE TEMPORARY TABLE `t_tmp` (
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

雖然臨時(shí)表的生命周期是會(huì)話級(jí)別的，但是在程序中顯式的刪除臨時(shí)表永遠(yuǎn)是一個(gè)我們必須要做的動(dòng)作(刪除也是使用drop table語(yǔ)句如：drop table t_tmp;)，因?yàn)槟悴荒鼙ＷC任何場(chǎng)景下你所創(chuàng)建的臨時(shí)表都會(huì)被合理的刪除，比如使用線程池時(shí)，此時(shí)就不僅僅是臨時(shí)表沒(méi)有被刪除而占用資源的問(wèn)題了，還會(huì)因?yàn)楹罄m(xù)的程序讀取到前面程序在臨時(shí)表中的數(shù)據(jù)，而造成bug，而且這種bug是很難發(fā)現(xiàn)的。所以，養(yǎng)成好習(xí)慣是很重要的。

2：臨時(shí)表和內(nèi)存表

• 內(nèi)存表
  內(nèi)存表指的是存儲(chǔ)引擎為memory的表，建表語(yǔ)句是create table t()engine=memory,數(shù)據(jù)是保存在內(nèi)存中的，因此如果是重啟的話，數(shù)據(jù)不會(huì)保留，但表結(jié)構(gòu)是保留的，可以看到，內(nèi)存表就是正常的表，只不過(guò)是存儲(chǔ)引擎為memory，且重啟后數(shù)據(jù)不會(huì)保留，如下測(cè)試：

mysql> create table t_memory(id int primary key auto_increment)engine=memory;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t_memory value();
Query OK, 1 row affected (0.04 sec)
mysql> select * from t_memory;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)
// 重啟
[root@localhost tmp]# service mysql restart
Shutting down MySQL............. SUCCESS! 
Starting MySQL................................................................. SUCCESS! 
// 重啟后查看
mysql> show create table t_memory;
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| t_memory | CREATE TABLE `t_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t_memory;
Empty set (0.00 sec)

• 臨時(shí)表
  可以是任何存儲(chǔ)引擎，但是生命周期和會(huì)話綁定，并且不同會(huì)話可以創(chuàng)建相同名稱的臨時(shí)表，具體我們?cè)诤竺胬^續(xù)來(lái)分析。

3：臨時(shí)表的特點(diǎn)

為了便于理解，我們來(lái)看下下面這個(gè)操作序列

總結(jié)其特點(diǎn)如下：

1：語(yǔ)法是create temporary table ...
2：臨時(shí)表在會(huì)話之間是隔離的，即本會(huì)話只能看到本會(huì)話內(nèi)創(chuàng)建的臨時(shí)表
3：臨時(shí)表可以和普通表同名
4：操作時(shí)，存在同名的臨時(shí)表和普通表時(shí)，臨時(shí)表的優(yōu)先級(jí)高于普通表
5：show tables不會(huì)顯示臨時(shí)表，只顯示普通表
6：不同會(huì)話可以創(chuàng)建同名的臨時(shí)表

其中的特點(diǎn)6：不同會(huì)話可以創(chuàng)建同名的臨時(shí)表當(dāng)我們?cè)趯?shí)際業(yè)務(wù)代碼中需要使用中間表的業(yè)務(wù)中就非常有用了，比如在分庫(kù)分表場(chǎng)景中聚合不同庫(kù)和表的數(shù)據(jù)，此時(shí)如果是使用普通表，那么不同的會(huì)話并行操作時(shí)肯定會(huì)出現(xiàn)表名稱重復(fù)的問(wèn)題，而使用臨時(shí)表則會(huì)很好的解決這個(gè)問(wèn)題。

4：為什么臨時(shí)表是可以重名的

從前面的分析中我們知道了，不同的會(huì)話臨時(shí)表名稱是可以重復(fù)的，這是為什么呢？要解釋這個(gè)問(wèn)題，必須先來(lái)了解下，MySQL是如何判斷表是否存在？，每個(gè)表都有一個(gè)對(duì)應(yīng)的table_def_key，對(duì)于普通表table_def_key的定義是庫(kù)名+表名，因此普通表的表名稱不可以重復(fù)，而臨時(shí)表table_def_key的規(guī)則是庫(kù)名+表名+server_id+thread_id，而其中thread_id，每個(gè)會(huì)話連接都是不一樣的，所以，臨時(shí)表是可以重名的，那么當(dāng)我們執(zhí)行語(yǔ)句create temporary table tttt(age int(32))engine=innodb;之后臨時(shí)表tttt的結(jié)構(gòu)和數(shù)據(jù)都是如何存儲(chǔ)的呢？對(duì)于結(jié)構(gòu)是在select @@tmpdir目錄下創(chuàng)建名稱為#sql{進(jìn)程 id}_{線程 id}_序列號(hào).frm的文件，如下：

[root@localhost tmp]# mysql -uroot -p -e"select @@tmpdir"
Enter password: 
+----------+
| @@tmpdir |
+----------+
| /tmp     |
+----------+
[root@localhost tmp]# pwd
/tmp
[root@localhost tmp]# ll | egrep '#sql'
-rw-r----- 1 mysql mysql    8558 Sep  2 16:50 #sql105b4_3_0.frm

數(shù)據(jù)的存放，在5.7之前是在select @@tmpdir目錄下創(chuàng)建一個(gè)相同前綴的.ibd文件，5.7之后引入了臨時(shí)文件表空間，數(shù)據(jù)就存放在這里，就不需要生成ibd文件了。

寫在后面

參考文章列表：

MySQL 臨時(shí)表 

到此這篇關(guān)于MySQL之臨時(shí)表的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySQL 臨時(shí)表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論