当前位置：Gxlcms >
数据库问题 >
mysql中的时间类型datetime,date,time,year,timestamp小知识点
mysql中的时间类型datetime,date,time,year,timestamp小知识点
时间：2021-07-01 10:21:17
帮助过：2人阅读
create table time_table(
-> dt
datetime,
-> d date,
-> t time,
-> y
year,
-> ts
timestamp
-> );
Query OK, 0 rows affected (
0.05 sec)
/*查看表结构*/
mysql> desc time_table;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field
| Type
| Null | Key | Default | Extra
|
+-------+-----------+------+-----+-------------------+-----------------------------+
| dt
| datetime | YES
| | NULL | |
| d
| date
| YES
| | NULL | |
| t
| time
| YES
| | NULL | |
| y
| year(
4)
| YES
| | NULL | |
| ts
| timestamp | NO
| | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows
in set (
0.01 sec)
/*手动插入数据，ts数据为null*/
mysql> insert into time_table(dt,d,t,y,ts)
values("
2016-6-22 11:
57:
30","
2016/6/22","
11:
57:
30","
2016",
null);
Query OK, 1 row affected (
0.01 sec)
/*手动插入数据，没有为ts提供数据*/
mysql> insert into time_table(dt,d,t,y)
values("
2016-6-22 11:
57:
30","
2016/6/22","
11:
57:
30","
2016");
Query OK, 1 row affected (
0.00 sec)
/*利用now()函数提供数据，为y字段手动输入数据，没有为ts提供数据*/
mysql> insert into time_table(dt,d,t,y)
values(now(),now(),now(),
‘2016‘);
Query OK, 1 row affected,
1 warning (
0.00 sec)
/*手动插入数据，日期时间分隔符更换为“/”，依然没有为ts提供数据*/
mysql> insert into time_table(dt,d,t,y)
values ("
2016/6/22 14/10/30","
2016/6/22","
14:
10:
30","
2016");
Query OK, 1 row affected (
0.00 sec)
/*查看数据*/
mysql> select * from time_table;
+---------------------+------------+----------+------+---------------------+
| dt
| d
| t
| y
| ts
|
+---------------------+------------+----------+------+---------------------+
| 2016-06-22 11:
57:
30 | 2016-06-22 | 11:
57:
30 | 2016 | 2016-06-22 11:
58:
00 |
| 2016-06-22 11:
57:
30 | 2016-06-22 | 11:
57:
30 | 2016 | 2016-06-22 11:
59:
05 |
| 2016-06-22 13:
58:
54 | 2016-06-22 | 13:
58:
54 | 2016 | 2016-06-22 13:
58:
54 |
| 2016-06-22 14:
10:
30 | 2016-06-22 | 14:
10:
30 | 2016 | 2016-06-22 14:
11:
58 |
+---------------------+------------+----------+------+---------------------+
4 rows
in set (
0.00 sec)
mysql中的时间类型datetime,date,time,year,timestamp小知识点
标签：
