怎么使用MySQL Strict SQL MODE

这篇文章主要介绍“怎么使用MySQL Strict SQL MODE”，在日常操作中，相信很多人在怎么使用MySQL Strict SQL MODE问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么使用MySQL Strict SQL MODE”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

严格SQL模式控制MySQL如何处理数据改变语句(insert或update)中的无效或缺失值。一个值可能由于各种原因而无效。例如，它对于列来说有错误的数据类型，或者超过了列的范围。当新记录被插入而对于非NULL且没有显式在定义时指定DEFAULT子句的列没有包含值就会出现缺失值的情况。(对于一个NULL列，如果缺失值就会插入NULL值)严格SQL模式也会影响DDL语句比如create table。

如果严格SQL模式没有生效，MySQL对于无效或者缺失值会插入调整值并生成一个警告。在严格SQL模式中，可以通过使用insert ignore或udpate ignore来产生这种行为。

对于select这样不改变数据的语句，在严格SQL模式中无效值会生成一个警告而不是错误。

严格SQL模式对于试图创建一个键值而超过列的最大键值长度时会产生一个错误。当严格SQL模式没有启用时，会产生一个警告并且截断键值的长度使其满足最大键值长度。

严格SQL模式不影响是否对外键约束执行检查。foreign_key_checks可以被使用。

如果STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用严格SQL模式就会生效，但这些模式的影响会有不同:
.对于事务表来说，当STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用后当在数据出现无效或丢失值就会出现错误。语句就会被终止与回滚。

.对于非事务表，如果在插入或更新语句中第一行记录出现坏值这些模式的行为是一样的:语句被终止并且表仍然保持不变。如果语句插入或修改多行记录并且在第二行或之后的行记录中出现坏值，那么结果依赖于严格SQL模式是否被启用。
-对于STRICT_ALL_TABLES，MySQL会返回一个错误并忽略剩余的行记录。然而，因为早些的行记录已经被插入或被更新，会导致部分更新。为了避免这个问题，使用单行语句，就会终止而不会改变表数据。

-对于STRICT_TRANS_TABLES，MySQL会将一个无效的值转换成一个最接近的有效值并插入这个调整值。如果这个值将丢失，MySQL插入这个隐式缺省值。在这种情况下，MySQL生成一个敬告而不是一个错误并继续处理语句。

严格SQL模式对除零，零日期和日期中出现零的处理如下:
.严格SQL模式影响对除零的处理，它包括MOD(N,0):对于数据改变操作(insert,update):
-如果严格SQL模式没有被启用，除零会插入NULL并生成一个警告。
-如果严格SQL模式被启用，除非指定了ignore否则除零操作会生成一个错误。对于insert ignore和update ignore操作，除零操作会插入NULL并生成一个警告。

对于select，除零操作会返回NULL。启用严格SQL模式会导致一个警告。
.严格SQL模式会影响服务器是否允许'0000-00-00'为一个有效日期:
-如果严格SQL模式没有被启用，'0000-00-00'被允许并且插入操作不会产生警告。
-如果严格SQL模式被启用,'0000-00-00'不被允许并且插入操作会产生错误，除非你指定ignore。对于insert ignore或update ignore，'0000-00-00'被允许并且插入操作会产生警告。

.严格SQL模式影响服务器是否允许在日期中的年部分为非零但月和日部分允许为零(比如'2010-00-01'或'2010-01-00'):
-如果严格SQL模式没有被启用，有零的日期被允许并且插入操作不产生警告。
-如果严格SQL模式被启用,有零的日期不被允许并且插入操作产生错误，除非指定ignore。对于insert ignore或update ignore来说，有零的日期将以'0000-00-00'形式被插入并产生一个警告。

IGNORE关键字与严格SQL模式的对比
这里将介绍在语句执行时IGNORE关键字(它降级错误为警告)和严格SQL模式(它升级警告为错误)的对比。描述它们影响那些语句以及应用它们有那些错误。

IGNORE对语句执行的影响
MySQL中的一些语句支持可选的IGNORE关键字。此关键字将导致服务器降级某些类型的错误并生成警告。对于多行语句，IGNORE会导致语句跳到下一行，而不是中止。

例如，如果表t2有一个主键列i,试图在多行记录中插入相同的i值正常来说会产生一个重复键错误:

mysql> insert into t2(i) values(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

当使用IGNORE关键字时，包含重复键值的记录仍然不会被插入，但会使用警告来代替错误:

mysql> insert ignore into t2(i) values(1),(1);
Query OK, 1 row affected, 1 warning (0.15 sec)
Records: 2  Duplicates: 1  Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
2 rows in set (0.00 sec)

以下语句支持IGNORE关键字:
.create table... select:ignore不能应用到语句的create table或select部分但对于由select语句所提供记录来执行插入语句可以应用。对于唯一键值重复的记录会被丢弃。

.delete:ignore会导致MySQL在处理删除记录时忽略错误。

.insert:使用ignore，对于唯一键值重复的记录会被丢弃。对于重复键值的行记录会导致数据转换为最接近的有效值被插入。

对于分区表当没有匹配指定值的分区被找到时，ignore会导致包含那些不匹配值的记录的插入操作失败。
.load data,load xml:使用ignore，对于唯一键值重复的记录会被丢弃。

.update:使用ignore，对于在唯一键值出现重复键值冲突的记录不会被更新。被更新的记录可能导致数据转换为最接近的有效值被插入。

ignore关键字应用到以下错误:
ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED

严格SQL模式对语句执行的影响
MySQL服务器可以以不同的SQL模式进行操作并且可以应用这些不同模式到不同的客房端，这依赖于sql_mode系统变量。在严格SQL模式中，服务器会将特定的警告升级成错误。

例如，在非严格SQL模式中，向整数类型列插入字符串'abc'的结果是将这个字符串值转换为0并生成一个警告:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t2(i) values('abc');
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

在严格SQL模式下，无效值会被拒绝并生成错误:

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t2(i) values('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

在某些条件下，某些值可能超出范围或将无效行插入或从表中删除，严格SQL模式适用于以下语句:
alter table
create table
create table ... select
delete(单表和多表)
insert
load data
load xml
select sleep()
update(单表和多表)
在存储程序中，如果程序是在严格模式生效时定义的，则刚才列出的类型的各个语句将以严格SQL模式执行。

严格SQL模式应用于以下错误，代表输入值可能无效或丢失这类错误。如果对于列值使用了错误数据类型或超过了值的
范围那么值就是无效的。如果被插入的新行不包含NOT NULL列值但除了在列定义时显式指定了DEFAULT子句的那么就
是值丢失。
ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED

到此，关于“怎么使用MySQL Strict SQL MODE”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！