MySQL datetime和timestamp的区别以及实例测试

datetime

1. 占用8个字节
2. 允许为空值，可以自定义值，系统不会自动修改其值。
3. 实际格式储存（Just stores what you have stored and retrieves the same thing which you have stored.）
4. 与时区无关（It has nothing to deal with the TIMEZONE and Conversion.）
5. 不可以设定默认值，所以在不允许为空值的情况下，必须手动指定datetime字段的值才可以成功插入数据。
6. 可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。
结论：datetime类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime字段的值都不会改变，除非你手动更改它。
例子：
mysql> create table `datetime` (
-> id int(11) not null auto_increment,
-> `create_ts` datetime default null comment 'test datetime',
-> primary key (`id`)
-> ) Engine=InnoDB default charset=utf8 auto_increment=1;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into datetime values(null,now());
Query OK, 1 row affected (0.11 sec)

mysql> select * from datetime;
+----+---------------------+
| id | create_ts |
+----+---------------------+
| 1 | 2014-11-13 16:09:43 |
+----+---------------------+
1 row in set (0.07 sec)

mysql> insert into datetime values(null,null);
Query OK, 1 row affected (0.11 sec)

mysql> select * from datetime;
+----+---------------------+
| id | create_ts |
+----+---------------------+
| 1 | 2014-11-13 16:09:43 |
| 2 | NULL |
+----+---------------------+
2 rows in set (0.01 sec)

mysql> insert into datetime values(null,now());
Query OK, 1 row affected (0.08 sec)

mysql> select * from datetime;
+----+---------------------+
| id | create_ts |
+----+---------------------+
| 1 | 2014-11-13 16:09:43 |
| 2 | NULL |
| 3 | 2014-11-13 16:10:15 |
+----+---------------------+
3 rows in set (0.06 sec)

