mysql - 使用其他表的列从表中删除
问题描述
我创建了一个过程,在该过程中我创建了一个临时表来存储列的值,然后我想使用这些列值来删除其他表中的数据。如何在 while 循环中执行此操作,或者我可以简单地使用 join 从 AppCoverLetter、AppError 中删除行,哪种方式更好?
CREATE DEFINER=`root`@`localhost` PROCEDURE `gdpr_delete`(_email_ varchar(128))
BEGIN
DECLARE NumberRecords int;
DECLARE RowCount int ;
-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE GdprDeleteData (AppId INT NOT null);
-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO GdprDeleteData (AppId) SELECT AppId FROM ApplyData.AppFormData where lower(Email) = lower(_email_);
-- Get the number of records in the temporary table
SET NumberRecords = ROW_COUNT();
SET RowCount = 1;
select NumberRecords, RowCount;
-- loop through all records in the temporary table
-- using the WHILE loop construct
start transaction;
DELETE AppCoverLetter FROM AppCoverLetter inner JOIN GdprDeleteData ON AppCoverLetter.AppID = GdprDeleteData.AppId;
DELETE AppError FROM AppError inner JOIN GdprDeleteData ON AppError.AppID = GdprDeleteData.AppId;
DELETE AppFormData FROM AppFormData inner JOIN GdprDeleteData ON AppFormData.AppID = GdprDeleteData.AppId;
DELETE AppJobData FROM AppJobData inner JOIN GdprDeleteData ON AppJobData.AppID = GdprDeleteData.AppId;
DELETE AppTrackingData FROM AppTrackingData inner JOIN GdprDeleteData ON AppTrackingData.AppID = GdprDeleteData.AppId;
DELETE FlowLog FROM FlowLog inner JOIN GdprDeleteData ON FlowLog.AppID = GdprDeleteData.AppId;
DELETE App FROM App inner JOIN GdprDeleteData ON App.AppID = GdprDeleteData.AppId;
DELETE AppCoverLetter FROM AppCoverLetter inner JOIN GdprDeleteData ON AppCoverLetter.AppID = GdprDeleteData.AppId;
DELETE AppResume FROM AppResume inner JOIN GdprDeleteData ON AppResume.AppID = GdprDeleteData.AppId;
commit;
-- drop the temporary table
drop table GdprDeleteData;
END
解决方案
您可以避免 mysql 中的临时表和循环,您可以在单个查询中从两个表中删除,并对临时数据使用 sunquery
DELETE t1, t2
FROM AppCoverLetter t1
INNER JOIN (
SELECT AppId
FROM ApplyData.AppFormData
where lower(Email) = lower(_email_)
) t3 ON t1.AppID = t3.AppId
INNER JOIN AppError t2 ON t2.AppID = t3.AppId;
或者如果你有更多的桌子
DELETE t1, t2, tx
FROM AppCoverLetter t1
INNER JOIN (
SELECT AppId
FROM ApplyData.AppFormData
where lower(Email) = lower(_email_)
) t3 ON t1.AppID = t3.AppId
INNER JOIN AppError t2 ON t2.AppID = t3.AppId
INNER JOIN table3 tx ON tx.AppID = t3.AppId;
推荐阅读
- vue.js - 在 Vue.js 中进行样式设置的最佳且简单的方法是什么?我可能认为引导程序是最好的选择
- wordpress - Wordpress:如何只允许特定角色进行一些评论操作(删除、编辑...)?
- c++ - 在下面的代码中,外部块 m 如何给出输出 20
- hybris - 访问 Hybris 后台会导致系统冻结几分钟
- php - api json响应不是以数组形式出现
- vim - 跳转到系统verilog宏的问题`使用ctags在VIM中定义
- php - Laravel 中受密码保护的视图
- javascript - 为什么在 package.json 中添加了一个空行
- ignite - 点燃服务器:“以下缓存的所有服务器节点都已离开集群”
- angular - Angular:*ngFor 在父组件或子组件中?