保存并复用查询结果¶
在同一会话中执行;LAST_QUERY_ID() 是会话上下文,先保存 ID,避免后续语句改变它。使用 MySQL 命令行客户端时要加 --comments,否则客户端会去掉 /* save_result */ 标记。
CREATE DATABASE cookbook_result;
CREATE TABLE cookbook_result.sales(id INT, amount INT);
INSERT INTO cookbook_result.sales VALUES(1,10),(2,20);
SET save_query_result = ON;
/* save_result */ SELECT id,amount FROM cookbook_result.sales ORDER BY id;
SET @saved_id = LAST_QUERY_ID();
SELECT id,amount FROM RESULT_SCAN(@saved_id) AS r ORDER BY id;
SELECT COUNT(*) AS saved_rows FROM RESULT_SCAN(@saved_id) AS r;
SELECT COUNT(*) AS metadata_rows FROM META_SCAN(@saved_id) AS m;
SET save_query_result = OFF;
DROP DATABASE cookbook_result;
复用结果应为 (1,10)、(2,20),saved_rows=2,并应有一条结果元数据。保存结果的大小、权限和生命周期受系统配置约束;参见源码 结果复用测试。