mysql中TIMESTAMP设置默认时间为当前时间

在我们保存数据进入到数据库中时多半会使用像php之类的脚本来获取一个时间保存到mysql中,其实在mysql可以直接使用TIMESTAMP 数据类型来实现默认类型了,下面一起来看看.

很多时候,为了简单,我们在设计数据表的时候,都需要设置时间字段为当前时间,可是MySQL 中,默认值无法使用函数,也就是你无法设置某一列,默认值是 NOW () 这样的处理,那怎么办呢？

TIMESTAMP 数据类型,插入的时候,忽略该列即可,代码如下:

1. dtTIMESTAMP
2. /*等价于*/
3. dtTIMESTAMPdefaultCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP
5. createtabletestB(
6. idintPRIMARYKEY,
7. valvarchar(10),
8. dtTIMESTAMP
9. );
11. /*我们试着插入下数据看看*/
12. INSERTINTOtestB(id,val)VALUES(1,'A');
13. INSERTINTOtestB(id,val)VALUES(2,'B');
14. //结果
16. SELECT*FROMtestB;
17. +----+------+---------------------+
18. |id|val|dt|
19. +----+------+---------------------+
20. |1|A|2014-08-2114:24:20|
21. |2|B|2014-08-2114:24:21|
22. +----+------+---------------------+
23. 2rowsinset(0.00sec)

注意:MySQL的timestamp类型时间范围between '1970-01-01 00:00:01' and '2038-01-19 03:14:07'，超出这个范围则值记录为'0000-00-00 00:00:00'

该类型的一个重要特点就是保存的时间与时区密切相关，上述所说的时间范围是UTC(Universal Time Coordinated)标准，指的是经度0度上的标准时间，我国日常生活中时区以首都北京所处的东半球第8区为基准，统一使用东8区时间(俗称北京时间)，比UTC要早8个小时，服务器的时区设置也遵照此标准，因此对应过来timestamp的时间范围则应校准为'1970-01-01 08:00:01' and '2038-01-19 11:14:07'，也就是说东八区的1970-1-1 08:00:01等同于UTC 1970-1-1 00:00:01。

需要特点注意,timestamp类型的时间不仅仅与写入记录时的时区有关，显示时也与时区有关,例如,代码如下:

1. mysql>descj1_dt;
2. +-------+-----------+------+-----+-------------------+-------+
3. |Field|Type|Null|Key|Default|Extra|
4. +-------+-----------+------+-----+-------------------+-------+
5. |dt|timestamp|NO||CURRENT_TIMESTAMP||
6. +-------+-----------+------+-----+-------------------+-------+
7. 1rowinset(0.00sec)
8. mysql>insertintoj1_dtvalues('1970-01-0108:00:01');
9. QueryOK,1rowaffected(0.00sec)
10. mysql>select*fromj1_dt;
11. +---------------------+
12. |dt|
13. +---------------------+
14. |1970-01-0108:00:01|
15. +---------------------+
16. 1rowinset(0.00sec)
17. mysql>settime_zone='+0:00';
18. QueryOK,0rowsaffected(0.00sec)
19. mysql>select*fromj1_dt;
20. +---------------------+
21. |dt|
22. +---------------------+
23. |1970-01-0100:00:01|
24. +---------------------+
25. 1rowinset(0.00sec)
26. mysql>settime_zone='+1:00';
27. QueryOK,0rowsaffected(0.00sec)
28. mysql>select*fromj1_dt;
29. +---------------------+
30. |dt|
31. +---------------------+
32. |1970-01-0101:00:01|
33. //开源代码phpfensi.com
34. +---------------------+
35. 1rowinset(0.00sec)

如上述所示,根据时区的不同,显示的日期也是不一样的,这正是timestamp类型在MySQL日期类型中独有的时区特点.

如果向timestamp类型列插入的值超出了指定范围,则实际实际保存的值为'0000-00-00 00:00:00',并触发一个警告信息,代码如下:

1. mysql>settime_zone='+8:00';
2. QueryOK,0rowsaffected(0.00sec)
3. mysql>select*fromj1_dt;
4. +---------------------+
5. |dt|
6. +---------------------+
7. |1970-01-0108:00:01|
8. +---------------------+
9. 1rowinset(0.00sec)
10. mysql>insertintoj1_dtvalues('1970-01-0100:00:01');
11. QueryOK,1rowaffected,1warning(0.00sec)
12. mysql>showwarnings;
13. +---------+------+------------------------------------------------------+
14. |Level|Code|Message|
15. +---------+------+------------------------------------------------------+
16. |Warning|1264|Outofrangevalueadjustedforcolumn'dt'atrow1|
17. +---------+------+------------------------------------------------------+
18. 1rowinset(0.00sec)
19. mysql>select*fromj1_dt;
20. +---------------------+
21. |dt|
22. +---------------------+
23. |1970-01-0108:00:01|
24. |0000-00-0000:00:00|
25. +---------------------+
26. 2rowsinset(0.00sec)

