MySQL存储过程使⽤动态表名
MySQL 默认不⽀持表名作为变量名。
1）案例说明
若有⼀下存储过程：
drop procedure if exists selectByTableName;
create procedure selectByTableName(in tableName varchar(50))
begin
select * from tableName;
end;
在进⾏调⽤时会报错：
call selectByTableName('user')
> 1146 - Table 'db2020.tablename' doesn't exist
> 时间: 0s
原因是它把变量tableName 作为了表名，并不是把传⼊的值作为表名。
2）解决⽅案
解决⽅法是使⽤concat函数，然后⽤预处理语句传⼊动态表名来执⾏sql，对于增删改查都适⽤。
将上述的存储过程修改如下：
drop procedure if exists selectByTableName;
create procedure selectByTableName(in tableName varchar(50))
begin
#定义语句
set @stmt = concat('select * from ',tableName);
#预定义sql语句，从⽤户变量中获取
prepare stmt from @stmt;
#执⾏sql语句
execute stmt;
#释放资源，后续还可以使⽤
deallocate prepare stmt;
end;
再调⽤时就能正常查询出结果了。在预处理语句中，使⽤了⽤户变量，变量名是⾃定义的。
3）补充案例
若表结构⼀样，⽽表名是动态⽣成的，想收到删除所有的表会很⿇烦，可借助游标和存储过程进⾏删除。具体案例如下：
表1：t_table_log 记录动态的表名信息
CREATE TABLE `t_table_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tab_name` varchar(50) NOT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表2：动态⽣成的表，在存储过程中定义表结构
（1）使⽤存储过程动态⽣成最近5天的表
drop procedure if exists createTable20220118;
DELIMITER ;;
create procedure createTable20220118(in d int)
begin
declare tableName varchar(100);
declare curDay int default1;
table_loop:loop
if curDay > d then
leave table_loop;
else
set tableName = concat('test_',curDay);
set @stmt = concat('create table ',tableName,'(id int not null,profit float(8,2),total int,create_time datetime,primary key(id))ENGINE=InnoDB DEFAULT CHARSET=utf8');          prepare stmt from @stmt;
execute stmt;
deallocate prepare stmt;
set @stmt = concat('insert into t_table_log(tab_name,create_time) values("',tableName,'",sysdate())');
prepare stmt from @stmt;
execute stmt;
deallocate prepare stmt;
end if;
set curDay = curDay + 1;
end loop table_loop;
end;;
mysql存储过程使用DELIMITER ;
call createTable20220118(5);
需要注意的是，在插⼊数据时，表名需要加引号，否则编译器不会将其视为字符串。（2）使⽤存储过程删除表
drop procedure if exists dropTable20220118;
DELIMITER ;;
create procedure dropTable20220118()
begin
declare tableName varchar(100);
declare finished int default0;
declare cur_table cursor for
select tab_name from t_table_log;
declare continue handler for1329set finished = 1;
open cur_table;
table_loop:loop
fetch cur_table into tableName;
if finished = 1 then
leave table_loop;
else
set @stmt = concat('drop table if exists ',tableName);
prepare stmt from @stmt;
execute stmt;
deallocate prepare stmt;
end if;
end loop table_loop;
close cur_table;
end;;
DELIMITER ;
（3）调⽤存储过程
-- 调⽤存储过程
call dropTable20220118();
-- 删除存储过程
drop procedure if exists dropTable20220118;
-- 清空表
truncate table t_table_log;
上述案例中，不仅使⽤存储过程动态创建了表，也动态删除了表，⾮常的实⽤。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。