mysql 有很多日期格式

144125271.jpg

这里仅说明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: t1Create Table: CREATE TABLE `t1` (  `id` int(11) DEFAULT NULL,  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf81 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: t2Create Table: CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL,  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf81 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: 0mysql> 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: 0mysql> 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: 0mysql> 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)

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