mysql数据库级联删除多表数据

mysql级联删除多表数据

方法1：通过sql删除(注意：关联两表删除可用，关联多表用不了)

delete table1,table2 from table1,table2 where table1.id=table2.id and table1.id=#{id}

方法2：通过设置外键级联删除

方法3：设置触发器

--删除触发器
DROP TRIGGER IF EXISTS trig_<触发器名>;

-- 查看此数据库中的触发器
SHOW TRIGGERS;

--设置触发器
DELIMITER $
CREATE TRIGGER trig_<触发器名> BEFORE delete on project for each row
BEGIN
			DELETE FROM document WHERE pid = old.pid;
			DELETE FROM user WHERE user.pid = old.pid;
			DELETE FROM strategy WHERE strategy.pid = old.pid;
			DELETE FROM label WHERE label.pid = old.pid;
END $ 

delete from project where pid=120;