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 default 1;
     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;;
DELIMITER ;

call createTable20220118(5);

需要注意的是，在插入数据时，表名需要加引号，否则编译器不会将其视为字符串。

（2）使用存储过程删除表

drop procedure if exists dropTable20220118;
DELIMITER ;;
create procedure dropTable20220118()
begin
         declare tableName varchar(100);
         declare finished int default 0;
         
         declare cur_table cursor for 
            select tab_name from t_table_log;
         declare continue handler for 1329 set 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;

上述案例中，不仅使用存储过程动态创建了表，也动态删除了表，非常的实用。