MYSQL 动态修改表面及动态变量查询 表

#修改表名
set @tableName = CONCAT('test_user'); 
SET @sqlstr = CONCAT('ALTER TABLE ',@tableName,' RENAME TO ',@tableName,'_',DATE_FORMAT(CURDATE(),'%Y_%m_%d'));
#预定义一个语句，并将它赋给 stmt
PREPARE stmt FROM @sqlstr ;
EXECUTE stmt ;


#方法一 查询修改的表数据 只查 10 条
#set @newtableName = CONCAT('test_user','_',DATE_FORMAT(CURDATE(),'%Y_%m_%d')); 
set @selectstr = CONCAT('select * from ',@tableName,'_',DATE_FORMAT(CURDATE(),'%Y_%m_%d'),' limit 10 ');
PREPARE stmt FROM @selectstr;
EXECUTE stmt;

#方法二 查询修改的表数据 ,' limit 10 '
set @newtableName = CONCAT('test_user','_',DATE_FORMAT(CURDATE(),'%Y_%m_%d'),' limit 10 '); 
set @selectstr = CONCAT('select * from ',@newtableName);
PREPARE stmt FROM @selectstr;
EXECUTE stmt;

#==================  修改后的表，名字重新改回去
SET @sqlstr = CONCAT('ALTER TABLE ',@tableName,'_',DATE_FORMAT(CURDATE(),'%Y_%m_%d'),' RENAME TO ',@tableName);
PREPARE stmt FROM @sqlstr ;
EXECUTE stmt ;

set @selectstr = CONCAT('select * from ',@tableName,' limit 10 ');
PREPARE stmt FROM @selectstr;
EXECUTE stmt;



#根据user 表 创建出一个按当日日期的备份表
SET @sqlstr = CONCAT('create table user_',DATE_FORMAT(CURDATE(),'%Y_%m_%d'),' like user');
PREPARE stmt FROM @sqlstr ;
EXECUTE stmt ;
#从源表 user 中查出数据，写入到按当日日期的备份表中
SET @sqlstr = CONCAT('insert into user_',DATE_FORMAT(CURDATE(),'%Y_%m_%d'),' select * from user ');
PREPARE stmt FROM @sqlstr ;
EXECUTE stmt ;