MySQL 临时表 - MySQL 教程 | 小傅哥技术教程

临时表概述

临时表是 MySQL 中一种特殊的表类型，用于存储临时数据。它们在会话结束时自动删除，非常适合处理中间结果、复杂查询的数据缓存和数据处理任务。

🔄 会话临时表

使用 CREATE TEMPORARY TABLE 创建，只在当前会话中可见，会话结束时自动删除

💾 内部临时表

MySQL 自动创建的临时表，用于处理复杂查询，如 GROUP BY、ORDER BY、UNION 等

                    临时表的优势：
                    自动清理，无需手动删除
会话隔离，不同会话间互不影响
提高复杂查询的性能
简化复杂的数据处理逻辑
减少对主表的锁定时间

                

使用注意事项：

临时表占用内存和磁盘空间
大量临时表可能影响性能
无法在不同会话间共享数据
不支持外键约束

创建临时表

基本语法

-- 创建临时表的基本语法
CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype,
    ...
);

-- 从现有表结构创建临时表
CREATE TEMPORARY TABLE temp_table_name LIKE existing_table;

-- 从查询结果创建临时表
CREATE TEMPORARY TABLE temp_table_name AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;
                

示例数据准备

-- 创建示例表
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    category VARCHAR(50),
    sale_date DATE,
    quantity INT,
    unit_price DECIMAL(10,2),
    salesperson VARCHAR(50),
    region VARCHAR(50)
);

-- 插入示例数据
INSERT INTO sales (product_name, category, sale_date, quantity, unit_price, salesperson, region) VALUES
('iPhone 14', '手机', '2024-01-15', 5, 7999.00, '张三', '华北'),
('MacBook Pro', '笔记本', '2024-01-16', 2, 15999.00, '李四', '华东'),
('iPad Air', '平板', '2024-01-17', 3, 4599.00, '王五', '华南'),
('AirPods Pro', '耳机', '2024-01-18', 10, 1899.00, '张三', '华北'),
('Samsung S23', '手机', '2024-01-19', 4, 5999.00, '赵六', '华西'),
('Dell XPS', '笔记本', '2024-01-20', 1, 8999.00, '李四', '华东'),
('Surface Pro', '平板', '2024-01-21', 2, 7888.00, '王五', '华南'),
('Sony WH-1000XM5', '耳机', '2024-01-22', 6, 2399.00, '赵六', '华西'),
('iPhone 15', '手机', '2024-01-23', 8, 8999.00, '张三', '华北'),
('MacBook Air', '笔记本', '2024-01-24', 3, 8999.00, '李四', '华东');

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    city VARCHAR(50),
    registration_date DATE
);

INSERT INTO customers (name, email, phone, city, registration_date) VALUES
('张明', 'zhangming@email.com', '13800138001', '北京', '2023-06-15'),
('李华', 'lihua@email.com', '13800138002', '上海', '2023-07-20'),
('王芳', 'wangfang@email.com', '13800138003', '广州', '2023-08-10'),
('赵强', 'zhaoqiang@email.com', '13800138004', '深圳', '2023-09-05'),
('刘洋', 'liuyang@email.com', '13800138005', '杭州', '2023-10-12');
                

创建临时表示例

-- 1. 创建简单的临时表
CREATE TEMPORARY TABLE temp_sales_summary (
    category VARCHAR(50),
    total_quantity INT,
    total_amount DECIMAL(12,2),
    avg_price DECIMAL(10,2)
);

-- 2. 从现有表结构创建临时表
CREATE TEMPORARY TABLE temp_sales_backup LIKE sales;

-- 3. 从查询结果创建临时表
CREATE TEMPORARY TABLE temp_high_value_sales AS
SELECT 
    product_name,
    category,
    quantity,
    unit_price,
    (quantity * unit_price) AS total_value
FROM sales
WHERE (quantity * unit_price) > 10000;

-- 4. 创建带索引的临时表
CREATE TEMPORARY TABLE temp_sales_analysis (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(50),
    month VARCHAR(7),
    total_sales DECIMAL(12,2),
    INDEX idx_category (category),
    INDEX idx_month (month)
);

