關于mysql如何創建和使用存盤程序，參考筆記《MySQL存盤程序和函式創建》以及官網：https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
本篇主要示例使用了輸入引數的存盤程序，并解決使用表名作為輸入引數的問題，因為之前遇到過需要使用表名作為引數的存盤程序，很難處理，
問題描述：
假設我們有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，
所以根據官網（https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html）提供的語法，對于上述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');
上述存盤程序經過了實測，可以正常的洗掉重復資料，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/125641.html
標籤：MySQL
上一篇：mysql57重新安裝后無法再次啟動mysql57服務“本地計算機上的MySQL服務啟動后停止。某些服務在未由其他服務或程式使用時將自動。”--解決方法