mysql数据类型--[日期类型]--timestamp类型

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)

转载于:https://www.cnblogs.com/chenliangc/articles/11107418.html