创建存储过程
CREATE PROCEDURE `backup_table`( IN tableName CHAR ( 100 ) )
BEGIN
SET @backupTable = CONCAT( tableName, '_bak_',DATE_FORMAT(NOW(), '%Y%m%d%k%i%s') );
SET @stmt = CONCAT( "create table ", @backupTable, " like ", tableName, ";" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt;
SET @stmt = CONCAT( "INSERT into ", @backupTable, " SELECT * from ", tableName, ";" );
PREPARE stmt
FROM
@stmt;
EXECUTE stmt;
END
初始化测试用数据
CREATE TABLE `test` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `flink`.`test`(`id`, `name`) VALUES (1, '1www');
INSERT INTO `flink`.`test`(`id`, `name`) VALUES (2, NULL);
INSERT INTO `flink`.`test`(`id`, `name`) VALUES (3, '4zabv');
执行存储过程
CALL backup_table ( "test" );
最终可以看到备份表。