timestamp类型说明
类 型:timestamp
显示效果:年-月-日 时:分:秒
有效范围:1970-01-01 00:00:00 至 2038
插入方式:
insert into test2(mydate) values(20190601120030);
insert into test2(mydate) values('2019-06-01 12:00:30');
insert into test2(mydate) values(now());
实践环境准备
-- 创建chenliang库,字符集为utf8
mysql> create database if not exists chenliang character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> show databases like "chenliang";
+----------------------+
| Database (chenliang) |
+----------------------+
| chenliang |
+----------------------+
1 row in set (0.00 sec)
-- 进入chenliang库,并查看是否成功进入到了chenliang库
mysql> use chenliang;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| chenliang |
+------------+
1 row in set (0.00 sec)
通过案例理解
-- 创建test2表,字符集为utf8,存储引擎为innodb,mydate字段为timestamp
mysql> create table if not exists test2(
-> mydate timestamp
-> )engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.02 sec)
-- 查看test2表的表结构
mysql> desc test2;
+--------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------+------+-----+---------+-------+
| mydate | timestamp | YES | | NULL | |
+--------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
-- 第一种插入数据的方式【不建议使用】
mysql> insert into test2(mydate) values(20190601120030);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+---------------------+
| mydate |
+---------------------+
| 2019-06-01 12:00:30 |
+---------------------+
1 row in set (0.00 sec)
-- 第二种插入数据的方式【建议使用】
mysql> insert into test2(mydate) values("2019-06-02 12:00:30");
Query OK, 1 row affected (0.01 sec)
mysql> select * from test2;
+---------------------+
| mydate |
+---------------------+
| 2019-06-01 12:00:30 |
| 2019-06-02 12:00:30 |
+---------------------+
2 rows in set (0.00 sec)
-- 第三种插入数据的方式【建议使用】
mysql> insert into test2(mydate) values(now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+---------------------+
| mydate |
+---------------------+
| 2019-06-01 12:00:30 |
| 2019-06-02 12:00:30 |
| 2019-06-15 09:59:39 |
+---------------------+
3 rows in set (0.00 sec)