mysql5.6升级到5.7后 linux下修改mysql的sql_mode模式

升级mysql数据库后产生的错误：
mysql从5.6升级到5.7后出现：插入数据和修改数据时出错。

修改方式在liunx服务器中找到my.cnf文件最后一行加入

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

---

修改mysql的sql_mode的原因
MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。DBA可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序可以将其会话SQL模式设置为其自己的要求。

模式会影响MySQL支持的SQL语法以及它执行的数据验证检查。这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务器一起使用变得更加容易。

sql_mode:定义MySQL应该支持的sql语法，对数据的校验等等

一、设置SQL模式

默认的SQL模式是 NO_ENGINE_SUBSTITUTION。

要在服务器启动时设置SQL模式，请 在命令行上或 在（Unix操作系统）或（Windows）等选项文件中使用该 选项 。 是逗号分隔的不同模式的列表。要显式清除SQL模式，请在命令行或选项文件中将其设置为空字符串 。 --sql-mode="modes"sql-mode="modes"my.cnfmy.inimodes--sql-mode=""sql-mode=""

注意
MySQL安装程序可以在安装过程中配置SQL模式。例如， mysql_install_db创建my.cnf在基本安装目录中命名的默认选项文件。该文件包含一行设置SQL模式; 请参见第4.4.3节“ mysql_install_db - 初始化MySQL数据目录”。

如果SQL模式与默认模式或您期望的模式不同，请检查服务器在启动时读取的选项文件中的设置。

要在运行时更改SQL模式，请sql_mode使用以下SET 语句设置全局或会话 系统变量：

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
设置GLOBAL变量需要该 SUPER权限，并影响从该时间开始连接的所有客户端的操作。设置SESSION变量仅影响当前客户端。每个客户端都可以随时更改其会话 sql_mode值。

要确定当前的全局或会话 sql_mode设置，请选择其值：

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

重要
SQL模式和用户定义的分区。 在创建数据并将数据插入分区表后更改服务器SQL模式可能会导致此类表的行为发生重大更改，并可能导致数据丢失或损坏。强烈建议您在创建使用用户定义分区的表后永远不要更改SQL模式。

复制分区表时，主服务器和从服务器上的不同SQL模式也会导致问题。为获得最佳结果，应始终在主服务器和从服务器上使用相同的服务器SQL模式。

有关更多信息，请参见 第19.6节“分区的限制和限制”。

二、最重要的SQL模式

最重要的sql_mode 价值可能是这些：

ANSI

此模式更改语法和行为以更符合标准SQL。它是 本节末尾列出的特殊 组合模式之一。

STRICT_TRANS_TABLES

如果无法将值插入事务表中，则中止该语句。对于非事务性表，如果值出现在单行语句或多行语句的第一行中，则中止该语句。更多细节将在本节后面给出。

TRADITIONAL

使MySQL的行为类似于“ 传统的 ” SQL数据库系统。在向列中插入不正确的值时，此模式的简单描述是 “ 给出错误而不是警告 ”。它是本节末尾列出的特殊组合模式之一。

注意
随着TRADITIONAL模式允许，INSERT或者 UPDATE只要发生错误中止。如果您使用的是非事务性存储引擎，则可能不是您想要的，因为在错误之前进行的数据更改可能无法回滚，从而导致“ 部分完成 ”更新。

当本手册涉及“ 严格模式 ”时，表示具有一个或两个STRICT_TRANS_TABLES或 STRICT_ALL_TABLES启用的模式 。

三、SQL模式的完整列表

以下列表描述了所有支持的SQL模式：

ALLOW_INVALID_DATES

不要对日期进行全面检查。仅检查月份是否在1到12的范围内，并且日期在1到31的范围内。这对于在三个不同字段中获取年，月和日并准确存储用户的Web应用程序非常有用。插入，没有日期验证。此模式适用于 DATE和 DATETIME列。它不应用TIMESTAMP列，它始终需要有效日期。

与ALLOW_INVALID_DATES 启用，服务器需要月份和日期值是合法的，而不是仅仅在范围为1〜12和1〜31，分别。禁用严格模式后，将生成无效日期，例如'2004-04-31'转换为 '0000-00-00'和警告。启用严格模式后，无效日期会生成错误。要允许此类日期，请启用 ALLOW_INVALID_DATES。

ANSI_QUOTES

治疗"作为标识符引号字符（如引号字符），而不是作为一个字符串引号字符。您仍然可以使用 此模式启用引用标识符。与ANSI_QUOTES 启用，则不能使用双引号，因为它们被解释为标识符引用文字字符串。

ERROR_FOR_DIVISION_BY_ZERO