-- 查看创建的临时表
SHOW TABLES;
-- 注意：临时表不会在 SHOW TABLES 中显示

-- 查看临时表结构
DESCRIBE temp_sales_summary;
DESCRIBE temp_high_value_sales;
                

临时表操作

插入数据

-- 向临时表插入数据
INSERT INTO temp_sales_summary (category, total_quantity, total_amount, avg_price)
SELECT 
    category,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total_amount,
    AVG(unit_price) as avg_price
FROM sales
GROUP BY category;

-- 查看临时表数据
SELECT * FROM temp_sales_summary;

-- 向备份临时表插入数据
INSERT INTO temp_sales_backup 
SELECT * FROM sales WHERE sale_date >= '2024-01-20';

-- 查看备份数据
SELECT * FROM temp_sales_backup;
                

更新和删除数据

-- 更新临时表数据
UPDATE temp_sales_summary 
SET avg_price = ROUND(avg_price, 2)
WHERE avg_price IS NOT NULL;

-- 删除临时表中的数据
DELETE FROM temp_high_value_sales 
WHERE total_value < 15000;

-- 查看更新后的数据
SELECT * FROM temp_sales_summary;
SELECT * FROM temp_high_value_sales;
                

查询临时表

-- 简单查询
SELECT * FROM temp_sales_summary ORDER BY total_amount DESC;

-- 连接查询（临时表与普通表）
SELECT 
    t.category,
    t.total_quantity,
    t.total_amount,
    COUNT(s.id) as transaction_count
FROM temp_sales_summary t
LEFT JOIN sales s ON t.category = s.category
GROUP BY t.category, t.total_quantity, t.total_amount;

-- 子查询中使用临时表
SELECT 
    product_name,
    total_value,
    CASE 
        WHEN total_value > (SELECT AVG(total_value) FROM temp_high_value_sales) 
        THEN '高于平均'
        ELSE '低于平均'
    END as value_level
FROM temp_high_value_sales;
                

临时表的高级应用

数据分析和报表

-- 创建月度销售分析临时表
CREATE TEMPORARY TABLE temp_monthly_analysis AS
SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') as month,
    category,
    COUNT(*) as transaction_count,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total_revenue,
    AVG(quantity * unit_price) as avg_transaction_value
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m'), category;

-- 查看月度分析结果
SELECT * FROM temp_monthly_analysis ORDER BY month, total_revenue DESC;

-- 创建销售人员绩效临时表
CREATE TEMPORARY TABLE temp_salesperson_performance AS
SELECT 
    salesperson,
    region,
    COUNT(*) as deals_count,
    SUM(quantity) as total_units_sold,
    SUM(quantity * unit_price) as total_sales,
    AVG(quantity * unit_price) as avg_deal_size,
    MAX(quantity * unit_price) as largest_deal
FROM sales
GROUP BY salesperson, region;

-- 查看销售人员绩效
SELECT 
    salesperson,
    region,
    total_sales,
    deals_count,
    ROUND(avg_deal_size, 2) as avg_deal_size,
    RANK() OVER (ORDER BY total_sales DESC) as sales_rank
FROM temp_salesperson_performance
ORDER BY total_sales DESC;
                

复杂数据处理

-- 创建产品分析临时表
CREATE TEMPORARY TABLE temp_product_analysis (
    product_name VARCHAR(100),
    category VARCHAR(50),
    total_sold INT,
    revenue DECIMAL(12,2),
    market_share DECIMAL(5,2),
    performance_level VARCHAR(20)
);

-- 插入产品分析数据
INSERT INTO temp_product_analysis (product_name, category, total_sold, revenue)
SELECT 
    product_name,
    category,
    SUM(quantity) as total_sold,
    SUM(quantity * unit_price) as revenue
FROM sales
GROUP BY product_name, category;

-- 计算市场份额
UPDATE temp_product_analysis t1
SET market_share = (
    SELECT ROUND((t1.revenue / SUM(t2.revenue)) * 100, 2)
    FROM temp_product_analysis t2
    WHERE t2.category = t1.category
);

