MySql5.7版本-timestamp

1.Mysql5.7-sql_mode

　　MySQL 5.6以后timestamp设定默认值规则改变，不能为”0000 00-00 00:00:00”，一般安装的5.7版本的sql_mode(系统变量)配置为严格模式，在严格模式下将控制我们在sql脚本的值限制。

2.常见异常：

 --执行语句：

CREATE TABLE IF NOT EXISTS `Goods`
(
`goods_Id` INT NOT NULL COMMENT '商品Id',
`goods_Name` VARCHAR(30) COMMENT '商品名称',
`goods_CardNo` VARCHAR(50) COMMENT '商品编码',
`goods_Price` DOUBLE DEFAULT NULL COMMENT '商品进价',
`shopp_Price` DOUBLE DEFAULT NULL COMMENT '商品售价',
`goods_CreateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '进货时间',
`goods_lastupdateTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT'最后更新时间',
PRIMARY KEY (`goods_Id`),
KEY `CarId` (`goods_Id`) USING BTREE,
`Cat_Id` INT COMMENT '商品类型',
`goods_Number` INT COMMENT '商品数量'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 --报错：

Invalid default value for 'goods_lastupdateTime'

3.解决方案

  --查询mysql下当前的sql_mode系统变量配置详情，删除对应配置：NO_ZERO_IN_DATE，NO_ZERO_DATE

  ①此种配置方式只对当前会话状态下有效，重启MySQL服务后配置失效，退回到原来配置

   -- mysql控制台下：

mysql>show session variable '%sql_mode%';
mysql>set sql_mode=
"ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

   -- Navicat下：

 

  ②配置永久有效，在MySQL配置文件下添加配置，window下为my.ini，Liunx下为my.cnf

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  -- 重新启动MySQL服务生效

　[~] systemctl restart mysqld

4.timestamp属性之：CURRENT_TIMESTAMP与ON UPDATE CURRENT_TIMESTAMP详解

①CURRENT_TIMESTAMP属性说明：当向数据表执行 insert操作时，如果有个 timestamp字段属性设为 CURRENT_TIMESTAMP，则无论这个字段有没有set值都插入当前系统时间。

②ON UPDATE CURRENT_TIMESTAMP属性说明：当timestamp字段属性配置ON UPDATE CURRENT_TIMESTAMP时，在后续数据表中数据发生Update操作将自动更新执行时间。

③测试：

CREATE TABLE IF NOT EXISTS `t_map`(
`Id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增主键Id',
`Name` VARCHAR(20) DEFAULT '',
-- '在执行insert语句后此字段记录为当前系统时间且后续不受update操作影响而自动更新'
`Time_1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ，
-- '执行insert操作，此字段不赋值默认为0000-00-00 00:00:00且不受update操作影响而更新，如有报错，按以上方式处理'
`Time_2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
-- '在执行insert操作后，此字段记录当前系统时间，当执行Update操作，此字段更新为最新系统update操作时间'
`Time_3` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ，
-- '在执行insert操作，此字段不赋值默认为0000-00-00 00:00:00且受后续update操作影响更新为操作最新时间'
`Time_4` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

  --执行insert插入语句

INSERT INTO t_map(`Name`) VALUES('a'),('b'),('c'),('d');

  --执行Update操作

-- 更新Id=1的值，可以看到Time_3、Time_4已经更新为当前系统最新时间
UPDATE t_map SET `Name`='aa' WHERE Id=1;

5.sql_mode系统变量值说明 

1.ONLY_FULL_GROUP_BY：
    对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中。

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

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

4.NO_ZERO_IN_DATE:
    在严格模式下,不允许日期和月份为零。

5.NO_ZERO_DATE:
    设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

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后,不能用双引号来引用字符串,因为它被解释为识别符。

注：ORACLE的sql_mode设置等同:PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

6.参考文献

①https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

②https://www.cnblogs.com/jiliunyongjin/p/7569893.html

③https://www.cnblogs.com/wenxin1120/p/11234990.html