当前位置:   article > 正文

MySQL基础篇:sql_mode配置_sql mode

sql mode


零、简介

MySQL数据库中的一个环境变量sql_mode,定义了MySQL应该支持的SQL语法、数据校验等等。

sql_mode 是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境下必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须设置。

一、sql_mode常用来解决的几类问题

  • 通过设置sql_mode,可以完成不同严格程度的数据校验,有效地保证数据准确性;
  • 通过设置sql_mode为宽松模式,来保证大多数sql符合标准的sql语法,这样应用在不同数据库之间进行迁移时。则不需要对业务sql进行较大的修改;
  • 在不同数据库之间进行数据迁移之前,通过设置sql_mode可以使MySQL上的数据更方便地迁移到目标数据库中

二、sql_mode包含的模式

MySQL5.5以上版本支持三种sql_mode模式,具体如下:

ANSI模式TRADITIONAL模式STRICT_TRANS_TABLES模式
宽松模式
,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告
严格模式
,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误。用于事务时,会进行事务的回滚
严格模式
,进行数据的严格校验,错误数据不能插入,报error错误

三、sql_mode各个选项作用示例

3.1、sql_mode为空(对于不符合定义的值,会截断到符合定义类型)

查看数据库sql_mode的默认值

在这里插入图片描述

由上图可知,可以通过两种方式查看MySQL5.5默认的sql_mode的值,即MySQL5.5中的环境变量sql_mode默认为空。

在这里插入图片描述

按照上图所示的步骤进行操作:

  1. 在MySQL数据库中建立一张测试表;
  2. 向测试表中插入两条测试数据,并查看数据插入语句是否运行成功;
  3. 通过show warnings命令查看,MySQL数据库中是否有警告或者报错信息;
  4. 通过select命令查看最终插入数据库中数据的内容。

结论:在sql_mode为空的模式下,当我们插入数据时,为满足列长度要求时,数据同样会插入成功,但对超出列长度要求的字段进行截断,同时报warning警告

3.2、sql_mode为ANSI

修改MySQL数据库的sql_mode模式为ANSI,具体如下:

在这里插入图片描述

修改MySQL数据库sql_mode模式为ANSI,针对修改ANSI模式后的结果REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI的具体含义,稍后详解。

在这里插入图片描述

结论:ANSI模式下,插入数据时,未满足列长度要求时,数据同样会插入成功,但是对超出列长度的字段进行截断,同时报告warning警告

3.3、sql_mode为TRADITIONAL

修改MySQL数据库的sql_mode模式为TRADITIONAL,具体如下:

在这里插入图片描述

修改MySQL数据库sql_mode模式为ANSI,针对修改ANSI模式后的结果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的具体含义,稍后详解。

在这里插入图片描述

结论:
1、严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误,而不仅仅是警告。用于事务时,会进行事务的回滚。

2、一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎,这种方式不是你想要的,因为出现错误前进行的数据更改不会“滚动”,结果是更新“只进行了一部分”。

3.4、sql_mode为STRICT_TRANS_TABLES

修改MySQL数据库的sql_mode模式为STRICT_TRANS_TABLES,具体如下:

在这里插入图片描述

在这里插入图片描述

结论:在STRICT_TRANS_TABLES模式下,插入数据时,mysql会严格的进行数据的校验,当发现插入列值未满足要求,直接报告error错误,保证了错误数据无法插入到数据库中

四、sql_mode选项说明

模式名称模式说明
ONLY_FULL_GROUP_BY对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了
STRICT_TRANS_TABLES在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制,
只对支持事务的表启用严格模式
NO_ZERO_IN_DATE在严格模式下,不允许日期和月份为零
NO_ZERO_DATE在非严格模式下,可以插入形如“00-00-0000:00:00”的非法日期,mysql仅抛出一个警告,而启用该选项后,mysql不允许插入零日期,插入0日期会抛出错误而非警告
ERROR_FOR_DIVISION_BY_ZERO在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER禁止GRANT创建密码为空的用户
PIPES_AS_CONCAT将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
ANSI_QUOTES启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符
REAL_AS_FLOAT将real视为float的同义词而不是double的同义词
STRICT_ALL_TABLES对所有引擎的表都启用严格模式

五、sql_mode设置

  • 查看当前sql_mode
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
  • 1
  • 2
  • 设置当前sql_mode
SET GLOBAL sql_mode = 'modes……';
SET SESSION sql_mode = 'modes ……';
  • 1
  • 2
  • 如果使用mysql,为了继续保留大家使用oracle的习惯,可以对mysql的sql_mode设置如下

进入/etc/my.cnf配置文件,添加如下内容后,重启MySQL服务:

在这里插入图片描述

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/我家自动化/article/detail/709339
推荐阅读
相关标签
  

闽ICP备14008679号