MySQL Variables foreign_key_checks 数据库 参数变量解释及正确配置使用

本站中文解释

刚开始使用数据库技术时，在MySQL中设置参数变量及其用途可能有点困难。但是随着对MySQL参数变量的深入了解，设置参数变量及其用途变得非常容易。其中一个重要的参数变量是foreign_key_checks，它的作用是确定MySQL是否允许表之间的外键关系。

通常来说，foreign_key_checks的默认值都是ON，这意味着MySQL会检查表之间的外键关系，以确保它们的完整性和一致性。当某一表中某列的值更改时，另一表中使用该值的其他行也会发生变化。这样可以避免两个表保持不一致。

若要关闭foreign_key_checks，可以使用以下SQL指令：SET foreign_key_checks = 0;这样，MySQL就不会检查表之间的外键关系，并且可以自由地更改某一表的任意值。但是，这会导致数据库完整性退化，因此应慎重使用该指令。

官方英文解释

foreign_key_checks

If set to 1 (the default), foreign key constraints are
checked. If set to 0, foreign key constraints are ignored,
with a couple of exceptions. When re-creating a table that was
dropped, an error is returned if the table definition does not
conform to the foreign key constraints referencing the table.
Likewise, an ALTER TABLE
operation returns an error if a foreign key definition is
incorrectly formed. For more information, see
Section 13.1.18.5, “FOREIGN KEY Constraints”.

Setting this variable has the same effect on
NDB tables as it does for
InnoDB tables. Typically you leave this
setting enabled during normal operation, to enforce
referential
integrity. Disabling foreign key checking can be useful
for reloading InnoDB tables in an order
different from that required by their parent/child
relationships. See
Section 13.1.18.5, “FOREIGN KEY Constraints”.

Setting foreign_key_checks to 0 also
affects data definition statements:
DROP
SCHEMA drops a schema even if it contains tables
that have foreign keys that are referred to by tables outside
the schema, and DROP TABLE
drops tables that have foreign keys that are referred to by
other tables.