该 ERROR_FOR_DIVISION_BY_ZERO 模式影响除零处理，包括 。对于数据更改操作（， ），其效果还取决于是否启用了严格的SQL模式。 MOD(N,0)INSERTUPDATE

如果未启用此模式，则除以零并不 NULL产生警告。

如果启用此模式，则除以零 NULL并生成警告。

如果启用此模式和严格模式，除非IGNORE 同时给出，除以零会产生错误。对于INSERT IGNORE 和UPDATE IGNORE，除以零插入NULL并产生警告。

因为SELECT，除以零回报NULL。启用 ERROR_FOR_DIVISION_BY_ZERO 原因的警告要制造为好，而不管是否启用了严格模式。

从MySQL 5.6.17开始， ERROR_FOR_DIVISION_BY_ZERO 不推荐使用并设置sql_mode 值以包含它会生成警告。

HIGH_NOT_PRECEDENCE

NOT 运算符 的优先级是这样的表达式，如NOT a BETWEEN b AND c解析为NOT (a BETWEEN b AND c)。在某些旧版本的MySQL中，表达式被解析为(NOT a) BETWEEN b AND c。通过启用HIGH_NOT_PRECEDENCESQL模式可以获得旧的高优先级行为 。

mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;

    -> 0

mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;

    -> 1

IGNORE_SPACE

