Refer to the notes MySQL Stored Procedures and Function Creation and the official website on how MySQL creates and uses stored procedures: https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
This main example uses stored procedures for input parameters and solves the problem of using table names as input parameters, since previously you encountered stored procedures that required table names as parameters and were difficult to handle.
Description of the problem:
Assuming we have 12 vehicle milestones of the same structure, TEST1-TEST12, and we want to weight these 12 tables, the logical simpler way is to write 12 stored procedure processes or write a stored procedure to rename and recompile each time it executes, but that's too cumbersome.
It's easy to think about whether you can use a table name as an input parameter so that you can execute a given table name each time.
So the initial stored procedure code is as follows:
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 ;
These stored procedures can be compiled properly, but execution will always report a table not exist error because mysql incorrectly treats the input variable table_name as the true database table name, which is obviously an error.
So how do you reference variables in SQL?One possible approach is to use dynamic SQL, spell variables into the SQL statement, and then execute dynamic SQL.
So according to the website ( https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html ) The syntax provided can be rewritten to the following format for the delete statement in the procedure above:
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;
//Note that prepare is currently only available in stored procedures, and neither functions nor triggers apply.
Ps: It should be noted that the official website uses? As a placeholder in the grammar of the sample prepare s, but it has been experimented with? It cannot be used as a placeholder for table names (in fact, the official website is just an example? It can be used as a placeholder for integer polygons, so I guess all database objects use it? It will fail as a placeholder) and I want to integrate table name variables.Only concat functions can be used in SQL, and the inputs to concat functions support local variables, user defined variables, and input variables.
Okay, the delete statement is processed, but what about the select statement in cursor?The official website clearly states that dynamic SQL cannot be used in cursors, that is, prepare statements cannot be used, so there is only a different way of thinking.
What does a cursor do?If you want to get a result set for traversal, can you use temporary tables instead of cursors to store the result set so that you can create temporary tables using dynamic SQL (mysql's temporary tables are session-level, different sessions can use temporary tables with the same name, and temporary tables are automatically deleted when a session is released):
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;
Then the logic of the entire stored procedure can be changed, because we put the intermediate result set in a temporary table, so there is no need to traverse the cursor, and even declare's local variables are omitted (because these local variables are used to store column values when traversing the cursor), only delete... Join is required, so the finalModify the stored procedure to:
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:
call Del_Dupilicate('TEST1');
These stored procedures have been tested and can normally remove duplicate data.