DELIMITER $$
CREATE PROCEDURE MergeDataFromTables()
BEGIN
-- 游标声明
DECLARE done INT DEFAULT FALSE;
DECLARE tbl_name VARCHAR(255);
DECLARE cur CURSOR FOR SELECT 表明 FROM table_col;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 创建临时表,结构与目标表相同
DROP TEMPORARY TABLE IF EXISTS temp_result;
CREATE TEMPORARY TABLE temp_result LIKE table1;
-- 开启游标
OPEN cur;
-- 循环读取表名并执行查询
read_loop: LOOP
FETCH cur INTO tbl_name;
IF done THEN
LEAVE read_loop;
END IF;
-- 动态构建并执行SQL查询
SET @sql = CONCAT('INSERT INTO temp_result SELECT * FROM ', tbl_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
-- 关闭游标
CLOSE cur;
-- 从临时表中选择并显示合并结果
SELECT * FROM temp_result;
END$$
DELIMITER ;
CALL MergeDataFromTables();
CREATE PROCEDURE MergeDataFromTables()BEGIN -- 游标声明 DECLARE done INT DEFAULT FALSE; DECLARE tbl_name VARCHAR(255); DECLARE cur CURSOR FOR SELECT 表明 FROM table_col; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 创建临时表,结构与目标表相同 DROP TEMPORARY TABLE IF EXISTS temp_result; CREATE TEMPORARY TABLE temp_result LIKE table1;
-- 开启游标 OPEN cur;
-- 循环读取表名并执行查询 read_loop: LOOP FETCH cur INTO tbl_name; IF done THEN LEAVE read_loop; END IF;
-- 动态构建并执行SQL查询 SET @sql = CONCAT('INSERT INTO temp_result SELECT * FROM ', tbl_name); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP;
-- 关闭游标 CLOSE cur;
-- 从临时表中选择并显示合并结果 SELECT * FROM temp_result;END$$
DELIMITER ;
CALL MergeDataFromTables();