MySQL5.6中sql_mode常用值实例分析

SQL模式定义了两个方面：MySQL应该支持的SQL语法，以及应该对数据进行哪些验证检查。


SQL语法支持类

ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果SELECT、HAVING或ORDER BY子句中的列，不出现在GROUP BY中，那么这个SQL是非法的。这是可以理解的，因为如果不勾选并显示在group by列中，就会出现矛盾。
5.7中默认开启，所以从5.6升级到5.7的过程需要注意：


1 SELECT list的Expression #1 is not in GROUP BY


2 子句并包含非聚合列


3'1066export.ebay_order_items.TransactionID'，


4 在功能上不依赖于 GROUP BY


5 子句中的列；这与 sql_mode=only_full_group_by 不兼容



ANSI_QUOTES
当启用 ANSI_QUOTES 时，不能使用双引号来引用字符串，因为它被解释为标识符并且具有与`相同的效果。
设置后 update t set f1="" ... 会报 Unknown column ‘’ in ‘field list’ 之类的语法错误。
() 类似
NO_TABLE_OPTIONS
使用SHOW CREATE TABLE不会输出MySQL特有的语法部分，例如ENGINE。使用mysqldump跨数据库类型迁移时需要考虑这一点。
NO_AUTO_CREATE_USER
字面意思是不自动创建用户。在对MySQL用户进行授权时，我们习惯使用GRANT ... ON ... TO dbuser来一起创建用户。设置该选项后，就和oracle操作类似了。
授权前必须先创建用户化。从5.7.7开始也是默认的。


数据校验类
NO_ZERO_DATE
日期‘0000-00-00’被认为是非法的，取决于后续是否设置了严格模式。
1.如果设置了严格模式，自然满足NO_ZERO_DATE。但如果是INSERT IGNORE或UPDATE IGNORE，'0000-00-00'仍然是允许的，并且只显示警告
2。如果非严格模式下设置了NO_ZERO_DATE，效果和上面一样， '0000-00-00' '允许但显示警告；如果未设置 NO_ZERO_DATE，则不会将任何警告视为完全合法的值。
3. NO_ZERO_IN_DATE的情况与上面类似。区别在于控制日期和星期是否可以为0，即2010-01-00是否合法。
NO_ENGINE_SUBSTITUTION
使用 ALTER TABLE 或 CREATE TABLE 指定 ENGINE 时，所需的存储引擎将被禁用或不编译。我应该怎么办？当启用NO_ENGINE_SUBSTITUTION时，直接抛出错误；当这个值我不设置，CREATE 使用默认存储引擎，ATLER 不进行更改，并抛出警告。
STRICT_TRANS_TABLES
设置它意味着启用严格模型。
请注意，STRICT_TRANS_TABLES 不是多种策略的组合。它单独指的是如何处理INSERT和UPDATE中较小或无效的值：
1.如上所述，在严格模式下将 '' 传递给 int 是非法的。如果启用非严格模式，则变为0，产生警告
2.Out Of Range，成为插入的最大边界值
3.插入新行时缺少值不包含定义中没有显式DEFAULT子句的非NULL列的值


sql_mode一般很少关注它，并且在遇到实际问题之前不会去启动和停止上面的条目。我们经常设置的sql_mode有ANSI、STRICT_TRANS_TABLES、TRADITIONAL，ansi和traditional是以上的组合。
ANSI：更改 s语法和行为与标准 SQL 更加一致
相当于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE
传统：更像传统的 SQL 数据库系统，这种模式的简单描述是当“改为给出错误”时将不正确的值插入到列中时出现警告”。
相当于 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
ORACLE：相当于 PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY _OPTION S、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER

无论如何在这种模式下，当产生错误时，意味着单个SQL语句执行失败。对于支持事务的表，会导致当前事务回滚；但如果不是在事务中执行，或者存储引擎表不支持事务，则可能会导致数据不一致。 。 MySQL相信es 数据不一致比直接报错终止更严重。因此，STRICT_TRANS_TABLES对于非事务表仍然允许尽可能继续写入，比如给出“最合理”的默认值或者截断。对于STRICT_ALL_TABLES来说，如果是单次更新，则不会受到影响，但如果有多次更新，则第一个会成功，如果后续失败，则会有部分更新。

感谢您仔细阅读本文。希望小编分享《MySQL5.6中sql_mode常用值解析》这篇文章对大家有帮助，也希望大家多多支持，关注行业资讯频道。更多相关知识等待您学习！