MySQL之臨時表的實現(xiàn)示例

更新時間：2023年08月23日 10:43:41 作者：一戶董

MySQL臨時表是存儲在內存或者磁盤上的臨時數(shù)據(jù)表,它們的生命周期只限于當前數(shù)據(jù)庫會話,臨時表的創(chuàng)建和使用方式與普通表類似,本文就詳細的介紹了MySQL之臨時表,感興趣的可以了解一下

寫在前面

本文一起看下MySQL的臨時表。

1：什么是臨時表

通過create temporary table t語句創(chuàng)建的表，就是臨時表，臨時表的臨時體現(xiàn)在其生命周期是和會話一樣的，當會話結束，即連接關閉時MySQL會自動將創(chuàng)建的臨時表執(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)

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

2：臨時表和內存表

內存表
內存表指的是存儲引擎為memory的表，建表語句是create table t()engine=memory,數(shù)據(jù)是保存在內存中的，因此如果是重啟的話，數(shù)據(jù)不會保留，但表結構是保留的，可以看到，內存表就是正常的表，只不過是存儲引擎為memory，且重啟后數(shù)據(jù)不會保留，如下測試：

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)

臨時表
可以是任何存儲引擎，但是生命周期和會話綁定，并且不同會話可以創(chuàng)建相同名稱的臨時表，具體我們在后面繼續(xù)來分析。

3：臨時表的特點

為了便于理解，我們來看下下面這個操作序列

總結其特點如下：

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

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

4：為什么臨時表是可以重名的

從前面的分析中我們知道了，不同的會話臨時表名稱是可以重復的，這是為什么呢？要解釋這個問題，必須先來了解下，MySQL是如何判斷表是否存在？，每個表都有一個對應的table_def_key，對于普通表table_def_key的定義是庫名+表名，因此普通表的表名稱不可以重復，而臨時表table_def_key的規(guī)則是庫名+表名+server_id+thread_id，而其中thread_id，每個會話連接都是不一樣的，所以，臨時表是可以重名的，那么當我們執(zhí)行語句create temporary table tttt(age int(32))engine=innodb;之后臨時表tttt的結構和數(shù)據(jù)都是如何存儲的呢？對于結構是在select @@tmpdir目錄下創(chuàng)建名稱為#sql{進程 id}_{線程 id}_序列號.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)建一個相同前綴的.ibd文件，5.7之后引入了臨時文件表空間，數(shù)據(jù)就存放在這里，就不需要生成ibd文件了。