如何在MySQL MyISAM存储引擎上使用删除级联? [英] How to use delete cascade on MySQL MyISAM storage engine?

问题描述

我有一个被称为 equipment 的表，还有另外八个被称为 equipment_child1 的表，依此类推，直到 equipment_child8 . /p>

所有表之间的commom字段为 cod_equip ，通过该字段，我可以使用 equipment 来标识我所有的 child equipment 表.父表.

移动设备时，我需要从设备中删除数据，但是我需要将所有表equipment_child1到equipment_child8中的数据删除

然后我记得我曾在innoDB引擎中使用 DELETE CASCADE ，但是现在我正在使用MyISAM engina，这是一个问题吗?

任何帮助，都将真正澄清...

解决方案

是.简而言之，您无法使用该引擎.

编辑.您可以编写触发器，一旦删除表中的记录，便会删除所有其他表中的所有子记录.

好的.我给你写了一个例子:

 create table tab1 (
 id int )
 engine = myisam;

insert into tab1 values (1),(2),(3),(4); 

 create table tab2(
 id int not null auto_increment primary key,
 id_tab1 int
 ) engine = myisam;

 insert into tab2 (id_tab1) values (1),(2),(2),(3),(4);

 create table tab3(
 id int not null auto_increment primary key,
 id_tab1 int
 ) engine = myisam;

  insert into tab3 (id_tab1) values (1),(2),(2),(3),(2);


delimiter //
create trigger deletecascade after delete on tab1
for each row
begin
delete from tab2 where id_tab1 = old.id;
delete from tab3 where id_tab1 = old.id;
end; //
delimiter ;

delete from tab1 where id = 2;

希望有帮助.

编辑.显然，即使您同时从table1中删除了更多ID，它也可以正常工作:

delete from tab1 where id in (2,3,4);

I have one table that I had called equipment, and 8 other tables that I had called equipment_child1 and so on until equipment_child8.

The commom field between all that tables is cod_equip, with this field I 'm able to identify all my child equipment tables with equipment parent table.

I need to delete data from equipment when the equipment is moved, but I need to delete data in all my tables equipment_child1 to equipment_child8.

then I remenber I had used DELETE CASCADE in innoDB engine, but now I'm using MyISAM engina, is that a problem?

Any help, will really clarify ...

解决方案

Yes. Simply you can't with that engine.

edit. You could write a trigger that once you delete a record in your table delete all child records in all the other tables.

Ok. I wrote you an example:

 create table tab1 (
 id int )
 engine = myisam;

insert into tab1 values (1),(2),(3),(4); 

 create table tab2(
 id int not null auto_increment primary key,
 id_tab1 int
 ) engine = myisam;

 insert into tab2 (id_tab1) values (1),(2),(2),(3),(4);

 create table tab3(
 id int not null auto_increment primary key,
 id_tab1 int
 ) engine = myisam;

  insert into tab3 (id_tab1) values (1),(2),(2),(3),(2);


delimiter //
create trigger deletecascade after delete on tab1
for each row
begin
delete from tab2 where id_tab1 = old.id;
delete from tab3 where id_tab1 = old.id;
end; //
delimiter ;

delete from tab1 where id = 2;

Hope that it helps.

edit. Obviously it works even if you delete more id from table1 at the same time:

delete from tab1 where id in (2,3,4);

这篇关于如何在MySQL MyISAM存储引擎上使用删除级联?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！