mysql 创建日期列之timestamp-白红宇

mysql 有很多日期格式

这里仅说明TIMESTAMP的应用

1.插入默认时间：

                            
mysql> CREATE TABLE t1(
id
INT,b TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.19 sec)
mysql> SHOW CREATE TABLE  t1\G
*************************** 1. row ***************************
       
Table: t1
Create Table: CREATE TABLE `t1` (
  
`
id
` int(11) DEFAULT NULL,
  
`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row 
in
set
(0.00 sec)
mysql> INSERT INTO t1(
id
) VALUES(1);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO t1(
id
) VALUES(2);
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM t1;
+------+---------------------+
| 
id
| b                   |
+------+---------------------+
|    1 | 2013-05-20 06:44:07 |
|    2 | 2013-05-20 06:44:08 |
+------+---------------------+
2 rows 
in
set
(0.00 sec)
         
       

2.更新id的同时更新时间

                            
mysql> CREATE TABLE t2(
id
INT,b TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)ENGINE=Innodb;
Query OK, 0 rows affected (0.19 sec)
mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       
Table: t2
Create Table: CREATE TABLE `t2` (
  
`
id
` int(11) DEFAULT NULL,
  
`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row 
in
set
(0.00 sec)
mysql> INSERT INTO t2(
id
) VALUES(1);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO t2(
id
) VALUES(2);
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM t2;
+------+---------------------+
| 
id
| b                   |
+------+---------------------+
|    1 | 2013-05-20 06:46:39 |
|    2 | 2013-05-20 06:46:43 |
+------+---------------------+
2 rows 
in
set
(0.00 sec)
mysql> UPDATE t2 SET 
id
=2 WHERE 
id
=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM t2;
+------+---------------------+
| 
id
| b                   |
+------+---------------------+
|    2 | 2013-05-20 06:47:15 |
|    2 | 2013-05-20 06:46:43 |
+------+---------------------+
2 rows 
in
set
(0.00 sec)
mysql> UPDATE t2 SET 
id
=3 WHERE b=
'2013-05-20 06:47:15'
;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM t2;
+------+---------------------+
| 
id
| b                   |
+------+---------------------+
|    3 | 2013-05-20 06:47:59 |
|    2 | 2013-05-20 06:46:43 |
+------+---------------------+
2 rows 
in
set
(0.00 sec)
mysql> UPDATE t2 SET 
id
=3 WHERE 
id
=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> SELECT * FROM t2;
+------+---------------------+
| 
id
| b                   |
+------+---------------------+
|    3 | 2013-05-20 06:47:59 |
|    2 | 2013-05-20 06:46:43 |
+------+---------------------+
2 rows 
in
set
(0.00 sec)
         
       

插入默认当前时间，当对表字段进行修改的时候，自动更新时间，如果表字段的值更新之前与更新之后没有变化，则时间也不会发生变更。

本文转自暗黑魔君 51CTO博客，原文链接：http://blog.51cto.com/clovemfong/1204794，如需转载请自行联系原作者