mysql 存储过程 动态建表_mysql 存储过程动态创建表并插入数据

CREATE DEFINER = `root`@`%%` PROCEDURE `backup_data`()

BEGIN

-- 动态创建表

SET @backupMonth = DATE_FORMAT(date_sub(curdate(),interval 1 MONTH),'%Y%m');

SET @tableName = CONCAT("backup_tzdr_fund_" ,@backupMonth);

SET @createSql = CONCAT('create table ',@tableName," (

`id` VARCHAR(32) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=UTF8");

PREPARE createSql FROM @createSql;

EXECUTE createSql;

-- 动态创建表

-- 插入数据

SET @insertSql = CONCAT('INSERT INTO ',@tableName,"

(`id`) SELECT id from w_user where FROM_UNIXTIME(ctime,'%Y%m')=",

@backupMonth);

PREPARE insertSql FROM @insertSql;

EXECUTE insertSql;

-- 插入数据

END;