触发的警告信息在MySQL层面仅是个警告而并非错误,前端应用的try catch捕获不到,不过，由于实际写入的数据并非期望值,还是有可能埋下一些隐患,这些隐患一旦显露,就有可能触发前端应用出现异常.

对于timestamp类型,在实际应用中务必理解时区的概念,在设置timestamp列默认值，及实际赋值时务必明确写入的值实际保存时的状态,尽量避免埋入隐患,对于现有已经出错的记录,可以考虑通过批量UPDATE及修改表结构的方式予以处理.

关于 timestamp最小值与最大值一些测试例子,代码如下:

1. -mysqltimestamp最小值
2. CREATETABLE`test`(
3. `ID`int(11)NOTNULLDEFAULT'0',
4. `NAME`varchar(20)DEFAULTNULL,
5. `hiredate`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
6. PRIMARYKEY(`ID`)
7. )ENGINE=InnoDBDEFAULTCHARSET=gbk;
9. insertintotestvalues(1,'zjadolf','1970-01-0108:01:00')--插入成功
10. CategoryTimestampDurationMessageLinePosition
11. Statement2010/9/2714:57:430:00:00.0031rowsaffected20
13. insertintotestvalues(2,'zjadolf','1970-01-0108:00:00')--
14. CategoryTimestampDurationMessageLinePosition
15. Error2010/9/2714:58:350:00:00.000MySQLDatabaseError:Incorrectdatetimevalue:'1970-01-0108:00:00'forcolumn'hiredate'atrow120
17. insertintotestvalues(3,'zjadolf','1970-01-0108:00:30')--ok可以插入
18. CategoryTimestampDurationMessageLinePosition
19. Statement2010/9/2715:04:480:00:00.0071rowsaffected20
21. insertintotestvalues(1,'zjadolf','1970-01-0108:00:01')--ok可以插入
22. CategoryTimestampDurationMessageLinePosition
23. Statement2010/9/2715:06:390:00:00.0061rowsaffected20
25. insertintotestvalues(1,'zjadolf','1970-01-0108:00:00')--不可以插入报错
26. CategoryTimestampDurationMessageLinePosition
27. Error2010/9/2715:07:130:00:00.000MySQLDatabaseError:Incorrectdatetimevalue:'1970-01-0108:00:00'forcolumn'hiredate'atrow120

看样子1970-01-01 08:00:01应该是mysql中timestamp允许的最小值,大家使用时应该注意下,验证下我们插入如下记录应该报错,代码如下:

insert into test values(1,'zjadolf','1969-01-01 08:01:02')

Category Timestamp Duration Message Line Position

Error 2010/9/27 15:09:29 0:00:00.000 MySQL Database Error: Incorrect datetime value: '1969-01-01 08:01:02' for column 'hiredate' at row 1 2 0

--果然报错,难怪我在测试使用toad for mysql import tool导入1000000数据导到 日期为：'1970-01-01 07:57:09' 这条数据的时候报错呢！结果小于这个日期的记录都导不进去,悲剧啊.

查看官方解释文档如下:TIMESTAMP值不能早于1970或晚于2037。

当你需要同时包含日期和时间信息的值时则使用DATETIME类型,MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值,支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59',“支持”表示尽管先前的值可能工作,但没有保证.

当你只需要日期值而不需要时间部分时应使用DATE类型,MySQL用'YYYY-MM-DD'格式检索和显示DATE值,支持的范围是'1000-01-01'到 '9999-12-31'.

TIMESTAMP列类型的属性不固定,取决于MySQL版本和服务器运行的SQL模式,这些属性将在本节后面描述,看样子如果要支持较长时间的日期,那就需要使用datetime类型,代码如下:

1. insertintot4values(1,'1900-01-0108:01:02')
2. CategoryTimestampDurationMessageLinePosition
3. Statement2010/9/2715:21:530:00:00.0511rowsaffected150

这样看来mysql 的timestamp 和db2 的timestamp还是不一样的,db2 的timestamp 类型没有mysql 这样的限制.