mysql - 删除mysql数据库中的所有外键

Question

我可以看到有一些问题[ 1 , 2 ] 已经要求这样做，但解决方案不包含完成此任务的完整 SQL 脚本。我有一种情况，只使用 SQL 删除所有外键会很有帮助。目前我正在尝试使用存储过程和游标来解决这个问题，如下所示:

-- No automatic commits:
DROP PROCEDURE IF EXISTS removeConstraints;
-- Magic to happen soon:
DELIMITER |
CREATE PROCEDURE removeConstraints()
BEGIN
  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;
  -- https://dev.mysql.com/doc/refman/5.0/en/cursors.html
  -- https://stackoverflow.com/questions/1745165/looping-over-result-sets-in-mysql
  -- https://mariadb.com/kb/en/mariadb/cursor-overview/
  DECLARE done INT DEFAULT FALSE;
  DECLARE s VARCHAR(1024) DEFAULT '';
  DECLARE cur CURSOR FOR SELECT CONCAT('ALTER TABLE ',TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
    FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
    WHERE REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO s;
    IF done THEN
      LEAVE read_loop;
    END IF;
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;

  SET FOREIGN_KEY_CHECKS=1;
  COMMIT;
  SET AUTOCOMMIT=1;
END |
DELIMITER ;
-- Do magic:
CALL removeConstraints();
-- Cleanup:
DROP PROCEDURE removeConstraints;

遗憾的是，这会产生以下错误消息:

ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE done INT DEFAULT FALSE;
  DECLARE s VARCHAR(1024) DEFAULT '';
  DECLARE ' at line 8

---

使用来自 Ravinder Reddy 的输入我现在已经将 BEGIN 之后的 DECLARE 部分更新为如下所示:

CREATE PROCEDURE removeConstraints()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE s VARCHAR(1024) DEFAULT '';
  DECLARE cur CURSOR FOR 
          SELECT DISTINCT CONCAT('ALTER TABLE ',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';')
          FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
          WHERE TABLE_SCHEMA=DATABASE()
          AND REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;

但是当我尝试执行该过程时，我仍然遇到错误:

  MariaDB [v4]> CALL removeConstraints();
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1

我还尝试使用不同的 SELECT 语句，例如:

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';')
FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
WHERE TABLE_SCHEMA=DATABASE()
AND CONSTRAINT_NAME != 'PRIMARY'
AND CONSTRAINT_NAME IS NOT NULL
AND TABLE_NAME IS NOT NULL;

...但它没有帮助。

---

我现在通过更改代码使其工作，以便 CONCAT 稍后发生。

DROP PROCEDURE IF EXISTS removeConstraints;
-- Magic to happen soon:
DELIMITER |
CREATE PROCEDURE removeConstraints()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tName VARCHAR(64);
  DECLARE cName VARCHAR(64);
  DECLARE cur CURSOR FOR 
          SELECT DISTINCT TABLE_NAME, CONSTRAINT_NAME
          FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
          WHERE TABLE_SCHEMA=DATABASE()
          AND REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO tName, cName;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('ALTER TABLE ',tName,' DROP FOREIGN KEY ',cName,';');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;

  SET FOREIGN_KEY_CHECKS=1;
  COMMIT;
  SET AUTOCOMMIT=1;
END |
DELIMITER ;
-- Do magic:
CALL removeConstraints();
-- Cleanup:
DROP PROCEDURE removeConstraints;

Answer 1

所有DECLARE 语句必须位于BEGIN - END block 的顶部。

所有其他语句都应遵循它们。

在您的代码中，您在 DECLARE 语句之前定义了 SET 语句。
将这些语句移动到 DECLARE 语句的下方。

示例:

  -- https://dev.mysql.com/doc/refman/5.0/en/cursors.html
  -- https://stackoverflow.com/questions/1745165/looping-over-result-sets-in-mysql
  -- https://mariadb.com/kb/en/mariadb/cursor-overview/
  DECLARE done INT DEFAULT FALSE;
  DECLARE s VARCHAR(1024) DEFAULT '';
  DECLARE cur CURSOR FOR 
          SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';')
            FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
           WHERE REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;

文档引用:

DECLARE Syntax

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations