mysql - 如何从整个相关MySQL表中删除数据？

Question

我有3张 table

tbl_car
id | name
1 | toyota

tbl_brand
id | brand_name | **car_id** (one to many)
1 | brand1 | 1
2 | brand2 | 1


tbl_item
id | item_name | **brand_id** (one to many)
1 | brand1_item1 | 1
2 | brand1_item2 | 1
3 | brand2_item1 | 2
4 | brand2_item2 | 2

我想从 tbl_car 删除数据 id= 1，我尝试

删除 tbl_car a LEFT JOIN tbl_brand b ON a.id = b.car_id LEFT JOIN tbl_item c ON b.id = c.brand_id WHERE a.id = 1

但是没有成功。我想要的是删除 tbl_car id = 1 以及关系表中的整个数据。

Answer 1

你可以这样尝试:

DELETE a.*,b.*,c.* FROM tbl_car a LEFT JOIN tbl_brand b ON a.id = b.car_id LEFT JOIN tbl_item c ON b.id = c.brand_id WHERE a.id = 1

或者在表 tbl_car 中删除后尝试使用触发器

  delimiter $$
    CREATE TRIGGER `after_delete_Car`     
      AFTER DELETE ON `tbl_car`     
      FOR EACH ROW     
    BEGIN
      DELETE FROM tbl_brand WHERE car_id=OLD.id;
      DELETE FROM tbl_item WHERE brand_id=OLD.id;
    END
    $$
    delimiter ;