问题描述
我有一组 InnoDB 表，我需要通过删除一些行并插入其他行来定期维护这些表.一些表具有引用其他表的外键约束，因此这意味着表加载顺序很重要.要插入新行而不用担心表的顺序，我使用:
I have a set of InnoDB tables that I periodically need to maintain by removing some rows and inserting others. Several of the tables have foreign key constraints referencing other tables, so this means that the table loading order is important. To insert the new rows without worrying about the order of the tables, I use:
SET FOREIGN_KEY_CHECKS=0;
之前，然后:
SET FOREIGN_KEY_CHECKS=1;
之后.
加载完成后，我想检查更新表中的数据是否仍然保持参照完整性--新行不会破坏外键约束--但似乎没有办法这个.
When the loading is complete, I'd like to check that the data in the updated tables still hold referential integrity--that the new rows don't break foreign key constraints--but it seems that there's no way to do this.
作为测试，我输入了我确定违反外键约束的数据，并且在重新启用外键检查后，mysql 没有产生任何警告或错误.
As a test, I entered data that I was sure violated foreign key constraints, and upon re-enabling the foreign key checks, mysql produced no warnings or errors.
如果我试图找到一种方法来指定表加载顺序，并在加载过程中保留外键检查，这将不允许我在具有自引用外键约束的表中加载数据，所以这不是一个可以接受的解决方案.
If I tried to find a way to specify the table loading order, and left the foreign key checks on during the loading process, this would not allow me to load data in a table that has a self-referencing foreign key constraint, so this would not be an acceptable solution.
有没有办法强制 InnoDB 验证表或数据库的外键约束?
Is there any way to force InnoDB to verify a table's or a database's foreign key constraints?
推荐答案
DELIMITER $$
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$
CREATE
PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64),
temporary_result_table ENUM('Y', 'N'))
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);
DECLARE done INT DEFAULT 0;
DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
END IF;
OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;
SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = COLUMN_NAME_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = CONCAT('SELECT ',
'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
'REFERRING.* ',
@from_part, ';');
END IF;
DEALLOCATE PREPARE stmt;
END LOOP foreign_key_cursor_loop;
END$$
DELIMITER ;
CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;
SELECT * FROM INVALID_FOREIGN_KEYS;
您可以使用此存储过程检查所有数据库中的无效外键.结果将加载到 INVALID_FOREIGN_KEYS 表中.ANALYZE_INVALID_FOREIGN_KEYS的参数:
You can use this stored procedure to check the all database for invalid foreign keys.
The result will be loaded into INVALID_FOREIGN_KEYS table.
Parameters of ANALYZE_INVALID_FOREIGN_KEYS:
- 数据库名称模式(LIKE 样式)
- 表名模式(LIKE 风格)
结果是否是暂时的.可以是:
'Y'、'N'、NULL.
- 如果是
'Y'，ANALYZE_INVALID_FOREIGN_KEYS结果表将是临时表.临时表对其他会话不可见.您可以与临时结果表并行执行多个ANALYZE_INVALID_FOREIGN_KEYS(...)存储过程. - 但是如果您对其他会话的部分结果感兴趣，那么您必须使用
'N'，然后从其他会话执行SELECT * FROM INVALID_FOREIGN_KEYS;. 你必须使用
NULL来跳过事务中的结果表创建，因为 MySQL 在事务中为CREATE TABLE ...和DROP 执行隐式提交TABLE ...，所以创建结果表会导致事务出现问题.在这种情况下，您必须自己从BEGIN; 中创建结果表.COMMIT/ROLLBACK;块:
- In case of
'Y'theANALYZE_INVALID_FOREIGN_KEYSresult table will be temporary table. The temporary table won't be visible for other sessions. You can execute multipleANALYZE_INVALID_FOREIGN_KEYS(...)stored procedure parallelly with temporary result table. - But if you are interested in the partial result from an other session, then you must use
'N', then executeSELECT * FROM INVALID_FOREIGN_KEYS;from an other session. You must use
NULLto skip result table creation in transaction, because MySQL executes implicit commit in transaction forCREATE TABLE ...andDROP TABLE ..., so the creation of result table would cause problem in transaction. In this case you must create the result table yourself out ofBEGIN; COMMIT/ROLLBACK;block:
CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
访问 MySQL 站点关于隐式提交:http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html
Visit MySQL site about implicit commit: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html
INVALID_FOREIGN_KEYS 行将仅包含无效数据库、表、列的名称.但是如果有的话，你可以通过执行INVALID_FOREIGN_KEYS的INVALID_KEY_SQL列的值看到无效的引用行.
The INVALID_FOREIGN_KEYS rows will contain only the name of invalid database, table, column. But you can see the invalid referring rows with the execution of value of INVALID_KEY_SQL column of INVALID_FOREIGN_KEYS if there is any.
如果在引用列(也称为外部索引)和被引用列(通常是主键)上有索引，这个存储过程将会非常快.
This stored procedure will be very fast if there are indexes on the referring columns (aka. foreign index) and on the referred columns (usually primary key).
这篇关于强制 InnoDB 重新检查表/表上的外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！