mysql关于timestamp字段相关内容

发现5.6和5.7版本的创建表不一致，从5.6导出数据表创建sql文件，然后导入到5.7表会报错，timestamp不能为空

查看的sql_mode

mysql5.0以上支持的三种模式

1. ANSI
2. TRADITIONAL
3. STRICT_TRANS_TABLES

ANSI模式：宽松模式
对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
对于除数为0的结果的字段，会用NULL值代替。

TRADITIONAL模式：严格模式
当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。
用于事务时，会进行事务的回滚。使用非事务存储引擎，出现错误前进行的数据更改不会回滚，
结果是只更新的一部分

STRICT_TRANS_TABLES模式：严格模式
进行数据的严格校验，错误数据不能插入，报error错误。
如果不能将值插入到事务表中，则进行回滚。
对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。

注：

Myisam不支持事务，非事务存储引擎
Innodb支持事务，事务存储引擎
事务表(TST)和非事务表 (NTST)

 

支持的sql语法模式

执行sql命令可以查看sql模式

select @@sql_mode
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

在严格模式下，会检查sql的语法，如果不通过则会直接报错

比如下面的字段定义，设置时间戳timestamp默认为null，会直接报错

`create_time` TIMESTAMP(0) DEFAULT NULL

`update_time` TIMESTAMP(0) DEFAULT NULL

 

如果把DEFAULT NULL去掉后，执行可以通过

使用show create table 表名

查看建表语句,其他建表语句省略

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

在STRICT_TRANS_TABLE模式下，

缺省时，第一个timestamp字段会为它设置当前系统时间戳，并且会随着记录的更新自动更新。

 第二个及后面的timestamp列都默认设置为  年 - 月 - 日  时：分：秒

一般来说，create_time，这个创建时间第一次赋值后就不变了，而系统默认是自动更新的，不符合我们的需求。

我们需要在建表的时候主动设置默认值

`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

等价的

`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`update_time` timestamp,

只有第一个timestamp才会设置，第二个及后面的都是默认设置为0000-00-00 00:00:00

 

额外的内容：

sql_mode可以在my.ini或者my.inf文件的[mysqld]节点下进行配置

sql_mode的值

ONLY_FULL_GROUP_BY

出现在select语句、HAVING条件和ORDER BY语句中的列，必须是GROUP BY的列或者依赖于GROUP BY列的函数列。
user表有三个字段，name, age, sex
select * from user  group by sex;
执行上面的sql语句会报错ERROR 1055 (42000):
select中查询的是所有列，在group by中必须全部出现才可以

select sex from user group by sex;
这句sql就可以执行通过

如果要让第一个代码能正常通过，则需要将sql_mode = ONLY_FULL_GROUP_BY ....这个配置中的ONLY_FULL_GROUP_BY去掉，

select * from user  group by sex;就可以正常运行

NO_ZERO_IN_DATE：日期类型字段，年月日不能全部为0
在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告
 
NO_ZERO_IN_DATE：
在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

NO_ZERO_DATE：
在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告

ERROR_FOR_DIVISION_BY_ZERO：
在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。
如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL

NO_AUTO_CREATE_USER：
防止GRANT自动创建新用户，除非还指定了密码。

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