datetime类型说明
类 型:datetime 显示效果:年-月-日 时:分:秒 有效范围:1000-01-01 00:00:00 至9999-12-31 23:59:59 插入方式: insert into test1(mydate) values(20190601120030); insert into test1(mydate) values('2019-06-01 12:00:30'); insert into test1(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)
通过案例理解
-- 创建test1表,字符集为utf8,存储引擎为innodb,mydate字段的类型为datetime mysql> create table if not exists test1( -> mydate datetime -> )engine=innodb character set utf8 collate utf8_general_ci; Query OK, 0 rows affected (0.01 sec) -- 查看test1表的表结构 mysql> desc test1; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | mydate | datetime | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 1 row in set (0.00 sec) -- 第一种插入数据的方式【不推荐使用】 mysql> insert into test1(mydate) values(20190601120030); Query OK, 1 row affected (0.01 sec) mysql> select * from test1; +---------------------+ | mydate | +---------------------+ | 2019-06-01 12:00:30 | +---------------------+ 1 row in set (0.01 sec) -- 第二种插入数据的方式【推荐使用这种方式】 mysql> insert into test1(mydate) values("2019-06-02 12:00:30"); Query OK, 1 row affected (0.00 sec) mysql> select * from test1; +---------------------+ | mydate | +---------------------+ | 2019-06-01 12:00:30 | | 2019-06-02 12:00:30 | +---------------------+ 2 rows in set (0.00 sec) -- 第三种插入数据的方式【推荐使用这种方式】 mysql> insert into test1(mydate) values(now()); Query OK, 1 row affected (0.00 sec) mysql> select * from test1; +---------------------+ | mydate | +---------------------+ | 2019-06-01 12:00:30 | | 2019-06-02 12:00:30 | | 2019-06-15 09:48:37 | +---------------------+ 3 rows in set (0.00 sec)