mysql 创建日期列之timestamp

原创

暗黑魔君 2013-05-20 14:50:05 ©著作权

文章标签 mysql 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者暗黑魔君的原创作品，请联系作者获取转载授权，否则将追究法律责任

mysql 有很多日期格式

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)

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