严格模式:简述严格模式
设置模式中包含了
NO_ZERO_DATE
严格模式控制MySQL如何处理非法或丢失的输入值的SQL。有几种原因可以使一个值为非法。例如,数据类型错误或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值,则该值被丢失。
对于事务表,当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时,如果语句中有非法或丢失值,则会出现错误。SQL语句被回滚。
对于非事务表,STRICT_TRANS_TABLES,MySQL将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失,MySQL在列中插入隐式 默认值。在任何情况下,MySQL都会生成警告而不是给出错误并继续执行语句。
如果你不使用严格模式(即不启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式),对于非法或丢失的值,MySQL将插入调整后的值并给出警告。在严格模式,你可以通过INSERT IGNORE或UPDATE IGNORE来实现。
/事务表,什么是事物?一个最小的不可再分的工作单元;通常一个事务对应一个完整的业务,事务表支持将批处理当做一个完整的任务统一提交或回滚,即对包含在事务中的多条语句要么全执行,要么全部不执行。非事务表则不支持此种操作,批处理中的语句如果遇到错误,在错误前的语句执行成功,之后的则不执行。
/
如何解决?:
模式的设置和修改
方式一:先执行select @@sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除,
然后执行set sql_mode = '修改后的值'或者set session sql_mode='修改后的值';,例如:set session sql_mode='STRICT_TRANS_TABLES';
改为严格模式此方法只在当前会话中生效,关闭当前会话就不生效了。
方式二:先执行select @@global.sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除,
然后执行set global sql_mode = '修改后的值'。此方法在当前服务中生效,重新MySQL服务后失效
方法三:在mysql的安装目录下,或my.cnf文件(windows系统是my.ini文件),新增 sql_mode = ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,
添加my.cnf如下:
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
然后重启mysql。
此方法永久生效.当然生产环境上是禁止重启MySQL服务的,所以采用方式二加方式三来解决线上的问题,那么即便是有一天真的重启了MySQL服务,也会永久生效了。
临时修改sql_mode【需要退出数据库重新进入】
mysql> set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
永久修改sql_mode【需要重新进入数据库】
在配置文件中[mysqld]下加入
sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'