-- 设置性能等级
UPDATE temp_product_analysis
SET performance_level = CASE 
    WHEN market_share >= 40 THEN '优秀'
    WHEN market_share >= 25 THEN '良好'
    WHEN market_share >= 15 THEN '一般'
    ELSE '需改进'
END;

-- 查看产品分析结果
SELECT * FROM temp_product_analysis ORDER BY category, market_share DESC;
                

数据清洗和转换

-- 创建数据清洗临时表
CREATE TEMPORARY TABLE temp_clean_customers AS
SELECT 
    id,
    TRIM(UPPER(name)) as clean_name,
    LOWER(TRIM(email)) as clean_email,
    REGEXP_REPLACE(phone, '[^0-9]', '') as clean_phone,
    TRIM(city) as clean_city,
    registration_date,
    CASE 
        WHEN registration_date >= '2024-01-01' THEN '新客户'
        WHEN registration_date >= '2023-01-01' THEN '老客户'
        ELSE '历史客户'
    END as customer_type
FROM customers;

-- 查看清洗后的数据
SELECT * FROM temp_clean_customers;

-- 创建数据验证临时表
CREATE TEMPORARY TABLE temp_data_validation AS
SELECT 
    id,
    clean_name,
    clean_email,
    clean_phone,
    clean_city,
    customer_type,
    CASE 
        WHEN clean_email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' 
        THEN '有效' ELSE '无效' 
    END as email_status,
    CASE 
        WHEN LENGTH(clean_phone) = 11 AND clean_phone REGEXP '^1[3-9][0-9]{9}$' 
        THEN '有效' ELSE '无效' 
    END as phone_status
FROM temp_clean_customers;

-- 查看验证结果
SELECT 
    customer_type,
    COUNT(*) as total_count,
    SUM(CASE WHEN email_status = '有效' THEN 1 ELSE 0 END) as valid_emails,
    SUM(CASE WHEN phone_status = '有效' THEN 1 ELSE 0 END) as valid_phones
FROM temp_data_validation
GROUP BY customer_type;
                

临时表与普通表的比较

特性	临时表	普通表
生命周期	会话结束时自动删除	手动删除或数据库删除
可见性	仅当前会话可见	所有会话可见
存储位置	内存或临时目录	数据库文件
索引支持	支持	支持
外键约束	不支持	支持
触发器	不支持	支持
复制	不参与主从复制	参与主从复制
备份	不包含在备份中	包含在备份中

临时表性能优化

内存配置

-- 查看临时表相关配置
SHOW VARIABLES LIKE 'tmp%';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'tmp_table_size';

-- 查看临时表使用统计
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- 临时表大小限制配置
-- tmp_table_size: 内存临时表的最大大小
-- max_heap_table_size: MEMORY 存储引擎表的最大大小
-- 临时表大小受这两个参数中较小值的限制

-- 设置临时表大小（需要适当权限）
-- SET GLOBAL tmp_table_size = 64 * 1024 * 1024;  -- 64MB
-- SET GLOBAL max_heap_table_size = 64 * 1024 * 1024;  -- 64MB
                

性能监控

-- 监控临时表创建情况
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
    'Created_tmp_tables',
    'Created_tmp_disk_tables',
    'Created_tmp_files'
);

-- 计算磁盘临时表比例
SELECT 
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_tables') * 100 
    AS disk_tmp_table_percentage;

-- 查看当前会话的临时表
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'TEMPORARY';
                

优化建议

-- 1. 为临时表创建适当的索引
CREATE TEMPORARY TABLE temp_optimized_sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    category VARCHAR(50),
    total_amount DECIMAL(12,2),
    sale_date DATE,
    INDEX idx_category (category),
    INDEX idx_date (sale_date),
    INDEX idx_amount (total_amount)
);

-- 2. 使用适当的数据类型
CREATE TEMPORARY TABLE temp_efficient_summary (
    category VARCHAR(50) NOT NULL,
    count_sales MEDIUMINT UNSIGNED,  -- 使用更小的整数类型
    total_amount DECIMAL(10,2),      -- 适当的精度
    avg_amount DECIMAL(8,2),
    PRIMARY KEY (category)
);

-- 3. 及时删除不需要的临时表
DROP TEMPORARY TABLE IF EXISTS temp_no_longer_needed;

