需求:根据projectId删除数据库所有表中的数据。
在进行尝试后发现delete from table1,table2 where project_id = '111’这种方式并不能删除多表,于是想遍历符合条件的数据库来分别执行sql。在参考了(cv大法)mysql表遍历后实现了该功能。
-- 创建存储过程前先检查是否存在,存在就删除
DROP PROCEDURE IF EXISTS scrity;
-- 存储过程
CREATE PROCEDURE scrity(in db_name VARCHAR(64), in project_id VARCHAR(64))
BEGIN
-- 该变量用于标识是否还有数据需遍历
DECLARE flag INT DEFAULT 0;
-- 创建一个变量用来存储遍历过程中的值
DECLARE tablename varchar(255);
-- 查询出需要遍历的数据集合,这里查询出了有project_id列的所有表。
DECLARE table_list CURSOR FOR select table_name from information_schema.COLUMNS where table_schema = db_name AND column_name = 'project_id';
-- 查询是否有下一个数据,没有将标识设为1,相当于hasNext
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
-- 打开游标
OPEN table_list ;
-- 取值设置到临时变量中,我这里就一个字段
-- 注意:多个字段取值,变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
FETCH table_list INTO tablename ;
-- 遍历未结束就一直执行
WHILE flag != 1 DO
-- targetSQL //你想要执行的目标功能,这里可以写多个SQL
-- 注意
-- 目标语句引用临时变量,实测发现不需要加@符号,但是搜索到的结果都是例如:@id ,这样来使用,
-- 我们这里直接用变量作为表明代码如下。
-- 注意
-- 定义sql语句,数据库字段进行编辑
set @sql = CONCAT("DELETE FROM `",db_name,"`.", tablename, " WHERE project_id = '",project_id,"'");
-- 从@sql准备stmt;
prepare stmt from @sql;
-- 执行stmt;
execute stmt;
-- 结束循环,下面这条语句会终止循环,如果不需要请注释
drop prepare stmt;
-- 把游标向后移一位
FETCH table_list INTO tablename ;
-- 当s等于1时表明遍历以完成,退出循环
END WHILE;
-- 关闭游标
CLOSE table_list ;
END;
-- 执行存储过程
call scrity('test','111')