mysql中的时间year/date/time/datetime

year:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

mysql> create table y(y year); Query OK, 0 rows affected (0.03 sec)   mysql> desc y; +-------+---------+------+-----+---------+-------+ | Field | Type    | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | y     | year(4) | YES  |     | NULL    |       | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)     mysql> insert into y values(1987); Query OK, 1 row affected (0.00 sec)   mysql> select * from y; +------+ | y    | +------+ | 0000 | | 0000 | | 1987 | +------+ 3 rows in set (0.00 sec)   mysql> insert into y values(19874); Query OK, 1 row affected, 1 warning (0.00 sec)   mysql> select * from y; +------+ | y    | +------+ | 0000 | | 0000 | | 1987 | | 0000 | +------+ 4 rows in set (0.00 sec)

 

 

 date:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

mysql> create table d(d date); Query OK, 0 rows affected (0.02 sec)   mysql> insert into d(1); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual th corresponds to your MariaDB server version for the right syntax to use near '  at line 1 mysql> insert into d values(1); Query OK, 1 row affected, 1 warning (0.00 sec)   mysql> select * from d; +------------+ | d          | +------------+ | 0000-00-00 | +------------+ 1 row in set (0.00 sec)   mysql> insert into d values(10-15); Query OK, 1 row affected, 1 warning (0.01 sec)   mysql> select * from d; +------------+ | d          | +------------+ | 0000-00-00 | | 0000-00-00 | +------------+ 2 rows in set (0.00 sec)   mysql> insert into d values(2011-7-7); Query OK, 1 row affected, 1 warning (0.00 sec)   mysql> select * from d; +------------+ | d          | +------------+ | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | +------------+ 3 rows in set (0.00 sec)   mysql> insert into d values(2017-10-15); Query OK, 1 row affected, 1 warning (0.00 sec)   mysql> select * from d; +------------+ | d          | +------------+ | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | +------------+ 4 rows in set (0.00 sec)   mysql> insert into d values('2011-7-7'); Query OK, 1 row affected (0.00 sec)   mysql> select * from d; +------------+ | d          | +------------+ | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 2011-07-07 | +------------+ 5 rows in set (0.00 sec)   mysql>

 要用字符串型式插入

 

 

time:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

mysql> create table t(t time); Query OK, 0 rows affected (0.03 sec)   mysql> insert into t values(12345); Query OK, 1 row affected (0.00 sec)   mysql> select * from t; +----------+ | t        | +----------+ | 01:23:45 | +----------+ 1 row in set (0.00 sec)   mysql> insert into t values(1234511111); Query OK, 1 row affected, 1 warning (0.00 sec)   mysql> select * from t; +----------+ | t        | +----------+ | 01:23:45 | | 00:00:00 | +----------+ 2 rows in set (0.00 sec)   mysql> insert into t values('09:09:09'); Query OK, 1 row affected (0.00 sec)   mysql> select * from t; +----------+ | t        | +----------+ | 01:23:45 | | 00:00:00 | | 09:09:09 | +----------+ 3 rows in set (0.00 sec)   mysql>

 

datetime:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

mysql> create table dt(dt datetime); Query OK, 0 rows affected (0.03 sec)   mysql> insert into dt values('2017-01-01 0:0:01'); Query OK, 1 row affected (0.00 sec)   mysql> select * from dt; +---------------------+ | dt                  | +---------------------+ | 2017-01-01 00:00:01 | +---------------------+ 1 row in set (0.00 sec)   mysql> insert into dt values('1234567890'); Query OK, 1 row affected, 1 warning (0.00 sec)   mysql> select * from dt; +---------------------+ | dt                  | +---------------------+ | 2017-01-01 00:00:01 | | 0000-00-00 00:00:00 | +---------------------+ 2 rows in set (0.00 sec)   mysql>