MySQL 数据库单表备份存储过程

创建存储过程

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" );

最终可以看到备份表。