MYSQL怎么用命令删除某个表的全部关联外键约束？

[ljm10]

如题

[ljm10]

alter table test1 drop foreign key FK_Reference_14

[wwwwb]

直接打开information_schema.REFERENTIAL_CONSTRAINTS
REFERENCED_TABLE_NAME： 主表
TABLE_NAME：子表
CONSTRAINT_NAME：名字

[ljm10]

14楼是什么意思啊？打开也不能删除啊？

[ACMAIN_CHM]

alter table test1 drop foreign key FK_Reference_14

这句直接执行是否有问题？

[ljm10]

没有，估计是MYSQL的BUG

[ACMAIN_CHM]

改为如下再试一下。应该是你执行的速度过快了。

DELIMITER $$

DROP PROCEDURE IF EXISTS `Proc_app_DelRef`$$

CREATE DEFINER=`root`@`%` PROCEDURE `Proc_app_DelRef`(
    IN p_tablename VARCHAR(50) -- 表名
    )
BEGIN      
    DECLARE done INT DEFAULT 0;
    DECLARE v_constraintname VARCHAR(100);
        DECLARE cur CURSOR FOR 
    SELECT constraint_name FROM information_schema.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_schema=DATABASE() AND table_name=p_tablename;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur;
    REPEAT         
            FETCH cur INTO v_constraintname; 
            IF v_constraintname IS NOT NULL THEN
                SET @sqlcmd = CONCAT('alter table ',p_tablename,' drop foreign key ', v_constraintname);
                PREPARE stmt FROM @sqlcmd;
                EXECUTE stmt;  
                DEALLOCATE PREPARE stmt;   
                SLEEP(10000) ;		---- changed by ACMAIN
            END IF;            
            
    UNTIL done END REPEAT;
    CLOSE cur;
    END$$

DELIMITER ;

[ljm10]

现在用游标已经很慢了，还阻塞10秒.
还是试试吧，谢谢楼上

[ljm10]

还是不行

[ljm10]

直接在sleep(10000)报错

[ACMAIN_CHM]

哦，写错了，应该是
select SLEEP(10000) ;

[ljm10]

执行后数据库死掉了

[ACMAIN_CHM]

也不会死掉啊。
你的这些个表有多大？ drop foreign key 所花的时间是多少？

[ljm10]

数据库还在死啊，就删除一个关联而已，就drop foreign key最多0.02秒

[ACMAIN_CHM]

改了一下你代码，测试了没有问题。

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `Proc_app_DelRef`$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE  PROCEDURE `Proc_app_DelRef`(
    ->     IN p_tablename VARCHAR(50) -- 表名
    ->     )
    -> BEGIN
    ->     DECLARE done INT DEFAULT 0;
    ->     DECLARE v_constraintname VARCHAR(100);
    ->         DECLARE cur CURSOR FOR
    ->     SELECT constraint_name FROM information_schema.REFERENTIAL_CONSTRAINT
S
    ->     WHERE CONSTRAINT_schema=DATABASE() AND table_name=p_tablename;
    ->     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->     OPEN cur;
    ->     REPEAT
    ->             FETCH cur INTO v_constraintname;
    ->             IF NOT done THEN
    ->                 SET @sqlcmd = CONCAT('alter table ',p_tablename,' drop fo
reign key ', v_constraintname);
    ->                 select @sqlcmd;
    ->                 PREPARE stmt FROM @sqlcmd;
    ->                 EXECUTE stmt;
    ->                 DEALLOCATE PREPARE stmt;
    ->             END IF;
    ->
    ->     UNTIL done END REPEAT;
    ->     CLOSE cur;
    ->     END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> call Proc_app_DelRef('tx');
Query OK, 0 rows affected (0.02 sec)

mysql> drop table tx;
Query OK, 0 rows affected (0.02 sec)

mysql> create table tx (
    ->  id int primary key,
    ->  c1 int,
    ->  c2 int,
    ->  c3 int,
    ->  CONSTRAINT c1 FOREIGN KEY f1 (c1) REFERENCES t1(id),
    ->  CONSTRAINT c2 FOREIGN KEY f2 (c2) REFERENCES t2(id),
    ->  CONSTRAINT c3 FOREIGN KEY f3 (c3) REFERENCES t3(id)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> call Proc_app_DelRef('tx');
+------------------------------------+
| @sqlcmd                            |
+------------------------------------+
| alter table tx drop foreign key c1 |
+------------------------------------+
1 row in set (0.00 sec)

+------------------------------------+
| @sqlcmd                            |
+------------------------------------+
| alter table tx drop foreign key c2 |
+------------------------------------+
1 row in set (0.12 sec)

+------------------------------------+
| @sqlcmd                            |
+------------------------------------+
| alter table tx drop foreign key c3 |
+------------------------------------+
1 row in set (0.51 sec)

Query OK, 0 rows affected (0.56 sec)

mysql>

[ljm10]

我的是mysql5.2，难道是版本的问题？听说这个版本有这个问题的BUG
http://cavonchen.javaeye.com/blog/626522

[ACMAIN_CHM]

我改了你的代码。注意修改的部分。

IF NOT done THEN

[ljm10]

可以了，谢谢啊

[ljm10]

有人能帮帮忙吗？

[小小小小周]

不知道是不是这样:
set foreign_key_checks=0;
backup 表数据;
drop table tbname;
create table tbname;
load data 数据;
这样可以一下子全部删除完;

要不就 alter table tbname drop foreign key foreign_key_name

[ljm10]