MySQL 的 sql_mode 模式介绍:为什么 MySQL 中 int,float,double 类型字段插入空字符时自动转为0

### 1,sql_mode 模式介绍

如果设置的是宽松模式,那么我们在插入数据的时候,即便是给了一个错误的数据,也可能会被接受,并且不报错。严格模式的话,就会报错。

例子1:

表中一个字段类型是char(10),如果插入该字段的数据的长度超过了10,例如'1234567890abc',那么并不会报错,实际上存入该字段的数据时'1234567890'。

例子2:

表中一个字段类型时 INT,如果插入数据不是整型数据,而是一个字符串,比如:'120abc',实际存入数据是‘120’。如果插入数据是‘abc’、‘abc120’或者‘’,实际存入数据是‘0’。实际存入数据是字符串强制转换成integer 后的结果。


### 2,如何设置 sql_mode 严格模式

MySQL5.6 和 MySQL5.7 默认的 sql_mode 模式参数是不一样的,MySQL 5.6 的 sql_mode 是NO_ENGINE_SUBSTITUTION,即空值,可以理解为宽松模式。MySQL 5.7 的 sql_mode 是STRICT_TRANS_TABLES,也就是严格模式。

找到 MySQL 5.6 的配置文件 my.cnf,在 [mysqld] 下,添加下列配置后重启。

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


### 3,常见的 sql_mode 值介绍:

1,ONLY_FULL_GROUP_BY:出现在SELECT语句、HAVING条件和ORDER BY语句中的列,必须是GROUP BY的列或者依赖于GROUP BY列的函数列。


2,NO_AUTO_VALUE_ON_ZERO:该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。


3,STRICT_TRANS_TABLES:如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制。


4,NO_ZERO_IN_DATE:是否允许日期中的月份和日包含0。如果开启此模式,2020-01-00是不允许的,但是0000-02-01是允许的。


5,NO_ZERO_DATE:不允许插入零日期。


6,ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时,MySQL返回NULL。


7,NO_AUTO_CREATE_USER:禁止 GRANT 创建密码为空的用户。


8,NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常。


9,PIPES_AS_CONCAT:将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似。


10,ANSI_QUOTES:启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符。


### 4,如何选择

新版本的 sql_mode 严格模式是默认的,但是一些老程序会依赖 sql_mode 宽松模式。如果线上多个数据库,数据库默认设置肯定要设为宽松模式。

开发新程序的时候,数据库也要设置为严格模式。



真诚赞赏,手留余香
赞赏
Node.js,后端
什么是 RESTful API 的幂等性
2022-10-09
Web Server
Nginx 通过日志统计访问数据
2022-11-03
ngtwewy
随机推荐
Koa.js 限流中间件 koa-ratelimit
封装一个像 axios.js 一样的 http 请求拦截器
如何使用命令修改 MySQL 数据库名称
数据库中间表应该如何命名
CSS 媒体特性 prefers-color-scheme
什么是DDD领域驱动设计?
依赖注入(DI)和控制反转(Ioc)
Debian11 安装笔记2:编译安装PHP
MySQL 函数 FIND_IN_SET 和 IN 的区别
CSS 实现图标详解 image sprite、Icon Font、svg 和 CSS 绘制

微信联系我

夜间模式切换
回到顶部