mysql 创建日期列之timestamp
mysql 有很多日期格式
这里仅说明TIMESTAMP的应用
1.插入默认时间：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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的同时更新时间
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
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，如需转载请自行联系原作者