mysql 删除多张表的数据 mysql删除多表关联数据

今天在做删除单个program时，此program可能关联多个表，需要将记录一起删除。
本来以为只能每个表去分别删除，后来了解到MySQL可以多表关联删除数据。

详解

1、从数据表t1中把那些id值在数据表t2里有匹配的记录全删除 掉

DELETE t1 FROM t1,t2 WHERE t1.id=t2.id 或 DELETE FROM t1 USING t1,t2 WHERE t1.id=t2.id

2、从数据表t1里在数据表t2里没有匹配的记录查找出来并删除掉

DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL 或 DELETE FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL

3、 从两个表中找出相同记录的数据并把两个表中的数据都删除掉

DELETE t1,t2 from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25

注意此处的delete t1,t2 from 中的t1,t2不能是别名（MYSQL 版本不小于5.0是可以的）

实例

DELETE 
	p, c, d, a, r, rp, rb, rr
FROM
	kid_punch_program p
LEFT JOIN kid_punch_program_child c ON c.program_id=p.id
LEFT JOIN kid_punch_program_total_data d ON d.program_id=p.id
LEFT JOIN kid_punch_summary_article a ON a.program_id=p.id
LEFT JOIN rec_reading_record_v2 r ON r.program_id=p.id
LEFT JOIN rec_reading_record_v2_people rp ON rp.reading_record_id=r.id
LEFT JOIN rec_reading_record_v2_book rb ON rb.reading_record_id=r.id
LEFT JOIN rec_reading_record_v2_resource rr ON rr.reading_record_id=r.id
WHERE
	p.id=#{programId}