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