-- 4. 批量操作而不是逐行操作
-- 好的做法：批量插入
INSERT INTO temp_efficient_summary
SELECT 
    category,
    COUNT(*),
    SUM(quantity * unit_price),
    AVG(quantity * unit_price)
FROM sales
GROUP BY category;

-- 避免的做法：逐行插入
-- INSERT INTO temp_table VALUES (...);  -- 重复多次
                

临时表的实际应用场景

1. 复杂报表生成

-- 生成综合销售报表
CREATE TEMPORARY TABLE temp_sales_report AS
SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') as month,
    region,
    category,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as revenue,
    AVG(quantity * unit_price) as avg_transaction
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m'), region, category;

-- 添加同比数据
ALTER TABLE temp_sales_report 
ADD COLUMN prev_month_revenue DECIMAL(12,2),
ADD COLUMN growth_rate DECIMAL(5,2);

-- 计算增长率（简化示例）
UPDATE temp_sales_report t1
SET prev_month_revenue = (
    SELECT revenue 
    FROM temp_sales_report t2 
    WHERE t2.region = t1.region 
      AND t2.category = t1.category 
      AND t2.month = DATE_FORMAT(DATE_SUB(STR_TO_DATE(CONCAT(t1.month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m')
);

UPDATE temp_sales_report 
SET growth_rate = CASE 
    WHEN prev_month_revenue > 0 
    THEN ROUND(((revenue - prev_month_revenue) / prev_month_revenue) * 100, 2)
    ELSE NULL 
END;

-- 生成最终报表
SELECT 
    month,
    region,
    category,
    revenue,
    prev_month_revenue,
    growth_rate,
    CASE 
        WHEN growth_rate > 10 THEN '快速增长'
        WHEN growth_rate > 0 THEN '稳定增长'
        WHEN growth_rate > -10 THEN '轻微下降'
        ELSE '显著下降'
    END as trend
FROM temp_sales_report
WHERE month = '2024-01'
ORDER BY region, category;
                

2. 数据迁移和ETL

-- 数据迁移示例
CREATE TEMPORARY TABLE temp_migration_staging (
    old_id INT,
    new_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    normalized_category VARCHAR(50),
    clean_price DECIMAL(10,2),
    migration_status VARCHAR(20) DEFAULT 'pending'
);

-- 第一步：提取和清洗数据
INSERT INTO temp_migration_staging (old_id, product_name, normalized_category, clean_price)
SELECT 
    id,
    TRIM(product_name),
    CASE 
        WHEN category IN ('手机', 'phone', 'mobile') THEN '移动设备'
        WHEN category IN ('笔记本', 'laptop', 'notebook') THEN '计算机'
        WHEN category IN ('平板', 'tablet', 'pad') THEN '移动设备'
        WHEN category IN ('耳机', 'headphone', 'earphone') THEN '音频设备'
        ELSE '其他'
    END,
    ROUND(unit_price, 2)
FROM sales
WHERE unit_price > 0;

-- 第二步：数据验证
UPDATE temp_migration_staging
SET migration_status = CASE 
    WHEN product_name IS NULL OR product_name = '' THEN 'invalid_name'
    WHEN clean_price <= 0 THEN 'invalid_price'
    WHEN normalized_category = '其他' THEN 'needs_review'
    ELSE 'valid'
END;

-- 第三步：查看迁移状态
SELECT 
    migration_status,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM temp_migration_staging), 2) as percentage
FROM temp_migration_staging
GROUP BY migration_status;

-- 第四步：迁移有效数据
-- INSERT INTO new_products_table 
-- SELECT new_id, product_name, normalized_category, clean_price
-- FROM temp_migration_staging
-- WHERE migration_status = 'valid';
                

3. 性能优化中间结果

-- 复杂查询优化示例
-- 原始复杂查询（可能很慢）
/*
SELECT 
    s1.region,
    s1.category,
    s1.total_sales,
    s2.avg_regional_sales,
    s3.category_rank
FROM (
    SELECT region, category, SUM(quantity * unit_price) as total_sales
    FROM sales GROUP BY region, category
) s1
JOIN (
    SELECT region, AVG(total_sales) as avg_regional_sales
    FROM (
        SELECT region, category, SUM(quantity * unit_price) as total_sales
        FROM sales GROUP BY region, category
    ) GROUP BY region
) s2 ON s1.region = s2.region
JOIN (
    SELECT category, 
           RANK() OVER (ORDER BY SUM(quantity * unit_price) DESC) as category_rank
    FROM sales GROUP BY category
) s3 ON s1.category = s3.category;
*/

-- 使用临时表优化
-- 第一步：创建基础聚合临时表
CREATE TEMPORARY TABLE temp_region_category_sales AS
SELECT 
    region,
    category,
    SUM(quantity * unit_price) as total_sales
FROM sales
GROUP BY region, category;

-- 第二步：创建区域平均销售临时表
CREATE TEMPORARY TABLE temp_regional_avg AS
SELECT 
    region,
    AVG(total_sales) as avg_regional_sales
FROM temp_region_category_sales
GROUP BY region;

-- 第三步：创建分类排名临时表
CREATE TEMPORARY TABLE temp_category_rank AS
SELECT 
    category,
    SUM(total_sales) as category_total,
    RANK() OVER (ORDER BY SUM(total_sales) DESC) as category_rank
FROM temp_region_category_sales
GROUP BY category;

-- 第四步：最终查询（更快）
SELECT 
    rcs.region,
    rcs.category,
    rcs.total_sales,
    ra.avg_regional_sales,
    cr.category_rank
FROM temp_region_category_sales rcs
JOIN temp_regional_avg ra ON rcs.region = ra.region
JOIN temp_category_rank cr ON rcs.category = cr.category
ORDER BY rcs.region, cr.category_rank;
                

删除临时表

-- 手动删除临时表
DROP TEMPORARY TABLE temp_sales_summary;
DROP TEMPORARY TABLE temp_high_value_sales;

-- 安全删除（如果存在才删除）
DROP TEMPORARY TABLE IF EXISTS temp_sales_backup;
DROP TEMPORARY TABLE IF EXISTS temp_monthly_analysis;

-- 批量删除多个临时表
DROP TEMPORARY TABLE IF EXISTS 
    temp_product_analysis,
    temp_salesperson_performance,
    temp_clean_customers,
    temp_data_validation;

-- 查看剩余的临时表
-- 注意：临时表不会在 SHOW TABLES 中显示
-- 可以通过 information_schema 查看
SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_TYPE = 'TEMPORARY' AND TABLE_SCHEMA = DATABASE();
                

                    自动清理：
                    会话结束时，所有临时表自动删除
连接断开时，临时表自动清理
MySQL 重启时，所有临时表丢失
不需要手动清理，但建议及时删除大型临时表

                

最佳实践和注意事项

                    最佳实践：
                    为临时表使用描述性的命名（如 temp_、tmp_ 前缀）
为大型临时表创建适当的索引
及时删除不再需要的临时表
监控临时表的内存使用情况
在复杂查询中使用临时表分解逻辑
使用适当的数据类型以节省空间

                

注意事项：

临时表不支持外键约束
临时表不参与主从复制
大量临时表可能消耗过多内存
临时表超过内存限制时会写入磁盘
不同会话的临时表完全隔离
临时表不包含在数据库备份中

场景	建议	原因
复杂报表	使用临时表分步处理	提高可读性和性能
数据清洗	创建中间临时表	便于验证和调试
大量计算	使用索引优化临时表	加速后续查询
会话结束	让系统自动清理	避免手动管理
内存不足	调整 tmp_table_size	避免频繁磁盘写入

⚡ MySQL 临时表

临时表概述

🔄 会话临时表

💾 内部临时表

创建临时表

基本语法

示例数据准备

创建临时表示例

临时表操作

插入数据

更新和删除数据

查询临时表

临时表的高级应用

数据分析和报表

复杂数据处理

数据清洗和转换

临时表与普通表的比较

临时表性能优化

内存配置

性能监控

优化建议

临时表的实际应用场景

1. 复杂报表生成

2. 数据迁移和ETL

3. 性能优化中间结果

删除临时表

最佳实践和注意事项