创建mysql临时表的方法是使用create temporary table语句，1.语法与普通表相同但需添加temporary关键字；2.临时表在会话结束后自动删除；3.不同会话可创建同名表互不影响；4.用户需拥有create temporary tables权限；5.磁盘空间不足或tmpdir配置问题会导致创建失败；6.内存临时表适用于小数据量、速度快，磁盘临时表适合大数据量或含blob/text字段；7.可通过explain命令查看是否使用临时表及类型；8.优化技巧包括避免复杂where条件、合理设置tmp_table_size和max_heap_table_size、使用索引、避免blob/text字段；9.监控可使用show global status、performance_schema或第三方工具；10.存储过程中可创建临时表处理中间结果，函数中受限；11.相比子查询，临时表更适合多次使用或大数据量场景；12.mysql 8.0支持with子句、create or replace语法并优化存储引擎提升性能。
创建MySQL临时表，本质上就是创建一个只在当前会话存在的表。会话断开，表自动消失，数据也随之不见。它像一个草稿本，用完就扔，非常适合处理一些中间结果集，避免污染正式的数据库表。
创建临时表使用 CREATE TEMPORARY TABLE 语句。语法和创建普通表几乎一样，只是多了 TEMPORARY 关键字。
CREATE TEMPORARY TABLE temp_orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2)
);
-- 插入数据
INSERT INTO temp_orders (order_id, customer_id, order_date, total_amount)
SELECT order_id, customer_id, order_date, total_amount FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
-- 使用临时表进行查询
SELECT customer_id, SUM(total_amount) AS total_spent
FROM temp_orders
GROUP BY customer_id
ORDER BY total_spent DESC;
-- 会话结束，临时表自动删除临时表最大的好处是隔离性。不同会话可以创建同名的临时表，互不影响。这在并发环境下非常有用，避免了数据冲突。
MySQL临时表创建失败的常见原因及解决方法
临时表创建失败，除了语法错误，更常见的原因是权限问题。用户需要拥有 CREATE TEMPORARY TABLES 权限。另外，如果磁盘空间不足，也可能导致创建失败。
解决方法也很简单：
- 检查语法：仔细核对 SQL 语句，特别是表名、字段类型等。
- 检查权限：使用
SHOW GRANTS FOR 'your_user'@'your_host';查看用户权限，如果没有CREATE TEMPORARY TABLES权限，需要管理员授权。 - 检查磁盘空间：使用
df -h命令查看磁盘空间，如果空间不足，需要清理磁盘或增加磁盘空间。 - 检查
tmpdir配置：临时表默认存储在tmpdir指定的目录下。如果该目录不存在或权限不足，也会导致创建失败。可以在 MySQL 配置文件（my.cnf 或 my.ini）中修改tmpdir的值。 - 并发冲突：在高并发环境下，如果多个会话同时创建同名的临时表，可能会出现冲突。可以尝试使用更复杂的表名，或者使用锁机制避免冲突。
一个容易被忽略的点是，如果临时表依赖于其他表，而你没有这些表的访问权限，创建过程也会失败。
内存临时表与磁盘临时表：性能差异与选择策略
内存临时表和磁盘临时表，顾名思义，一个存储在内存中，一个存储在磁盘上。 性能差异巨大。内存临时表速度飞快，但受限于内存大小；磁盘临时表速度较慢，但可以存储更多数据。
MySQL 决定使用哪种类型的临时表，主要取决于几个因素：
- 临时表的大小：如果临时表的大小超过
tmp_table_size和max_heap_table_size这两个参数的最小值，MySQL 会自动将内存临时表转换为磁盘临时表。 - 字段类型：如果临时表包含
BLOB或TEXT类型的字段，MySQL 也会直接使用磁盘临时表。 - 索引：内存临时表不支持
BLOB和TEXT类型的字段建立索引，如果需要对这些字段进行索引，只能使用磁盘临时表。
选择策略也很简单：
- 小数据量：优先使用内存临时表，速度更快。
- 大数据量：只能使用磁盘临时表。
- 需要对
BLOB或TEXT类型的字段进行索引：只能使用磁盘临时表。
可以通过 EXPLAIN 命令查看 MySQL 是否使用了临时表，以及使用了哪种类型的临时表。
EXPLAIN SELECT customer_id, SUM(total_amount) AS total_spent FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' GROUP BY customer_id ORDER BY total_spent DESC;
如果 Extra 列包含 Using temporary，则表示使用了临时表。如果还包含 Using filesort，则表示使用了磁盘临时表。
优化临时表性能：避免磁盘临时表的出现
尽可能避免使用磁盘临时表，是优化 SQL 性能的关键。以下是一些常用的优化技巧：
- 优化 SQL 语句：避免在
WHERE子句中使用复杂的表达式或函数，尽量使用索引。 - 增加
tmp_table_size和max_heap_table_size的值：增加内存临时表的最大大小，减少转换为磁盘临时表的可能性。但要注意，这两个参数的值不能设置过大，否则会占用过多的内存。 - 使用索引：在经常用于
GROUP BY和ORDER BY的字段上建立索引，可以避免使用临时表。 - 避免使用
BLOB和TEXT类型的字段：尽量使用VARCHAR或TEXT类型的字段代替BLOB和TEXT类型的字段。 - 使用
SQL_BIG_RESULT或SQL_SMALL_RESULT提示：SQL_BIG_RESULT告诉 MySQL 结果集可能会很大，应该使用磁盘临时表；SQL_SMALL_RESULT告诉 MySQL 结果集可能会很小，应该使用内存临时表。
需要注意的是，过度优化也可能导致性能下降。例如，过度增加 tmp_table_size 和 max_heap_table_size 的值，可能会导致内存不足，反而降低性能。因此，需要根据实际情况进行调整。
如何监控MySQL临时表的使用情况？
监控 MySQL 临时表的使用情况，可以帮助我们发现潜在的性能问题。
-
使用
SHOW GLOBAL STATUS LIKE 'Created_tmp%';命令查看全局临时表的统计信息。SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-
Created_tmp_disk_tables：表示创建的磁盘临时表的数量。 -
Created_tmp_tables：表示创建的内存临时表的数量。
如果
Created_tmp_disk_tables的值很高，则表示存在大量的磁盘临时表，需要进行优化。 -
-
使用
PERFORMANCE_SCHEMA数据库：PERFORMANCE_SCHEMA数据库提供了更详细的临时表信息。SELECT OBJECT_NAME, SUM(IF(OBJECT_TYPE='TABLE',1,0)) AS TABLES, SUM(IF(OBJECT_TYPE='TABLE',COUNT,0)) AS TABLE_COUNT, SUM(IF(OBJECT_TYPE='TABLE',SUM_TIMER_WAIT,0)) AS TABLE_SUM_TIMER_WAIT FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NULL AND OBJECT_SCHEMA = 'your_database' GROUP BY OBJECT_NAME ORDER BY TABLE_SUM_TIMER_WAIT DESC;这个查询可以显示每个表的 I/O 等待时间，可以帮助我们找到哪些表使用了大量的磁盘 I/O。
使用第三方监控工具：例如 Prometheus、Grafana 等。这些工具可以提供更全面的监控指标，例如 CPU 使用率、内存使用率、磁盘 I/O 等。
监控临时表的使用情况，是一个持续的过程。需要定期检查，并根据实际情况进行调整。
临时表在存储过程和函数中的应用场景
存储过程和函数中，临时表可以发挥很大的作用。比如，在复杂的报表统计中，可以将中间结果存储在临时表中，然后进行进一步的计算。
DELIMITER //
CREATE PROCEDURE CalculateMonthlySales(IN year INT, IN month INT)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS MonthlyOrders (
order_id INT PRIMARY KEY,
customer_id INT,
total_amount DECIMAL(10, 2)
);
-- 清空临时表
TRUNCATE TABLE MonthlyOrders;
-- 插入当月订单数据
INSERT INTO MonthlyOrders (order_id, customer_id, total_amount)
SELECT order_id, customer_id, total_amount
FROM orders
WHERE YEAR(order_date) = year AND MONTH(order_date) = month;
-- 计算总销售额
SELECT SUM(total_amount) AS total_sales
FROM MonthlyOrders;
-- 删除临时表（可选，存储过程结束会自动删除）
-- DROP TEMPORARY TABLE MonthlyOrders;
END //
DELIMITER ;
-- 调用存储过程
CALL CalculateMonthlySales(2023, 01);在这个例子中，MonthlyOrders 临时表用于存储当月的订单数据。存储过程首先清空临时表，然后插入当月订单数据，最后计算总销售额。
需要注意的是，在存储过程中创建的临时表，在存储过程执行完毕后会自动删除。因此，如果需要在存储过程外部访问临时表，需要手动删除临时表。
另外，在函数中使用临时表需要注意一些限制。例如，函数不能修改全局变量，因此不能在函数中创建或删除临时表。
临时表与子查询：性能对比与最佳实践
临时表和子查询都可以用于处理中间结果集，但它们的性能差异很大。
- 临时表：将中间结果存储在临时表中，可以避免重复计算。临时表可以被多个查询使用，因此可以提高性能。
- 子查询：每次执行查询时，都需要重新计算子查询的结果。如果子查询的结果集很大，或者子查询的执行时间很长，则会导致性能下降。
一般来说，如果子查询只被使用一次，且结果集很小，则可以使用子查询。如果子查询被多次使用，或者结果集很大，则应该使用临时表。
以下是一个使用临时表代替子查询的例子：
-- 使用子查询
SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) AS order_count
FROM customers c;
-- 使用临时表
CREATE TEMPORARY TABLE IF NOT EXISTS CustomerOrderCount (
customer_id INT PRIMARY KEY,
order_count INT
);
INSERT INTO CustomerOrderCount (customer_id, order_count)
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;
SELECT c.customer_id, coc.order_count
FROM customers c
LEFT JOIN CustomerOrderCount coc ON c.customer_id = coc.customer_id;
DROP TEMPORARY TABLE CustomerOrderCount;在这个例子中，使用临时表 CustomerOrderCount 存储每个客户的订单数量，然后将客户表和临时表进行连接，得到每个客户的订单数量。使用临时表可以避免重复计算子查询的结果，从而提高性能。
最佳实践是，在复杂的查询中，优先考虑使用临时表，特别是当子查询被多次使用，或者结果集很大时。
MySQL 8.0 对临时表的改进
MySQL 8.0 对临时表进行了多项改进，包括：
- 支持
TEMPORARY TABLE的WITH子句：可以在WITH子句中创建临时表，使 SQL 语句更简洁。 - 支持
TEMPORARY TABLE的CREATE OR REPLACE语法：如果临时表已经存在，则会先删除临时表，然后重新创建临时表。 - 优化了临时表的存储引擎：MySQL 8.0 使用更高效的存储引擎存储临时表，提高了性能。
这些改进使得临时表更加易于使用，性能也更高。
总的来说，临时表是 MySQL 中一个非常重要的功能。掌握临时表的使用技巧，可以帮助我们编写更高效的 SQL 语句。
