MySQL或者MariaDB里面sql_mode的设置详解

发布时间:2020-03-19 15:11:40 作者:我的二狗呢
来源:网络 阅读:9585

详见MySQL5.6手册的"Server SQL Modes"章节。

地址:http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html


参考了好几个网站的博文,出处不太好找了,对大家的分享表示感谢。

 

MySQL5.6sql_modeSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

MariaDB10.0.17sql_mode是空的。

 

sql_mode 分为globalsession作用范围。

> 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

等价于:REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

更改语法和行为,使其更符合标准SQL

 

等价于:STRICT_TRANS_TABLES

如果不能将给定的值插入到事务表中,则放弃该语句。对于非事务表,如果值出现在单行语句或多行语句的第1行,则放弃该语句。

 

等价于: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)  提示插入成功并有一个警告

该模式仅对DATEDATETIME类型有效,而对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

INSERTUPDATE过程中,

该模式未启用的话,如果数据被零除,最终结果插入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

该选项影响列为自增长的插入。

在默认设置下,插入0NULL代表生成下一个自增长值。

如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
NO_BACKSLASH_ESCAPES

反斜杠“\”作为普通字符而非转义符

>set sql_mode='';

>select '\\';

+---+

| \ |

+---+

| \ |

+---+

>SET sql_mode = 'NO_BACKSLASH_ESCAPES';

>select '\\';

+----+

| \\ |

+----+

| \\|

+----+
NO_DIR_IN_CREATE

在创建表时忽视所有INDEX DIRECTORYDATA 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-022015-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_TABLESSTRICT_TRANS_TABLES都表示启用严格模式。但二者还有些区别:

严格模式控制MySQL如何处理非法或丢失的输入值。有几种原因可以使一个值为非法。例如,数据类型错误,不适合列,或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值,则该值被丢失。

 

对于事务表,当启用STRICT_ALL_TABLESSTRICT_TRANS_TABLES模式时,如果语句中有非法或丢失值,则会出现错误。语句被放弃并滚动。

 

对于非事务表,如果插入或更新的第1行出现坏值,两种模式的行为相同。语句被放弃,表保持不变。如果语句插入或修改多行,并且坏值出现在第2或后面的行,结果取决于启用了哪个严格选项:

 

一般情况下,不要使用MySQL来检查列的内容。最安全的方式(通常也较快)是,让应用程序负责,仅将有效值传递给数据库。

 

 

在严格模式下,我们仍然可以使用[INSERT|UPDATE] IGNORE INTO TB_NAMEVALUES(xxxx); 将错误当中警告对待,但是实际上不建议这样做。


MySQL版本>=5.6.6

默认为: NO_ENGINE_SUBSTITUTION

可选参数和上面的一样。就不一一解释了。


推荐阅读:
  1. MySQL中sql_mode合理设置的示例分析
  2. MySQL关于sql_mode解析与设置讲解

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql sql mode

上一篇:php站点压力测试

下一篇:ruby on rails 安装

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》