mysql> alter table datetime add `info` varchar(200) default ' ' comment 'info_datetime';
Query OK, 3 rows affected (0.20 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into datetime values(null,now(),'test1');
Query OK, 1 row affected (0.06 sec)

mysql> insert into datetime values(null,now(),'test2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into datetime values(null,now(),'test3');
Query OK, 1 row affected (0.12 sec)

mysql> select * from datetime;
+----+---------------------+-------+
| id | create_ts | info |
+----+---------------------+-------+
| 1 | 2014-11-13 16:09:43 | |
| 2 | NULL | |
| 3 | 2014-11-13 16:10:15 | |
| 4 | 2014-11-13 16:13:50 | test1 |
| 5 | 2014-11-13 16:13:53 | test2 |
| 6 | 2014-11-13 16:13:57 | test3 |
+----+---------------------+-------+
6 rows in set (0.07 sec)

mysql> update datetime set info='change' where id = 4;
Query OK, 1 row affected (0.23 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from datetime;
+----+---------------------+--------+
| id | create_ts | info |
+----+---------------------+--------+
| 1 | 2014-11-13 16:09:43 | |
| 2 | NULL | |
| 3 | 2014-11-13 16:10:15 | |
| 4 | 2014-11-13 16:13:50 | change |
| 5 | 2014-11-13 16:13:53 | test2 |
| 6 | 2014-11-13 16:13:57 | test3 |
+----+---------------------+--------+
6 rows in set (0.07 sec)

timestamp
1. 占用4个字节
2. 允许为空值，但是不可以自定义值，所以为空值时没有任何意义。
3. TIMESTAMP值不能早于1970或晚于2037。这说明一个日期，例如'1968-01-01'，虽然对于DATETIME或DATE值是有效的，但对于TIMESTAMP值却无效，如果分配给这样一个对象将被转换为0。
4.值以UTC格式保存（ it stores the number of milliseconds）
5.时区转化，存储时对当前的时区进行转换，检索时再转换回当前的时区。
6. 默认值为CURRENT_TIMESTAMP()，其实也就是当前的系统时间。
7. 数据库会自动修改其值，所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值，你只需要在设计表的时候添加一个timestamp字段即可，插入后该字段的值会自动变为当前系统时间。
8. 以后任何时间修改表中的记录时，对应记录的timestamp值会自动被更新为当前的系统时间。
结论：timestamp类型适合用来记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp字段的值都会被自动更新。

例子如下：
mysql> create table `timestamp` ( id int(11) not null auto_increment, update_ts timestamp ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, primary key (`id`) ) Engine=InnoDB CHARSET=UTF8 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from timestamp;
Empty set (0.12 sec)

mysql> insert into timestamp values(null,CURRENT_TIMESTAMP());
Query OK, 1 row affected (0.10 sec)

mysql> select * from timestamp;
+----+---------------------+
| id | update_ts |
+----+---------------------+
| 1 | 2014-11-13 16:32:38 |
+----+---------------------+
1 row in set (0.03 sec)

mysql> select * from timestamp;
+----+---------------------+
| id | update_ts |
+----+---------------------+
| 1 | 2014-11-13 16:32:38 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> insert into timestamp values(null,CURRENT_TIMESTAMP());
Query OK, 1 row affected (0.09 sec)

mysql> insert into timestamp values(null,CURRENT_TIMESTAMP());
Query OK, 1 row affected (0.02 sec)

mysql> select * from timestamp;

+----+---------------------+
| id | update_ts |
+----+---------------------+
| 1 | 2014-11-13 16:32:38 |
| 2 | 2014-11-13 16:32:45 |
| 3 | 2014-11-13 16:32:46 |
| 4 | 2014-11-13 16:32:47 |
+----+---------------------+
4 rows in set (0.07 sec)

mysql> alter table timestamp add info varchar(100) not null default '';
Query OK, 4 rows affected (0.15 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from timestamp;
+----+---------------------+------+
| id | update_ts | info |
+----+---------------------+------+
| 1 | 2014-11-13 16:32:38 | |
| 2 | 2014-11-13 16:32:45 | |
| 3 | 2014-11-13 16:32:46 | |
| 4 | 2014-11-13 16:32:47 | |
+----+---------------------+------+
4 rows in set (0.00 sec)

mysql> insert into timestamp values(null,CURRENT_TIMESTAMP(),"test1");
Query OK, 1 row affected (0.09 sec)

mysql> insert into timestamp values(null,CURRENT_TIMESTAMP(),"test1=2");
Query OK, 1 row affected (0.01 sec)

mysql> insert into timestamp values(null,CURRENT_TIMESTAMP(),"test3");
Query OK, 1 row affected (0.11 sec)

mysql> insert into timestamp values(null,CURRENT_TIMESTAMP(),"test4");
Query OK, 1 row affected (0.01 sec)

mysql> insert into timestamp values(null,CURRENT_TIMESTAMP(),"test5");
Query OK, 1 row affected (0.09 sec)

mysql> select * from timestamp;
+----+---------------------+---------+
| id | update_ts | info |
+----+---------------------+---------+
| 1 | 2014-11-13 16:32:38 | |
| 2 | 2014-11-13 16:32:45 | |
| 3 | 2014-11-13 16:32:46 | |
| 4 | 2014-11-13 16:32:47 | |
| 5 | 2014-11-13 16:35:06 | test1 |
| 6 | 2014-11-13 16:35:08 | test2 |
| 7 | 2014-11-13 16:35:12 | test3 |
| 8 | 2014-11-13 16:35:14 | test4 |
| 9 | 2014-11-13 16:35:32 | test5 |
+----+---------------------+---------+
9 rows in set (0.05 sec)

mysql> update timestamp set info="v1" where id = 7;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from timestamp;
+----+---------------------+---------+
| id | update_ts | info |
+----+---------------------+---------+
| 1 | 2014-11-13 16:32:38 | |
| 2 | 2014-11-13 16:32:45 | |
| 3 | 2014-11-13 16:32:46 | |
| 4 | 2014-11-13 16:32:47 | |
| 5 | 2014-11-13 16:35:06 | test1 |
| 6 | 2014-11-13 16:35:08 | test2 |
| 7 | 2014-11-13 16:36:29 | v1 | #记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp字段的值都会被自动更新。
| 8 | 2014-11-13 16:35:14 | test4 |
| 9 | 2014-11-13 16:35:32 | test5 |
+----+---------------------+---------+
9 rows in set (0.01 sec)

posted @ 2014-11-13 17:36 study-notes 阅读(498) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

study notes

MySQL datetime和timestamp的区别以及实例测试

公告