-- 查看所有支持的引擎
SHOW ENGINES;

graph LR
    A[Memory引擎] --> B[数据在内存]
    A --> C[表结构在磁盘]
    B --> D[读写极快]
    B --> E[重启丢失]
    C --> F[服务启动后
空表结构存在]

-- 场景1：临时中间表
CREATE TABLE temp_ranking ENGINE=MEMORY
AS SELECT user_id, SUM(score) AS total_score
FROM user_scores
GROUP BY user_id;

-- 场景2：字典/配置缓存
CREATE TABLE city_cache (
    city_id INT PRIMARY KEY,
    city_name VARCHAR(100),
    province VARCHAR(50)
) ENGINE=MEMORY;

SELECT * FROM city_cache WHERE city_id = 1;  -- 内存级速度

-- 键限制：不支持BLOB/TEXT列
CREATE TABLE t (content TEXT) ENGINE=MEMORY;
-- ERROR 1163: The used table type doesn't support BLOB/TEXT columns

-- 文件大小限制：受 max_heap_table_size 控制
SHOW VARIABLES LIKE 'max_heap_table_size';
-- 默认 16MB

-- 表锁：并发写入差
-- 表锁而非行锁

CREATE TABLE csv_export ENGINE=CSV
AS SELECT id, name, email FROM users;

-- 查看文件
-- /var/lib/mysql/db/csv_export.CSV

# 实际文件内容
1,张三,zhangsan@example.com
2,李四,lisi@example.com

-- 数据交换：导出数据给其他系统
CREATE TABLE data_exchange ENGINE=CSV
AS SELECT * FROM source_table;

-- 其他系统可以直接读取 CSV 文件

-- 注意：CSV 引擎不支持索引！
CREATE INDEX idx_name ON data_exchange (name);
-- ERROR: CSV engine does not support indexes

-- 创建归档表
CREATE TABLE log_archive (
    id INT AUTO_INCREMENT,
    log_time DATETIME,
    message TEXT,
    PRIMARY KEY (id)
) ENGINE=ARCHIVE;

-- 日志归档
-- 把7天前的热数据归档到Archive表
INSERT INTO log_archive
SELECT * FROM active_logs
WHERE created_at < NOW() - INTERVAL 7 DAY;

DELETE FROM active_logs
WHERE created_at < NOW() - INTERVAL 7 DAY;

-- 后续查询归档数据
SELECT COUNT(*) FROM log_archive
WHERE log_time BETWEEN '2024-01-01' AND '2024-01-31';
-- 虽然慢，但数据量被压缩80%，磁盘友好

flowchart TD
    A{数据特点} --> B{需要持久化?}
    B -->|否| C[Memory引擎]
    B -->|是| D{需要索引?}
    D -->|否| E{只读归档?}
    E -->|是| F[Archive引擎]
    E -->|否| G{CSV导出?}
    G -->|是| H[CSV引擎]
    G -->|否| I[InnoDB]
    D -->|是| J[InnoDB]

-- Memory + InnoDB 组合用法
-- 先缓存到 Memory 做快速计算，再持久化到 InnoDB

-- 1. 创建Memory临时表
CREATE TABLE tmp_rank ENGINE=MEMORY
SELECT user_id, SUM(revenue) AS total
FROM daily_sales
WHERE sale_date = CURDATE()
GROUP BY user_id;

-- 2. 做计算（极快）
UPDATE tmp_rank SET rank = @r:=@r+1;

-- 3. 持久化结果
INSERT INTO daily_rank
SELECT * FROM tmp_rank;

-- 4. 清理
DROP TABLE tmp_rank;