Mysql 存储过程生成历史表并转储数据
最近写了个存储过程用于转储数据到历史表, 做个简单的记录
业务场景 : 有一张原表, 表结构包含主键自增id, 可重复设备号, 设备信息入库时间以及若干设备信息字段. 现要求根据设备入库时间降序, 在原表中保留500个不同设备号的记录, 其余设备信息按入库时间存入以年为单位的历史表中
存储过程实现 :
1 CREATE DEFINER=`root`@`localhost` PROCEDURE `DumpHistoryData`() 2 BEGIN 3 #起始id,跳过最近500条不重复的设备号 4 SELECT 5 COUNT(DISTINCT(test_table.id)) INTO @begin_id 6 FROM 7 test_table, 8 (SELECT DISTINCT(device_number) FROM `test_table` ORDER BY device_date DESC LIMIT 500) AS spare_device 9 WHERE 10 test_table.device_number = spare_device.device_number 11 ORDER BY 12 device_date DESC; 13 14 #起始年限 15 SET @curr_limit = DATE_FORMAT(now(), '%Y-01-01 00:00:00'); 16 SET @table_year = DATE_FORMAT(now(), '%Y'); 17 18 #开始年份循环 19 dump_year: LOOP 20 21 #检查当前年份历史表是否存在,不存在则创建一个表结构与原表相同的历史表 22 SET @history_table = CONCAT( @table_year, '_history_test_table' ); 23 SELECT COUNT(*) INTO @is_table_exist FROM information_schema.TABLES WHERE table_name = @history_table; 24 IF @is_table_exist = 0 THEN 25 SET @create_history_table = CONCAT('CREATE TABLE ', @history_table, ' LIKE `test_table`'); 26 PREPARE generate_table FROM @create_history_table; 27 EXECUTE generate_table; 28 DEALLOCATE PREPARE generate_table; 29 END IF; 30 31 #开始数据转储循环 32 dump_data: LOOP 33 34 #创建待转储数据的临时表 35 SET @generate_dump_table = 'CREATE TEMPORARY TABLE need_dump AS (SELECT id FROM test_table WHERE device_date >= ? ORDER BY device_date DESC LIMIT ?,1000)'; 36 PREPARE generate_temp FROM @generate_dump_table; 37 EXECUTE generate_temp USING @curr_limit,@begin_id; 38 DEALLOCATE PREPARE generate_temp; 39 40 #检查转储临时表中是否有数据 41 SELECT COUNT(id) INTO @dump_data_remain FROM need_dump; 42 43 #执行转储,转储后将原表中与历史表主键相同的数据从原表中删除 44 IF @dump_data_remain != 0 THEN 45 SET @insert_history = concat('INSERT INTO ', @history_table, ' SELECT test_table.* FROM `test_table` JOIN need_dump ON test_table.id = need_dump.id'); 46 SET @clear_history = concat('DELETE FROM `test_table` USING `test_table`, `', @history_table,'` WHERE test_table.id = ', @history_table, '.id'); 47 PREPARE insert_sql FROM @insert_history; 48 PREPARE delete_sql FROM @clear_history; 49 EXECUTE insert_sql; 50 EXECUTE delete_sql; 51 DEALLOCATE PREPARE insert_sql; 52 DEALLOCATE PREPARE delete_sql; 53 END IF; 54 55 #清理临时表 56 DROP TABLE need_dump; 57 58 #如果转储临时表中没有数据,则退出循环 59 IF @dump_data_remain = 0 THEN 60 LEAVE dump_data; 61 END IF; 62 END LOOP dump_data; 63 64 #当前年限向前推1年 65 SELECT DATE_SUB(@curr_limit, INTERVAL 1 YEAR) INTO @curr_limit; 66 SELECT DATE_FORMAT(@curr_limit, '%Y') INTO @table_year; 67 68 #如果前推1年的年份早于原表中最早的年份,则退出循环 69 IF @table_year < (SELECT DATE_FORMAT(device_date,'%Y') FROM test_table ORDER BY device_date ASC LIMIT 1) THEN 70 LEAVE dump_year; 71 END IF; 72 END LOOP dump_year; 73 END
业务没有很强的性能要求, 存储过程内容比较简单, 但还是从中学到了很多Sql语法层面的知识, 仅此做个简单记录
