• <tfoot id='rlD0j'></tfoot>

        <small id='rlD0j'></small><noframes id='rlD0j'>

      1. <legend id='rlD0j'><style id='rlD0j'><dir id='rlD0j'><q id='rlD0j'></q></dir></style></legend>
        <i id='rlD0j'><tr id='rlD0j'><dt id='rlD0j'><q id='rlD0j'><span id='rlD0j'><b id='rlD0j'><form id='rlD0j'><ins id='rlD0j'></ins><ul id='rlD0j'></ul><sub id='rlD0j'></sub></form><legend id='rlD0j'></legend><bdo id='rlD0j'><pre id='rlD0j'><center id='rlD0j'></center></pre></bdo></b><th id='rlD0j'></th></span></q></dt></tr></i><div id='rlD0j'><tfoot id='rlD0j'></tfoot><dl id='rlD0j'><fieldset id='rlD0j'></fieldset></dl></div>
          <bdo id='rlD0j'></bdo><ul id='rlD0j'></ul>

        强制 InnoDB 重新检查表/表上的外键?

        时间:2023-06-03
        <legend id='IYzBs'><style id='IYzBs'><dir id='IYzBs'><q id='IYzBs'></q></dir></style></legend>
      2. <small id='IYzBs'></small><noframes id='IYzBs'>

      3. <i id='IYzBs'><tr id='IYzBs'><dt id='IYzBs'><q id='IYzBs'><span id='IYzBs'><b id='IYzBs'><form id='IYzBs'><ins id='IYzBs'></ins><ul id='IYzBs'></ul><sub id='IYzBs'></sub></form><legend id='IYzBs'></legend><bdo id='IYzBs'><pre id='IYzBs'><center id='IYzBs'></center></pre></bdo></b><th id='IYzBs'></th></span></q></dt></tr></i><div id='IYzBs'><tfoot id='IYzBs'></tfoot><dl id='IYzBs'><fieldset id='IYzBs'></fieldset></dl></div>

          <bdo id='IYzBs'></bdo><ul id='IYzBs'></ul>
          <tfoot id='IYzBs'></tfoot>

                <tbody id='IYzBs'></tbody>
                  本文介绍了强制 InnoDB 重新检查表/表上的外键?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我有一组 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:

                  1. 数据库名称模式(LIKE 样式)
                  2. 表名模式(LIKE 风格)
                  3. 结果是否是暂时的.可以是:'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' the ANALYZE_INVALID_FOREIGN_KEYS result table will be temporary table. The temporary table won't be visible for other sessions. You can execute multiple ANALYZE_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 execute SELECT * FROM INVALID_FOREIGN_KEYS; from an other session.
                  • You must use NULL to skip result table creation in transaction, because MySQL executes implicit commit in transaction for CREATE TABLE ... and DROP TABLE ..., so the creation of result table would cause problem in transaction. In this case you must create the result table yourself out of BEGIN; 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_KEYSINVALID_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 重新检查表/表上的外键?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:如何使用 Sequelize CLI 从 Sequelize 模型自动生成迁移? 下一篇:如何在codeigniter活动记录中插入查询后获取最后一个插入ID

                  相关文章

                  <tfoot id='PhM0n'></tfoot>

                    <i id='PhM0n'><tr id='PhM0n'><dt id='PhM0n'><q id='PhM0n'><span id='PhM0n'><b id='PhM0n'><form id='PhM0n'><ins id='PhM0n'></ins><ul id='PhM0n'></ul><sub id='PhM0n'></sub></form><legend id='PhM0n'></legend><bdo id='PhM0n'><pre id='PhM0n'><center id='PhM0n'></center></pre></bdo></b><th id='PhM0n'></th></span></q></dt></tr></i><div id='PhM0n'><tfoot id='PhM0n'></tfoot><dl id='PhM0n'><fieldset id='PhM0n'></fieldset></dl></div>

                      <small id='PhM0n'></small><noframes id='PhM0n'>

                      • <bdo id='PhM0n'></bdo><ul id='PhM0n'></ul>

                    1. <legend id='PhM0n'><style id='PhM0n'><dir id='PhM0n'><q id='PhM0n'></q></dir></style></legend>