Mysql：暫存資料表、表變數

在mysql中沒有表變數這一概念！

mysql有暫存資料表：create temporary table if not exists {表定義}　　--關鍵字”temporary“指示mysql建立會話層級的暫存資料表。

• 暫存資料表只對當前會話可見，串連斷開時，自動刪除！
• 你不必擔心所建立的暫存資料表的名稱會和其他會話建立的暫存資料表、或非暫存資料表衝突！注意如果你的暫存資料表和正常表名稱相同，正常表會被隱藏——如同全域變數和局部變數那樣
• 建立暫存資料表不會引發通常的commit事務提交

 

使用暫存資料表的諸多限制

• 引擎類型只能是：memory（heap）、myisam、merge、innodb
• 不支援mysql cluster
• 同一個查詢語句中只能引用一次！ 如 SELECT * FROM TP_TABLE , TP_TABLE AS ALIAS_NAME;　　是錯誤的
• 同一個使用者儲存函數中只能引用一次！
• show tables 不會顯示暫存資料表
• 不能使用rename重新命名暫存資料表。只能使用ALTER TABLE OLD_TP_TABLE_NAME RENAME NEW_TP_TABLE_NAME;
• 影響使用replication功能

 

mysql伺服器自動使用的內部暫存資料表

• 在某些情況下，mysql伺服器會自動建立內部暫存資料表：該暫存資料表可以是只存在於記憶體的memory暫存資料表，或者是儲存於硬碟的myisam暫存資料表；而且 初始建立的memory暫存資料表由於表的增大 可能會轉變為myisam暫存資料表——其轉化臨界點由max_heap_table_size 和tmp_table_size系統變數的 較小值 決定的！注意：max_heap_table_size系統變數應用於所有的memory引擎的表，不管是使用者暫存資料表、正常表、或者內部暫存資料表。
• 內部暫存資料表的建立條件：

1. group by 和 order by中的列不相同
2. order by的列不是引用from 表列表中 的第一表
3. group by的列不是引用from 表列表中 的第一表
4. 使用了sql_small_result選項
5. 含有distinct 的 order by語句

• 初始建立內部myisam暫存資料表的條件：

1. 表中存在text、blob列
2. 在group by中的 列 有超過512位元組
3. 在distinct查詢中的 列 有超過512位元組
4. 在union、union all聯集查詢中，select 列 列表中的 列 有超過512位元組的

• 如何判斷某個查詢是否會產生內部暫存資料表：使用explain查看執行計畫，檢查extra列看是否使用暫存資料表“using temporary”