使⽤动态SQL处理table_name作为输⼊参数的存储过程（MySQL）
关于mysql如何创建和使⽤存储过程，参考笔记《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;
mysql的存储过程怎么理解//注意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__on_time _off__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');
上述存储过程经过了实测，可以正常的删除重复数据。
发表评论