主页 / dba / 问题 / 254338
Accepted
Glasnhost
Asked: 2019-11-29 02:49:38 +0800 CST

我需要使用外键将数据库迁移到新数据库。如何检查约束错误？

  • 772

我需要将很多 mysql MyISAM 表迁移到一个新的 InnoDb 版本，添加外键约束。我可以转储和导入，禁用第一个外键检查。

但是，当我再次激活外键检查时，有没有办法检查哪些表存在约束问题（无需手动检查所有表）？

mysql

3 个回答

  1. Best Answer

    我不知道有任何备份解决方案在外键方面尊重表的顺序，并且在导入数据时不会简单地禁用外键检查。因此，您必须自己检查数据不一致。例如，您可以通过以下过程执行此操作：

    DROP PROCEDURE IF EXISTS sp_checkFKIntegrity;
DELIMITER $$
CREATE PROCEDURE sp_checkFKIntegrity(
    IN p_schemaname varchar(255),
    IN p_tablename varchar(255)
)
BEGIN
    DECLARE v_table_schema varchar(255);
    DECLARE v_table_name varchar(255);
    DECLARE v_referenced_table_schema varchar(255);
    DECLARE v_referenced_table_name varchar(255);
    DECLARE v_column_name varchar(255);
    DECLARE v_referenced_column_name varchar(255);
    DECLARE v_constraint_name varchar(255);

    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT
        table_schema,
        table_name,
        referenced_table_schema,
        referenced_table_name,
        column_name,
        referenced_column_name,
        constraint_name
        FROM
        information_schema.key_column_usage k
        WHERE k.table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'common')
        AND referenced_table_name IS NOT NULL
        AND table_schema = COALESCE(p_schemaname, table_schema)
        AND table_name = COALESCE(p_tablename, table_name);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SELECT
    COUNT(*) INTO @countFKs
    FROM
    information_schema.key_column_usage k
    WHERE k.table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'common')
    AND referenced_table_name IS NOT NULL
    AND table_schema = COALESCE(p_schemaname, table_schema)
    AND table_name = COALESCE(p_tablename, table_name);

    SET @counter := 1;
    OPEN cur;
    cur_loop: LOOP
        FETCH cur INTO v_table_schema, v_table_name, v_referenced_table_schema, v_referenced_table_name, v_column_name, v_referenced_column_name, v_constraint_name;
        IF done THEN
            LEAVE cur_loop;
        END IF;
        SET @query := CONCAT('SELECT EXISTS(
            SELECT 1 FROM ', v_table_schema, '.', v_table_name, ' k 
            LEFT JOIN ', v_referenced_table_schema, '.', v_referenced_table_name, ' e ON k.', v_column_name, ' = e.', v_referenced_column_name, 
            ' WHERE e.', v_referenced_column_name, ' IS NULL) INTO @consistency;');

        SELECT CONCAT('Checking FK constraint ', @counter, ' of ', @countFKs, '...') AS info;
        SET @counter := @counter + 1;

        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @consistency = 1 THEN
            SELECT CONCAT('Inconsistency found in foreign key "', v_table_schema, '.', v_table_name, '(', v_column_name, ') -> ', v_referenced_table_schema, '.', v_referenced_table_name, '(', v_referenced_column_name, ')", constraint_name "', v_constraint_name, '"') AS info;
            SELECT CONCAT('SELECT COUNT(*) FROM ', v_table_schema, '.', v_table_name, ' k LEFT JOIN ', v_referenced_table_schema, '.', v_referenced_table_name, ' e ON k.', v_column_name, ' = e.', v_referenced_column_name, 
            ' WHERE e.', v_referenced_column_name, ' IS NULL;') AS "Statement for further investigation";
        END IF;
    END LOOP;

    CLOSE cur;
    SELECT 'completed OK' AS info;
END $$
DELIMITER ;

    模式和表名参数是可选的，因此您可以像这样执行该过程：

    CALL sp_checkFKIntegrity('playground', null); -- checks all FK constraints in the playground schema
CALL sp_checkFKIntegrity('playground', 'foo'); -- checks the FK constraints the table foo in the playground schema has to other (parent) tables
CALL sp_checkFKIntegrity(null, null); -- checks all FKs except the schemas mentioned in the procedure
CALL sp_checkFKIntegrity(null, 'foo'); -- checks the FK constraints the table foo has to other tables, no matter in which schema it is

    此过程尚不支持的是具有多列的外键。我对此太懒了，尽管可能我还没有在野外看到过。

    • 1

  2. 这正是测试数据库的用途。

    创建一个并尝试将数据加载到其中。

    如果它加载，很好。如果没有，那么 MySQL 会告诉你为什么它不能这样做。

    • 0

  3. 可能有点乱：

    计划A：

    1. mysqldump --no-data > schema.txt
    2. mysqldump --no-create-table > data.txt
    3. mysql < 架构.txt
    4. 更改表 ... 引擎 = InnoDB；-- 对于每个表
    5. mysql < data.txt -- 将在插入时检查 FK 约束

    但是...如果表格以“错误”的顺序加载，第 5 步会出现很多故障。所以...

    1. 一次转储一张桌子
    2. 按顺序加载表，以便父表在子表之前加载。

    B计划：

    在同一台服务器上：

    1. CREATE DATABASE try;
    2. （子表之前的父表）：对于每个表：
      • CREATE TABLE try.tbl LIKE real.tbl;
      • ALTER TABLE try.tbl ENGINE=InnoDB;
      • INSERT INTO try.tbl SELECT * FROM real.tbl;

    计划C：

    在您现有的 MyISAM 数据库上，设计并运行可以发现任何 FK 错误的 SELECT。这些可能涉及LEFT JOIN ... IS NULL（寻找丢失的行。）

    建议使用小的LIMIT和/或用SELECT COUNT(*).

    • 0

相关问题