1.7.3.3 对无效数据的约束

MySQL 5.7.5 及更高版本默认使用严格的 SQL 模式，它会处理无效值，以便服务器拒绝它们并中止它们出现的语句（请参阅 第 5.1.10 节，“服务器 SQL 模式”）。以前，MySQL 对数据输入中使用的错误值更加宽容；现在这需要禁用严格模式，不推荐这样做。本节的其余部分讨论在禁用严格模式时 MySQL 遵循的旧行为。

如果你没有使用严格模式，那么每当你将一个 “不正确”的值插入到一个列中，比如一个 NULL插入到一个NOT NULL 列中或者一个太大的数值插入到一个数字列中，MySQL 都会将该列设置为“最佳可能值”而不是产生错误：以下规则更详细地描述了它是如何工作的：

  • 如果您尝试将超出范围的值存储到数字列中，MySQL 服务器将存储零、最小可能值或最大可能值，以最接近无效值的为准。

  • 对于字符串，MySQL 存储空字符串或尽可能多的字符串可以存储在列中。

  • 如果您尝试将不以数字开头的字符串存储到数字列中，MySQL Server 将存储 0。

  • ENUM和 列的 无效值按照第 1.7.3.4 节“ENUM 和 SET 约束”SET中的描述进行处理。

  • MySQL 允许您将某些不正确的日期值存储到DATE和 DATETIME列中（例如 '2000-02-31'或 '2000-02-00'）。在这种情况下，当应用程序未启用严格 SQL 模式时，应用程序将在存储日期之前验证日期。如果 MySQL 可以存储日期值并检索完全相同的值，MySQL 将按给定的方式存储它。如果日期完全错误（服务器无法存储它），则特殊的“零”日期值 '0000-00-00'将存储在列中。

  • 如果您尝试存储NULL到不带NULL值的列中，则单行 INSERT语句会出错。对于多行INSERT 语句或 for INSERT INTO ... SELECT语句，MySQL 服务器存储列数据类型的隐式默认值。通常，这是0用于数字类型，空字符串 ( '') 用于字符串类型，“零”值用于日期和时间类型。隐式默认值在 第 11.6 节“数据类型默认值”中讨论。

  • 如果INSERT语句没有为列指定值，如果列定义包含显式 DEFAULT子句，MySQL 将插入其默认值。如果定义没有这样的DEFAULT子句，MySQL 将插入列数据类型的隐式默认值。

在严格模式未生效时使用上述规则的原因是在语句开始执行之前我们无法检查这些条件。我们不能在更新几行后遇到问题就回滚，因为存储引擎可能不支持回滚。终止语句的选项不是很好；在这种情况下，更新将“完成一半”，这可能是最糟糕的情况。在这种情况下，最好 “尽力而为”，然后若无其事地继续。

您可以使用 STRICT_TRANS_TABLES或 STRICT_ALL_TABLESSQL 模式选择对输入值进行更严格的处理： 

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';

STRICT_TRANS_TABLES为事务存储引擎启用严格模式，也在一定程度上为非事务引擎启用严格模式。它是这样工作的：

  • 对于事务存储引擎，语句中任何地方出现的错误数据值都会导致语句中止并回滚。

  • 对于非事务性存储引擎，如果错误发生在要插入或更新的第一行中，语句将中止。（当错误发生在第一行时，可以中止语句以保持表不变，就像事务表一样。）第一行之后的行中的错误不会中止语句，因为表已被更改第一排。相反，错误的数据值会被调整并导致警告而不是错误。换句话说，与 STRICT_TRANS_TABLES，错误的值会导致 MySQL 回滚到目前为止所做的所有更新，如果可以在不更改表的情况下完成。但是一旦表格被更改，进一步的错误会导致调整和警告。

要进行更严格的检查，请启用 STRICT_ALL_TABLES. 这与 STRICT_TRANS_TABLES非事务性存储引擎相同，错误会中止语句，即使是第一行之后的行中的错误数据也是如此。这意味着如果在非事务性表的多行插入或更新中途发生错误，则会产生部分更新。较早的行被插入或更新，但从错误点开始的那些行则没有。为避免非事务表出现这种情况，请使用单行语句或使用 STRICT_TRANS_TABLES如果转换警告而不是错误是可以接受的。为首先避免出现问题，请勿使用 MySQL 检查列内容。让应用程序确保它只将有效值传递给数据库是最安全的（而且通常更快）。

INSERT IGNORE使用任何一个严格模式选项，您都可以通过使用orUPDATE IGNORE而不是INSERTor UPDATEwithout 将错误视为警告 IGNORE。