站点图标 IDC铺

使用动态SQL处理table_name作为输入参数的存储过程（MySQL）详细方案教程

小小编

本篇主要示例使用了输入参数的存储过程，并解决使用表名作为输入参数的问题，因为之前遇到过需要使用表名作为参数的存储过程，很难处理。

问题描述：

假设我们有TEST1-TEST12共12个相同结构的车辆里程表，我们想要对这12个表进行去重，那么逻辑上比较简单的办法是写12个存储过程处理或者写一个存储过程每执行一次改一次表名并重新编译，但是这样都太麻烦了。

接下来很容易的就会想到是否可以使用表名作为输入参数，这样每次执行给定表名即可。

因此初始的存储过程代码如下：

DELIMITER //
DROP PROCEDURE IF EXISTS Del_Dupilicate;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Del_Dupilicate`(in table_name varchar(64))
BEGIN
DECLARE v_min_id,v_group_count INT;
DECLARE v_get_on_time,v_get_off_time DATETIME;
DECLARE v_car_no VARCHAR(255);
DECLARE done INT DEFAULT FALSE;
DECLARE my_cur CURSOR FOR SELECT get_on_time,get_off_time,car_no,min(id),count(1) AS count FROM table_name GROUP BY get_on_time,get_off_time,car_no HAVING count>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN my_cur;
myloop: LOOP
FETCH my_cur INTO v_get_on_time,v_get_off_time,v_car_no,v_min_id,v_group_count;
IF done THEN
LEAVE myloop;
END IF;
DELETE FROM table_name WHERE get_on_time=v_get_on_time AND get_off_time=v_get_off_time AND car_no=v_car_no AND id>v_min_id;
COMMIT;
END LOOP;
CLOSE my_cur;
END;
//
DELIMITER ;

上述存储过程可以正常编译，但是执行却一定会报table not exist的错误，因为mysql会错误的把输入变量table_name当做真正的数据库表名，这显然是错误的。

那么如何在SQL中引用变量呢？一个可行的办法是使用动态SQL，把变量拼入SQL语句中然后执行动态SQL。

procedure中的delete语句可以改写成如下格式：

set @del_sql=concat('DELETE FROM ',table_name,' WHERE get_on_time=',v_get_on_time,' AND get_off_time=',v_get_off_time,' AND car_no=',v_car_no,' AND id>',v_min_id)
PREPARE stmt FROM @del_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
//注意prepare目前只能在存储过程中使用，函数和触发器都不适用。

Ps：需要注意的是官网在示例prepare的语法时使用了?作为占位符，但是经试验?不能作为表名的占位符（实际上官网只示例了?可以作为整数字面量的占位符，我猜测凡是数据库对象用?作为占位符都会报错），想要将表名变量整合入SQL中只能使用concat函数，concat的函数的输入支持local variables、user defined variables和input variables。

好，delete语句处理完毕，但是对于cursor中的select语句呢？官网明确说明游标中不能使用动态SQL，也就是不能使用prepare语句，那只能换一种思路了。

游标的作用是什么呢？是获取一个结果集以便进行遍历，那么可否使用临时表代替游标来存储结果集，这样可以使用动态SQL创建临时表(mysql的临时表是session级别的，不同会话可以使用相同名称的临时表，会话释放时临时表自动删除)：

set @tmp_table_name=concat(table_name,'_tmp');
set @cur_sql=concat('create temporary table ',@tmp_table_name,' as select get_on_time,get_off_time,car_no,min(id) as min_id,count(1) AS count FROM ',table_name,' GROUP BY get_on_time,get_off_time,car_no HAVING count>1');
PREPARE stmt FROM @cur_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

然后整个存储过程的逻辑就可以更改了，因为我们把中间结果集存入了临时表，那就无需遍历cursor了，同时连declare的local variables也省了（因为这些本地变量是用于遍历游标时存储列值的），只需要delete … join即可，因此最终的存储过程修改为：

CREATE DEFINER=`root`@`localhost` PROCEDURE `Del_Dupilicate`(in table_name varchar(64))
BEGIN

set @tmp_table_name=concat(table_name,'_tmp');

set @cur_sql=concat('create temporary table ',@tmp_table_name,' as select get_on_time,get_off_time,car_no,min(id) as min_id,count(1) AS count FROM ',table_name,' GROUP BY get_on_time,get_off_time,car_no HAVING count>1');
PREPARE stmt FROM @cur_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

set @del_sql=concat('delete a from ',table_name,' a join ',@tmp_table_name,' b on a.get_on_time=b.get_on_time and a.get_off_time=b.get_off_time and a.car_no=b.car_no and a.id != b.min_id');
PREPARE stmt FROM @del_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

set @drop_tmp_sql=concat('drop temporary table ',@tmp_table_name);
PREPARE stmt FROM @drop_tmp_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

调用：

call Del_Dupilicate('TEST1');

上述存储过程经过了实测，可以正常的删除重复数据。

退出移动版