45U8nRzx

1DROP PROCEDURE IF EXISTS rotateAudit;
2delimiter ;;
3CREATE PROCEDURE rotateAudit(
4  pv_database VARCHAR(64),
5  pv_table VARCHAR(64)
6)
7BEGIN
8        SET @createDbStatement := CONCAT('CREATE DATABASE IF NOT EXISTS archive_',pv_database);
9        SET @createStatement := CONCAT('CREATE TABLE ',pv_database,'.new_',pv_table,' LIKE ',pv_database,'.',pv_table);
10        SET @insertStatement := CONCAT('INSERT INTO ',pv_database,'.new_',pv_table,' SELECT * FROM ',pv_database,'.',pv_table,' ORDER BY id DESC LIMIT 50000');
11        SET @renameStatement := CONCAT('RENAME TABLE ',pv_database,'.',pv_table,' TO archive_',pv_database,'.old_',pv_table,', ',pv_database,'.new_',pv_table,' TO ',pv_database,'.',pv_table);
12
13        PREPARE createDbStatement FROM @createDbStatement;
14        PREPARE createStatement FROM @createStatement;
15        PREPARE renameStatement FROM @renameStatement;
16 
17        EXECUTE createDbStatement;
18        EXECUTE createStatement;
19        PREPARE insertStatement FROM @insertStatement;
20 
21        START TRANSACTION;
22                SELECT AUTO_INCREMENT+1000 INTO @autoInc
23                        FROM information_schema.tables
24                        WHERE table_name=pv_table
25                        AND table_schema=pv_database;
26                SET @changeStatement := CONCAT('ALTER TABLE ',pv_database,'.new_',pv_table,' AUTO_INCREMENT = ',@autoInc);
27                PREPARE changeStatement FROM @changeStatement;
28                EXECUTE insertStatement;
29                EXECUTE changeStatement;
30                -- EXECUTE renameStatement;
31        COMMIT;
32        DEALLOCATE PREPARE changeStatement;
33        DEALLOCATE PREPARE renameStatement;
34        DEALLOCATE PREPARE insertStatement;
35        DEALLOCATE PREPARE createStatement;
36        DEALLOCATE PREPARE createDbStatement;
37END;;
38delimiter ;