自定义存储过程完成表数据转移
公司业务需要,需要转移表中的数据到历史表中,历史表与原表只多一个task_id的字段,由于涉及表众多,自己觉得自定义个存储过程来实现。
实现思路
1.由于每个表的列不同,查询操作表的列,拼接
2.要转移的数据有条件限制,需要输出参数来进行筛选要转移的数据
3.转移到新的表中有个参数task_id, 需要输入参数多一个task_id
4.转移的表到表,有两个表名 ,需要输入参数多两个表名
综上,
数据参数: 4个,task_id(任务id),condtions(筛选条件), table_names(原表名), new_table_names(转移到的表名)
过程:先查询表的列名拼接 ,再进行数据转移(主要是sql的拼接问题)
代码
CREATE DEFINER="root"@"%" PROCEDURE "copyData"(IN table_names varchar(1000),IN new_table_names varchar(1000),IN task_id bigint,IN condtions varchar(2000))
COMMENT '转移表名table_names的表中符合conditions的数据到表名new_table_names中去,添加新的列为task'
BEGIN
#查询表的列,拼接
set @sql = CONCAT('SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME SEPARATOR '','') INTO @temCol FROM information_schema.COLUMNS WHERE TABLE_SCHEMA =''ssep_test'' and TABLE_NAME =','''',table_names,'''');
#测试时使用 INSERT into tem_test (content) values (@sql);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
#转移数据
set @sql2 = CONCAT('insert into ',new_table_names,' (', @temCol ,',task_id)' ,' select ',@temCol ,',',task_id, ' from ',table_names ,' where ', condtions);
#测试时使用 into tem_test (content) values(@sql2);
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END
执行
如下 navicat 的执行窗口
注意:整个过程中出现各种问题:调试不支持debug,可以在过程中执行sql插入到一些表中查看执行情况(#测试时使用 into tem_test (content) values(@sql2);)