MySQL 官方文档: Constraints on Invalid Data
默认情况下, MySQL 可以容忍无效或不正确的数据值, 并将它们强制为有效的数据输入值. 好在, 你可以为不良值选择更传统的处理方法, 从而使得服务器能够拒绝并放弃出现不良值的语句. 请参见第 5.1.11 节 "服务器 SQL 模式".
本节介绍 MySQL 的默认 (宽容) 行为, 以及严格的 SQL 模式及其区别.
如果您没有使用严格模式, 那么无论何时向列中插入 "不正确" 值, 例如 NULL 将 NOT NULL 列插入到列中或将过大的数值插入数字列, MySQL 都会将列设置为 "最佳可能值" 而不是产生错误: 以下规则更详细地描述了它的工作原理:
如果您尝试将超出范围的值存储到数字列中, 则 MySQL Server 会存储零, 最小可能值或最大可能值, 以最接近无效值为准.
对于字符串, MySQL 存储空字符串或尽可能多的存储在列中的字符串.
如果您尝试将不以数字开头的字符串存储到数字列中, MySQL Server 将存储 0.
如第 1.8.3.4 节 "ENUM 和 SET 约束" 中所述, 处理 ENUM https://dev.mysql.com/doc/refman/8.0/en/enum.html 和 SET https://dev.mysql.com/doc/refman/8.0/en/set.html 列的 值无效.
MySQL 的允许你存储某些不正确的日期值插入 DATE 和 DATETIME 列(如 '2000-02-31'或 '2000-02-00'). 在这种情况下, 当应用程序未启用严格的 SQL 模式时, 应用程序将在存储它们之前验证日期. 如果 MySQL 可以存储日期值并检索完全相同的值, MySQL 会将其存储为给定值. 如果日期完全错误(服务器存储它的能力之外), 则特殊的 "零" 日期值 '0000-00-00'将存储在列中.
如果尝试存储 NULL 到不带 NULL 值的列, 则单行 INSERT 语句会发生错误 . 对于多行 INSERT 语句或
INSERT INTO ... SELECT
语句, MySQL Server 存储列数据类型的隐式默认值. 通常, 这适用 0 于数字类型, 字符串类型为空字符串(''), 日期和时间类型为" 零 "值. 第 11.7 节" 数据类型默认值 " 中讨论了隐式默认值 .
如果 INSERT 语句没有为列指定值, 则在列定义包含 explicit 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. 要想在第 1 场合防止问题的出现, 不要使用 MySQL 来检查列的内容. 最安全的方式 (通常也较快) 是, 让应用程序负责, 仅将有效值传递给数据库.
有了严格的模式选项后, 可使用 INSERT IGNORE 或 UPDATE IGNORE 而不是不带 IGNORE 的 INSERT 或 UPDATE, 将错误当作告警对待.
来源: http://www.mzh.ren/mysql-constraint-invalid-data.html