在mysql中，暫存表指的是暫時使用的一張表，是用來儲存一些中間結果集的表；暫存表只在目前連線可見，當關閉連線時，Mysql會自動刪除表並釋放所有空間。
本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。
臨時表，是暫時使用的一張表。
臨時表是MySQL用來儲存一些中間結果集的表，臨時表只在目前連接可見，當關閉連接時，Mysql會自動刪除表並釋放所有空間。
使用其他MySQL客戶端程式連接MySQL資料庫伺服器來建立臨時表，那麼只有在關閉客戶端程式時才會銷毀臨時表，當然也可以手動刪除。
附註：臨時表在MySQL 3.23版本中加入，如果你的MySQL版本低於 3.23版本就無法使用MySQL的臨時表。不過現在一般很少有再使用這麼低版的MySQL資料庫服務了
#MySQL中的兩種臨時表
#外部暫存資料表
- 透過CREATE TEMPORARY TABLE 建立的暫存表，這種暫存表稱為外部暫存表。這種臨時表只對目前使用者可見，當前會話結束的時候，臨時表會自動關閉。這種臨時表的命名與非臨時表可以同名（同名後非臨時表將對當前會話不可見，直到臨時表被刪除）。 內部暫存表
一種是HEAP臨時表，這種臨時表的所有資料都會存在記憶體中，對於這種表的操作不需要IO操作。
另一種是OnDisk暫存資料表，顧名思義，這種暫存表會將資料儲存在磁碟上。 OnDisk臨時表用來處理中間結果比較大的操作。如果HEAP暫存資料表儲存的資料大於MAX_HEAP_TABLE_SIZE，HEAP暫存資料表將會自動轉換成OnDisk暫存資料表。
OnDisk臨時表在5.7中可以透過INTERNAL_TMP_DISK_STORAGE_ENGINE系統變數選擇使用MyISAM引擎或InnoDB引擎。
外部臨時表的常見用法 外部臨時表是透過CREATE TEMPORARY TABLE及DROP TABLE來操作的，但是SHOW TABLES指令顯示資料表清單時，你將無法看到自己建立的臨時表的。並且在退出目前會話後，臨時表就會被自動銷毀。當然也可以手動（DROP TABLE）銷毀。1、引擎類型：只能是：memory（heap）、myisam、merge、innodb ，不支援mysql cluster(簇)。
2、外部暫存資料表使用時注意幾點：
1）、自己所使用的資料庫帳號要有建立暫存資料表的權限；2）、在同一條sql中，不能關聯2次相同的臨時表，不然，就會報如下錯誤；
mysql> select * from temp_table, temp_table as t2; error 1137: can't reopen table: 'temp_table'3）、臨時表在建立連接時可見，關閉時會清除空間，刪除臨時表；
4）、show tables 不會列出臨時表；5）、不能使用rename重新命名臨時表。但是，你可以alter table代替：只能使用alter table old_tp_table_name rename new_tp_table_name;
6）、影響使用replication功能；7）、如果你為一個表聲明了別名，當你指向這個表的時候，就必須使用這個別名。請參閱《MySQL 多表關聯更新及刪除》範例：mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql> SELECT * FROM SalesSummary;
ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist
- #3、mybatis中暫存表操作
-
<update id="createTempTable"> CREATE TEMPORARY TABLE IF NOT EXISTS temp SELECT * FROM settlement_temp WHERE settle_date=#{settleDate} AND LENGTH(operator) IN(16,32) AND pay_status IN ('01','06') ORDER BY settle_date,merchant_no </update><!-- 4、删除临时表 --> <update id="dropTempTable"> DROP TEMPORARY TABLE IF EXISTS settlement_temp; </update>###### #內部臨時表的常見用法############ 如果使用者在書寫SQL語句的時候能夠盡量少的使用內部臨時表進行查詢優化，將有效的提高查詢執行的效率。 ######首先我們定義一個表t1，###CREATE TABLE t1( a int, b int); INSERT INTO t1 VALUES(1,2),(3,4);
###下面所有的運算都是基於表t1進行舉例的。 ############在SQL語句中使用SQL_BUFFER_RESULT hint###
SQL_BUFFER_RESULT主要用来让MySQL尽早的释放表上的锁。因为如果数据量很大的话，需要较长时间将数据发送到客户端，通过将数据缓冲到临时表中可以有效的减少读锁对表的占用时间。SQL_BUFFER_RESULT见《mysql查询优化之三：查询优化器提示（hint）》
例如：
mysql> explain format=json select SQL_BUFFER_RESULT * from t1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.00"
},
"buffer_result": {
"using_temporary_table": true,
"table": {
"table_name": "t1",
"access_type": "ALL",
...如果SQL语句中包含了DERIVED_TABLE。
在5.7中，由于采用了新的优化方式，我们需要使用 set optimizer_switch=’derived_merge=off’来禁止derived table合并到外层的Query中。
例如：
mysql> explain format=json select * from (select * from t1) as tt;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.40"
},
"table": {
"table_name": "tt",
"access_type": "ALL",
...
"materialized_from_subquery": {
"using_temporary_table": true,
...如果我们查询系统表的话，系统表的数据将被存储到内部临时表中。
我们当前不能使用EXPLAIN来查看是否读取系统表数据需要利用到内部临时表，但是可以通过SHOW STATUS来查看是否利用到了内部临时表。
例如：
mysql> select * from information_schema.character_sets; mysql> show status like 'CREATE%';
如果DISTINCT语句没有被优化掉，即DISTINCT语句被优化转换为GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT, 内部临时表将会被使用。
mysql> explain format=json select distinct a from t1;
EXPLAIN
{
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.60"
},
"duplicates_removal": {
"using_temporary_table": true,
...如果查询带有ORDER BY语句，并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据，然后对中间数据进行排序。
1）如果连接表使用BNL（Batched Nestloop）/BKA(Batched Key Access)
例如：
1))BNL默认是打开的
mysql> explain format=json select * from t1, t1 as t2 order by t1.a;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "22.00"
},
"ordering_operation": {
"using_temporary_table": true,
...2))关掉BNL后，ORDER BY将直接使用filesort。
mysql> set optimizer_switch='block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)
mysql> explain format=json select * from t1, t1 as t2 order by t1.a;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "25.00"
},
"ordering_operation": {
"using_filesort": true,
...2）ORDER BY的列不属于执行计划中第一个连接表的列。
例如：
mysql> explain format=json select * from t as t1, t as t2 order by t2.a;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "25.00"
},
"ordering_operation": {
"using_temporary_table": true,
...3）如果ORDER BY的表达式是个复杂表达式。
那么什么样的ORDER BY表达式，MySQL认为是复杂表达式呢？
1))如果排序表达式是SP或者UDF。
例如：
drop function if exists func1;
delimiter |
create function func1(x int)
returns int deterministic
begin
declare z1, z2 int;
set z1 = x;
set z2 = z1+2;
return z2;
end|
delimiter ;
explain format=json select * from t1 order by func1(a);
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.20"
},
"ordering_operation": {
"using_temporary_table": true,
...2))ORDER BY的列包含聚集函数
为了简化执行计划，我们利用INDEX来优化GROUP BY语句。
例如：
create index idx1 on t1(a);
explain format=json SELECt a FROM t1 group by a order by sum(a);
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"grouping_operation": {
"using_filesort": false,
...
drop index idx1 on t1;3))ORDER BY的列中包含有SCALAR SUBQUERY,当然该SCALAR SUBQUERY没有被优化掉。
例如：
explain format=json select (select rand() from t1 limit 1) as a from t1 order by a;
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
...4) 如果查询既带有ORDER BY同时也有GROUP BY语句，但是两个语句使用的列不相同。
注意: 如果是5.7，我们需要将sql_mode设置为非only_full_group_by模式，否则会报错。
同样为了简化执行计划，我们利用INDEX来优化GROUP BY语句。
例如：
set sql_mode='';
create index idx1 on t1(b);
explain format=json select t1.a from t1 group by t1.b order by 1;
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.40"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"grouping_operation": {
"using_filesort": false,
...
drop index idx1 on t1;如果查询带有GROUP BY语句，并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据，然后对中间数据进行GROUP BY。
1）如果连接表使用BNL（Batched Nestloop）/BKA(Batched Key Access)。
例如：
explain format=json select t2.a from t1, t1 as t2 group by t1.a;
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "8.20"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "4.00"
...2) 如果GROUP BY的列不属于执行计划中的第一个连接表。
例如：
explain format=json select t2.a from t1, t1 as t2 group by t2.a;
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "8.20"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"nested_loop": [
...3) 如果GROUP BY语句使用的列与ORDER BY语句使用的列不同。
例如：
set sql_mode='';
explain format=json select t1.a from t1 group by t1.b order by t1.a;
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.40"
},
"ordering_operation": {
"using_filesort": true,
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
...4) 如果GROUP BY带有ROLLUP并且是基于多表外连接。
例如：
explain format=json select sum(t1.a) from t1 left join t1 as t2 on true group by t1.a with rollup;
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "7.20"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "4.00"
},
...5) 如果GROUP BY语句使用的列来自于SCALAR SUBQUERY，并且没有被优化掉。
例如：
explain format=json select (select avg(a) from t1) as a from t1 group by a;
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.40"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "2.00"
},
...IN表达式转换为semi-join进行优化
1) 如果semi-join执行方式为Materialization
例如：
set optimizer_switch='firstmatch=off,duplicateweedout=off';
explain format=json select * from t1 where a in (select b from t1);
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "5.60"
},
"nested_loop": [
{
"rows_examined_per_scan": 1,
"materialized_from_subquery": {
"using_temporary_table": true,
"query_block": {
"table": {
"table_name": "t1",
"access_type": "ALL",
...2) 如果semi-join执行方式为Duplicate Weedout
例如：
set optimizer_switch='firstmatch=off';
explain format=json select * from t1 where a in (select b from t1);
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "4.80"
},
"duplicates_removal": {
"using_temporary_table": true,
"nested_loop": [
{
...如果查询语句带有UNION，MySQL将利用内部临时表帮助UNION操作消除重复。
例如：
explain format=json select * from t1 union select * from t1;
| {
"query_block": {
"union_result": {
"using_temporary_table": true,
"table_name": "<union1,2>",
...如果查询语句使用多表更新。
这里Explain不能看到内部临时表被利用，所以需要查看status。
例如：
update t1, t1 as t2 set t1.a=3; show status like 'CREATE%';
如果聚集函数中包含如下函数，内部临时表也会被利用。
1) count(distinct *) 例如： explain format=json select count(distinct a) from t1; 2) group_concat 例如： explain format=json select group_concat(b) from t1;
总之，上面列出了10种情况，MySQL将利用内部临时表进行中间结果缓存，如果数据量比较大的话，内部临时表将会把数据存储在磁盘上，这样显然会对性能有所影响。为了尽可能的减少性能损失，我们需要尽量避免上述情况的出现。
MySQL在以下几种情况会创建临时表：
1、UNION查询； 2、用到TEMPTABLE算法或者是UNION查询中的视图； 3、ORDER BY和GROUP BY的子句不一样时； 4、表连接中，ORDER BY的列不是驱动表中的； 5、DISTINCT查询并且加上ORDER BY时； 6、SQL中用到SQL_SMALL_RESULT选项时； 7、FROM中的子查询； 8、子查询或者semi-join时创建的表；
EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表。
当然了，如果临时表中需要存储的数据量超过了上限（ tmp-table-size 或 max-heap-table-size 中取其大者），这时候就需要生成基于磁盘的临时表了。
在以下几种情况下，会创建磁盘临时表：
1、数据表中包含BLOB/TEXT列； 2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）； 3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）； 4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型。
从5.7.5开始，新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB，而在这以前，只能使用 MyISAM。而在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型，在以前默认是MEMORY，不要把这二者混淆了。
见下例：
mysql> set default_tmp_storage_engine = "InnoDB"; -rw-rw---- 1 mysql mysql 8558 Jul 7 15:22 #sql4b0e_10_0.frm -- InnoDB引擎的临时表 -rw-rw---- 1 mysql mysql 98304 Jul 7 15:22 #sql4b0e_10_0.ibd -rw-rw---- 1 mysql mysql 8558 Jul 7 15:25 #sql4b0e_10_2.frm mysql> set default_tmp_storage_engine = "MyISAM"; -rw-rw---- 1 mysql mysql 0 Jul 7 15:25 #sql4b0e_10_2.MYD -- MyISAM引擎的临时表 -rw-rw---- 1 mysql mysql 1024 Jul 7 15:25 #sql4b0e_10_2.MYI mysql> set default_tmp_storage_engine = "MEMORY"; -rw-rw---- 1 mysql mysql 8558 Jul 7 15:26 #sql4b0e_10_3.frm -- MEMORY引擎的临时表
【相关推荐：mysql视频教程】
以上是mysql什麼是臨時表的詳細內容。更多資訊請關注PHP中文網其他相關文章！
图文详解mysql架构原理May 17, 2022 pm 05:54 PM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于架构原理的相关内容，MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层，下面一起来看一下，希望对大家有帮助。
mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM在mysql中，可以利用char()和REPLACE()函数来替换换行符；REPLACE()函数可以用新字符串替换列中的换行符，而换行符可使用“char(13)”来表示，语法为“replace(字段名,char(13),'新字符串') ”。
mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PMmysql的msi与zip版本的区别：1、zip包含的安装程序是一种主动安装，而msi包含的是被installer所用的安装文件以提交请求的方式安装；2、zip是一种数据压缩和文档存储的文件格式，msi是微软格式的安装包。
mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM方法：1、利用right函数，语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”；2、利用substring函数，语法为“select substring(指定字段,2)..”。
mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM转换方法：1、利用cast函数，语法“select * from 表名 order by cast(字段名 as SIGNED)”；2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。
MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于MySQL复制技术的相关问题，包括了异步复制、半同步复制等等内容，下面一起来看一下，希望对大家有帮助。
带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了mysql高级篇的一些问题，包括了索引是什么、索引底层实现等等问题，下面一起来看一下，希望对大家有帮助。
mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM在mysql中，可以利用REGEXP运算符判断数据是否是数字类型，语法为“String REGEXP '[^0-9.]'”；该运算符是正则表达式的缩写，若数据字符中含有数字时，返回的结果是true，反之返回的结果是false。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
熱工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
DVWA
Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序，非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具，幫助Web開發人員更好地理解保護網路應用程式的過程，並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞，難度各不相同。請注意，該軟體中
SecLists
SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合，這些清單在安全評估過程中經常使用，而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表，幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上，他就可以存取所需的每種類型的清單。
Atom編輯器mac版下載
最受歡迎的的開源編輯器
ZendStudio 13.5.1 Mac
強大的PHP整合開發環境
