MySQL或者MariaDB里面sql_mode的设置详解
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL或者MariaDB里面sql_mode的设置详解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8144字，纯文字阅读大概需要12分钟。
内容图文
详见MySQL5.6手册的"Server SQL Modes"章节。地址：http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
参考了好几个网站的博文，出处不太好找了，对大家的分享表示感谢。
MySQL5.6的sql_mode是STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
MariaDB10.0.17的sql_mode是空的。
sql_mode 分为global、session作用范围。
> SETGLOBAL sql_mode=‘STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION‘;
> SETSESSION sql_mode=‘STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION‘;
>SELECT @@GLOBAL.sql_mode;
>SELECT @@SESSION.sql_mode;
最重要的sql_mode
ANSI
等价于：REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
更改语法和行为，使其更符合标准SQL。
STRICT_TRANS_TABLES
等价于：STRICT_TRANS_TABLES
如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。
TRADITIONAL
等价于：STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Make MySQL的行为象“传统”SQL数据库系统。该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。注释：一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎，这种方式不是你想要的，因为出现错误前进行的数据更改不会“滚动”，结果是更新“只进行了一部分”。
如果把sql_mode的值设置成后面的两个值（STRICT_TRANS_TABLES或者TRADITIONAL，也就是我们说的严格模式），那么当在列中插入或更新不正确的值时，mysql将会给出错误，并且放弃insert/update操作。
在我们的一般应用中建议使用STRICT_TRANS_TABLES或者TRADITIONAL这两种模式，而不是使用默认的空或ANSI模式。
但是需要注意的问题是，如果数据库运行在严格模式下，并且你的存储引擎不支持事务(现在基本上都是用的InnoDB引擎了,不支持事务的较少见)，那么有数据不一致的风险存在，比如一组sql中有两个dml语句，如果后面的一个出现了问题，但是前面的已经操作成功，那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失，从而得到一个合适的选择。
下面的内容翻译自官方文档，有些参数翻译不上，搜了下网上也很少提到，翻译时候直接忽略了。
全部sql_mode
MySQL版本<=5.6.5
默认为空
可选值如下：
| 参数 | 含义及示例 |
| ALLOW_INVALID_DATES | 允许无效的日期插入到数据库(无效的日期插入时候会有warning，但仍能插入，但是日期会变成0000-00-00这种无效的数据) >set session sql_mode=‘ALLOW_INVALID_DATES‘; >insert into new(`d`) VALUES(‘201‘); Query OK, 1 row affected, 1 warning (0.01sec) 提示插入成功并有一个警告 该模式仅对DATE和DATETIME类型有效，而对TIMESTAMP无效，因为TIMESTAMP总是要求一个合法的输入。 |
| ANSI_QUOTES | 启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符。 >set session sql_mode=‘ANSI_QUOTES‘; >insert into teachersvalues(22,‘lee‘,12,"M"); ERROR1054 (42S22): Unknown column ‘M‘ in ‘field list‘ 用了双引号的话，直接报错了 |
| ERROR_FOR_DIVISION_BY_ZERO | 在INSERT或UPDATE过程中， 该模式未启用的话，如果数据被零除，最终结果插入NULL并且不提示warnings 该模式启用的话，如果数据被零除，最终结果插入NULL并且会提示warnings >set session sql_mode=‘STRICT_TRANS_TABLES‘; >insert into t1 VALUES(5/0); Query OK, 1 row affected (0.00 sec) 插入时候没有警告，结果是个NULL
>set session sql_mode=‘ERROR_FOR_DIVISION_BY_ZERO‘; >insert into t1 VALUES(6/0); QueryOK, 1 row affected, 1 warning (0.01 sec) 插入时候有警告，结果是个NULL |
| HIGH_NOT_PRECEDENCE | 设置优先级关系【主要为了兼容老版本的MySQL】 > SET sql_mode = ‘‘; l> SELECT NOT 1 BETWEEN -5 AND 5; 结果为0 > SET sql_mode = ‘HIGH_NOT_PRECEDENCE‘; > SELECT NOT 1 BETWEEN -5 AND 5; 结果为1 |
| IGNORE_SPACE | 函数名和括号“(”之间有空格。除了增加一些烦恼，这个选项好像没有任何好处，要访问保存为关键字的数据库、表或列名，用户必须引用该选项。 例如某个表中有user这一列，而MySQL数据库中又有user这个函数， user会被解释为函数，如果想要选择user这一列，则需要引用。 >createtable `partition` (i int(3)); 对于表名或者其它与保留关键字的地方或者内置函数同名称的话，，必须加反引号，不然MySQL报错。 |
| NO_AUTO_CREATE_USER | 禁止GRANT创建密码为空的用户。 > SET sql_mode = ‘NO_AUTO_CREATE_USER‘; > grant all on *.* to ‘lirl‘@‘%‘; ERROR 1133 (42000): Can‘t find any matchingrow in the user table >grant all on *.* to ‘lirl2‘@‘%‘identified by ‘123456‘; QueryOK, 0 rows affected (0.00 sec) |
| NO_AUTO_VALUE_ON_ZERO | 该选项影响列为自增长的插入。 在默认设置下，插入0或NULL代表生成下一个自增长值。 如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。 |
| NO_BACKSLASH_ESCAPES | 反斜杠“\”作为普通字符而非转义符 >set sql_mode=‘‘; >select ‘\\‘; +---+ | \ | +---+ | \ | +---+ >SET sql_mode = ‘NO_BACKSLASH_ESCAPES‘; >select ‘\\‘; +----+ | \\ | +----+ | \\| +----+ |
| NO_DIR_IN_CREATE | 在创建表时忽视所有INDEX DIRECTORY和DATA DIRECTORY的选项。 > set sql_mode=‘‘; > create table t1 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp"; 说明：此时查看/tmp下面会有相关的ibd表空间文件
> set sql_mode=‘NO_DIR_IN_CREATE‘; > create table t2 (`age` char(3)) DATADIRECTORY="/tmp" INDEX DIRECTORY="/tmp"; 说明：此时查看 /tmp下面找不到相关的ibd表空间文件，表文件还是原来的datadir下面。 |
NO_ENGINE_SUBSTITUTION 不使用默认引擎替代 | 设置这个参数后，如果需要的存储引擎被禁用或未编译，那么抛出错误。 不设置这个参数的话，找不到指定的存储引擎的话，会用默认的存储引擎替代，但会有warning提示。 >set sql_mode=‘‘; > create table t4 (`age` int(3))ENGINE=Inff; > show create table t4\G *************************** 1. row*************************** Table: t4 Create Table: CREATE TABLE `t4` ( `age` int(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 上面我们随便用了个不存在的存储引擎建表时候也没报错，只要有个warning提示，系统自带用默认的存储引擎替代了。
> set sql_mode=‘NO_ENGINE_SUBSTITUTION‘; > create table t4 (`age` int(3))ENGINE=Inff; ERROR 1286 (42000): Unknown storage engine‘Inff‘ 显式设置了sql_mode=‘NO_ENGINE_SUBSTITUTION‘后，建表时候指定一个不存在的存储引擎后，就直接报错了。 |
| NO_FIELD_OPTIONS | |
| NO_KEY_OPTIONS | |
| NO_TABLE_OPTIONS | |
| NO_UNSIGNED_SUBTRACTION | |
| NO_ZERO_DATE | 启用的话，表示允许0000-00-00这种日期的插入，但是会有warnings 不启用的话，表示允许0000-00-00这种日期的插入，不会有warnings
如果启动NO_ZERO_DATE模时,还启用了strict模式，则0000-00-00这种不允许插入, 显式使用IGNORE参数的话，才能插入成功。 > setsql_mode=‘NO_ZERO_DATE,.STRICT_TRANS_TABLES‘; > insert into tb2 VALUES(‘0000-00-00‘); 这样插入会报错的 >INSERT IGNORE INTO tb1 VALUES(‘0000-00-00‘); # 加了IGNORE后才能插入成功 |
| NO_ZERO_IN_DATE | > set sql_mode=‘NO_ZERO_IN_DATE‘; 允许插2015-00-02、2015-03-00 这种非法日期，但是实际上插入的是0000-00-00。插入2013-02-01这种正常的数据是没问题的。 > set sql_mode=‘‘; >insert into tb2 VALUES(‘2010-00-02‘); 允许这种格式的日期插入 |
| ONLY_FULL_GROUP_BY | 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。 |
| PAD_CHAR_TO_FULL_LENGTH | |
| PIPES_AS_CONCAT | 将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似。 |
| REAL_AS_FLOAT | 实数是浮点数的同义词。【默认情况下，MySQL把实数当double同义词】 double 8字节 float 4字节 real8字节 |
| STRICT_ALL_TABLES | 严格模式 |
| STRICT_TRANS_TABLES | 严格模式 |
STRICT_ALL_TABLES和STRICT_TRANS_TABLES都表示启用严格模式。但二者还有些区别：
严格模式控制MySQL如何处理非法或丢失的输入值。有几种原因可以使一个值为非法。例如，数据类型错误，不适合列，或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值，则该值被丢失。
对于事务表，当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时，如果语句中有非法或丢失值，则会出现错误。语句被放弃并滚动。
对于非事务表，如果插入或更新的第1行出现坏值，两种模式的行为相同。语句被放弃，表保持不变。如果语句插入或修改多行，并且坏值出现在第2或后面的行，结果取决于启用了哪个严格选项：
对于STRICT_ALL_TABLES，MySQL返回错误并忽视剩余的行。但是，在这种情况下，前面的行已经被插入或更新。这说明你可以部分更新，这可能不是你想要的。要避免这点，最好使用单行语句，因为这样可以不更改表即可以放弃。
对于STRICT_TRANS_TABLES，MySQL将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失，MySQL在列中插入隐式默认值。在任何情况下，MySQL都会生成警告而不是给出错误并继续执行语句。
一般情况下，不要使用MySQL来检查列的内容。最安全的方式（通常也较快）是，让应用程序负责，仅将有效值传递给数据库。
在严格模式下，我们仍然可以使用[INSERT|UPDATE] IGNORE INTO TB_NAMEVALUES(xxxx); 将错误当中警告对待，但是实际上不建议这样做。
MySQL版本>=5.6.6
默认为: NO_ENGINE_SUBSTITUTION
可选参数和上面的一样。就不一一解释了。
MySQL或者MariaDB里面sql_mode的设置详解
标签：mysql sql_mode
本文系统来源：http://lee90.blog.51cto.com/10414478/1826180
内容总结
以上是互联网集市为您收集整理的MySQL或者MariaDB里面sql_mode的设置详解全部内容，希望文章能够帮你解决MySQL或者MariaDB里面sql_mode的设置详解所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。