允许函数名称和(字符之间的空格 。这会导致内置函数名称被视为保留字。因此，必须引用与函数名称相同的标识符，如第9.2节“架构对象名称”中所述。例如，因为有一个 COUNT()函数，count在以下语句中使用 作为表名会导致错误：

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax
应引用表名：

mysql> CREATE TABLE count (i INT);
Query OK, 0 rows affected (0.00 sec)
在IGNORE_SPACESQL模式适用于内置函数，而不是用户定义的函数或存储功能。无论是否IGNORE_SPACE启用，始终允许在UDF或存储的函数名后面有空格 。

有关进一步的讨论 IGNORE_SPACE，请参见 第9.2.4节“函数名称解析和解决方案”。

NO_AUTO_CREATE_USER

GRANT除非指定了身份验证信息，否则 防止语句自动创建新用户。该语句必须使用IDENTIFIED BY或使用身份验证插件指定非空密码 IDENTIFIED WITH。

NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO 影响AUTO_INCREMENT 列的处理。通常，通过插入NULL或 插入列来生成列的下一个序列号0。 NO_AUTO_VALUE_ON_ZERO 抑制此行为，0以便仅NULL生成下一个序列号。

如果0已存储在表的AUTO_INCREMENT 列中，则此模式非常有用。（0顺便说一下，存储不是推荐的做法。）例如，如果使用mysqldump转储表然后重新加载它，MySQL通常会在遇到0值时生成新的序列号，从而生成一个内容与表中不同的表。那被倾倒了。NO_AUTO_VALUE_ON_ZERO 在重新加载转储文件之前启用 可以解决此问题。出于这个原因，mysqldump在其输出中自动包含一个启用的语句 NO_AUTO_VALUE_ON_ZERO。

NO_BACKSLASH_ESCAPES

禁止使用反斜杠字符（）作为字符串中的转义字符。启用此模式后，反斜杠就像其他任何一个普通字符一样。

NO_DIR_IN_CREATE

创建表时，请忽略all INDEX DIRECTORY和DATA DIRECTORY 指令。此选项在从属复制服务器上很有用。

NO_ENGINE_SUBSTITUTION

在诸如CREATE TABLE或ALTER TABLE指定禁用或未编译的存储引擎之类的语句时控制默认存储引擎的自动替换。

由于存储引擎可以在运行时插入，因此不可用的引擎也会以相同的方式处理：

随着 NO_ENGINE_SUBSTITUTION 禁止，CREATE TABLE 默认引擎使用，如果需要的引擎不可用时发出警告。因为 ALTER TABLE，会发出警告并且不会更改表格。

与 NO_ENGINE_SUBSTITUTION 启用，则会出现错误，并且不会创建或修改的表，如果所需的引擎不可用。

NO_FIELD_OPTIONS

不要在输出中打印特定于MySQL的列选项 SHOW CREATE TABLE。mysqldump在可移植模式下使用此模式。

NO_KEY_OPTIONS

不要在输出中打印特定于MySQL的索引选项 SHOW CREATE TABLE。mysqldump在可移植模式下使用此模式。

NO_TABLE_OPTIONS

不要ENGINE在输出中打印特定于MySQL的表选项（例如 ） SHOW CREATE TABLE。mysqldump在可移植模式下使用此模式。

NO_UNSIGNED_SUBTRACTION

UNSIGNED默认情况下， 整数值之间的减法（其中一个是类型 ）会产生无符号结果。如果结果否则为负，则会导致错误：

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
如果NO_UNSIGNED_SUBTRACTION 启用了 SQL模式，则结果为负：

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
CAST(0 AS UNSIGNED) - 1
-1

如果使用此类操作的结果更新 UNSIGNED整数列，则结果将剪切为列类型的最大值，或者如果NO_UNSIGNED_SUBTRACTION启用则剪切为0 。启用严格的SQL模式后，会发生错误并且列保持不变。

当 NO_UNSIGNED_SUBTRACTION启用时，减法结果是签订了，即使任何操作数是无符号。例如，比较列的类型c2在表 t1与该列的 c2表t2：

mysql> SET sql_mode='';
mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;

mysql> DESCRIBE t1;
Field	Type	Null	Key	Default	Extra
c2	bigint(21) unsigned	NO		0

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;

mysql> DESCRIBE t2;
Field	Type	Null	Key	Default	Extra
c2	bigint(21)	NO		0

这意味着BIGINT UNSIGNED在所有上下文中都不是100％可用的。请参见 第12.10节“强制转换函数和运算符”。

NO_ZERO_DATE

该NO_ZERO_DATE模式会影响服务器是否允许 '0000-00-00'作为有效日期。它的效果还取决于是否启用了严格的SQL模式。

如果未启用此模式， '0000-00-00'则允许并且插入不会产生警告。

如果启用此模式，'0000-00-00' 则允许并且插入产生警告。

如果启用此模式和严格模式， '0000-00-00'则不允许插入产生错误，除非 IGNORE同时给出。对于 INSERT IGNORE和UPDATE IGNORE，'0000-00-00'允许和插入产生警告。

从MySQL 5.6.17开始， NO_ZERO_DATE不推荐使用并设置sql_mode值以包含它会生成警告。

NO_ZERO_IN_DATE

该NO_ZERO_IN_DATE模式会影响服务器是否允许年份部分为非零但月份或日期部分为0的日期。（此模式会影响日期，例如'2010-00-01'或 '2010-01-00'，但不会 '0000-00-00'。要控制服务器是否允许'0000-00-00'，请使用该 NO_ZERO_DATE模式。）效果的NO_ZERO_IN_DATE 还取决于是否启用严格的SQL模式。

如果未启用此模式，则允许零件的日期，并且插入不会产生警告。

如果启用此模式，则插入零件的日期'0000-00-00'并生成警告。

如果启用此模式和严格模式，则不允许零件的日期，并且插入会产生错误，除非IGNORE同时给出。对于INSERT IGNORE和 UPDATE IGNORE，插入零件的日期'0000-00-00'并生成警告。

从MySQL 5.6.17开始， NO_ZERO_IN_DATE不推荐使用并设置sql_mode值以包含它会生成警告。

ONLY_FULL_GROUP_BY

拒绝选择列表， HAVING条件或ORDER BY列表引用未在GROUP BY子句中指定的非聚合列的查询。

标准SQL的MySQL扩展允许HAVING子句中的引用 到选择列表中的别名表达式。启用 ONLY_FULL_GROUP_BY 禁用此扩展，因此要求 HAVING使用非混淆表达式写入子句。

有关其他讨论和示例，请参见 第12.19.3节“GROUP BY的MySQL处理”。

PAD_CHAR_TO_FULL_LENGTH

默认情况下，CHAR在检索时从列值中修剪尾随空格 。如果 PAD_CHAR_TO_FULL_LENGTH启用，则不会进行修剪，并且检索的 CHAR值将填充到其全长。此模式不适 VARCHAR用于在检索时保留尾随空格的列。

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.37 sec)

mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.01 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
c1	CHAR_LENGTH(c1)
xy	2

1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
c1	CHAR_LENGTH(c1)
xy	10

1 row in set (0.00 sec)
PIPES_AS_CONCAT

治疗||作为字符串并置运算符（同 CONCAT()），而不是作为一个同义词OR。

REAL_AS_FLOAT

对待REAL作为一个代名词 FLOAT。默认情况下，MySQL将其REAL视为同义词 DOUBLE。

STRICT_ALL_TABLES

为所有存储引擎启用严格SQL模式。无效的数据值被拒绝。有关详细信息，请参阅 严格SQL模式。

STRICT_TRANS_TABLES

为事务存储引擎启用严格的SQL模式，并在可能的情况下为非事务性存储引擎启用。有关详细信息，请参阅严格SQL模式。

组合SQL模式
提供以下特殊模式作为前一列表中模式值组合的简写。

ANSI

相当于 REAL_AS_FLOAT， PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE。

ANSI模式还会导致服务器为查询返回错误，在该查询中，S外部引用 的set函数 无法在已解析外部引用的外部查询中聚合。这是一个查询： S(outer_ref)

SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE ...);
这里，MAX(t1.b)不能在外部查询中聚合，因为它出现在该WHERE查询的 子句中。在这种情况下，标准SQL需要出错。如果 ANSI未启用模式，则服务器 将以与其解释相同的方式处理 此类查询 。 S(outer_ref)S(const)

请参见第1.7节“MySQL标准合规性”。

DB2

相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS。

MAXDB

相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS， NO_AUTO_CREATE_USER。

MSSQL

相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS。

MYSQL323

相当于MYSQL323， HIGH_NOT_PRECEDENCE。这意味着HIGH_NOT_PRECEDENCE 加上一些SHOW CREATE TABLE 特定的行为 MYSQL323：

TIMESTAMP列显示不包含DEFAULT或 ON UPDATE属性。

字符串列显示不包括字符集和排序规则属性。对于 CHAR和 VARCHAR列，如果排序规则为二进制，则将其BINARY附加到列类型。

该 表选项显示为 。 ENGINE=engine_nameTYPE=engine_name

对于MEMORY表，存储引擎显示为HEAP。

MYSQL40

相当于MYSQL40， HIGH_NOT_PRECEDENCE。这意味着HIGH_NOT_PRECEDENCE 加上一些特定的行为 MYSQL40。MYSQL323除了SHOW CREATE TABLE 不显示HEAP为MEMORY表的存储引擎之外，它们与for相同。

ORACLE

相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS， NO_AUTO_CREATE_USER。

POSTGRESQL

相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS。

TRADITIONAL

相当于 STRICT_TRANS_TABLES， STRICT_ALL_TABLES， NO_ZERO_IN_DATE， NO_ZERO_DATE， ERROR_FOR_DIVISION_BY_ZERO， NO_AUTO_CREATE_USER，和 NO_ENGINE_SUBSTITUTION。

严格的SQL模式
严格模式控制MySQL如何处理数据更改语句（如INSERT或）中的 无效或缺失值 UPDATE。由于多种原因，值可能无效。例如，列可能具有错误的数据类型，或者可能超出范围。当要插入的新行不包含其定义中NULL没有显式DEFAULT子句的非列 的值时，缺少值。（对于 NULL列，NULL如果缺少值，则插入。）严格模式也会影响DDL语句，例如CREATE TABLE。

如果严格模式不起作用，MySQL会为无效值或缺失值插入调整值并产生警告（请参见 第13.7.5.41节“显示警告语法”）。在严格模式下，您可以使用INSERT IGNORE 或生成此行为 UPDATE IGNORE。

对于SELECT 不更改数据的语句，无效值会在严格模式下生成警告，而不是错误。

从MySQL 5.6.11开始，严格模式会在尝试创建超过最大密钥长度的密钥时产生错误。以前，这会导致警告并截断最大密钥长度的密钥（与未启用严格模式时相同）。

严格模式不会影响是否检查外键约束。foreign_key_checks可以用于此。（请参见 第5.1.7节“服务器系统变量”。）

如果其中一个STRICT_ALL_TABLES或 STRICT_TRANS_TABLES已启用，则严格的SQL模式有效 ，尽管这些模式的效果有所不同：

对于事务表，当启用STRICT_ALL_TABLES或 STRICT_TRANS_TABLES启用时，数据更改语句中的值无效或缺失时会发生错误 。该声明被中止并回滚。

对于非事务性表，如果在要插入或更新的第一行中出现错误值，则对于任一模式的行为都是相同的：语句被中止，表保持不变。如果语句插入或修改多行，并且在第二行或更高行中出现错误值，则结果取决于启用的严格模式：

对于STRICT_ALL_TABLES，MySQL返回错误并忽略其余行。但是，由于已插入或更新了较早的行，因此结果是部分更新。要避免这种情况，请使用单行语句，可以在不更改表的情况下中止。

对于 STRICT_TRANS_TABLES，MySQL将无效值转换为列的最接近的有效值，并插入调整后的值。如果缺少值，MySQL将插入列数据类型的隐式默认值。在任何一种情况下，MySQL都会生成警告而不是错误，并继续处理该语句。第11.6节“数据类型默认值”中描述了隐式默认值。

严格模式也由零，零日期和零影响分割处理中的日期，与所述结合 ERROR_FOR_DIVISION_BY_ZERO， NO_ZERO_DATE和 NO_ZERO_IN_DATE模式。有关详细信息，请参阅这些模式的说明。

翻译自：https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html