TIME类型 占用字节3
HH:MM:SS [D HH:MM:SS] D表示天数,范围是 0~34
或者: HHMMSS这种形式
mysql> SELECT * FROM test_time;
+----------+
| a |
+----------+
| 12:23:34 |
+----------+
1 row in set (0.00 sec)
mysql> INSERT test_time(a) VALUES('2 12:23:34');
Query OK, 1 row affected (0.11 sec)
mysql> SELECT * FROM test_time;
+----------+
| a |
+----------+
| 12:23:34 |
| 60:23:34 | ## 这个 60 = 2天 + 12 = 2 * 24 + 12
+----------+
2 rows in set (0.00 sec)
mysql> INSERT test_time(a) VALUES('12:23:34');
Query OK, 1 row affected (0.07 sec)
mysql> INSERT test_time(a) VALUES('2 12:23:34');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT test_time(a) VALUES('121212');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT test_time(a) VALUES('0');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT test_time(a) VALUES(0);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT test_time(a) VALUES(7878788);
ERROR 1292 (22007): Incorrect time value: '7878788' for column 'a' at row 1
mysql> INSERT test_time(a) VALUES(NOW());
Query OK, 1 row affected (0.10 sec)
mysql> INSERT test_time(a) VALUES(CURRENT_TIME);
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM test_time;
+----------+
| a |
+----------+
| 12:23:34 |
| 60:23:34 |
| 12:23:34 |
| 60:23:34 |
| 12:12:12 |
| 00:00:00 |
| 00:00:00 |
| 13:36:57 |
| 13:37:03 |
+----------+
9 rows in set (0.00 sec)
DATE类型 占用字节3
格式:YYYYMMDD 或 YYYY-MM-DD
mysql> CREATE TABLE test_date(
-> a DATE
-> );
Query OK, 0 rows affected (0.73 sec)
mysql> INSERT test_date(a) VALUES('2020-07-03');
Query OK, 1 row affected (0.19 sec)
mysql> SELECT *FROM test_date;
+------------+
| a |
+------------+
| 2020-07-03 |
+------------+
1 row in set (0.03 sec)
mysql> INSERT test_date(a) VALUES('20200816');
Query OK, 1 row affected (0.07 sec)
mysql> SELECT *FROM test_date;
+------------+
| a |
+------------+
| 2020-07-03 |
| 2020-08-16 |
+------------+
2 rows in set (0.00 sec)
还可以指定任意的分割符号
mysql> INSERT test_date(a) VALUES('4007#09#2');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT test_date(a) VALUES('4007@1@1');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT test_date(a) VALUES('4009.8.14');
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM test_date;
+------------+
| a |
+------------+
| 2020-07-03 |
| 2020-08-16 |
| 4007-09-02 |
| 4007-01-01 |
| 4009-08-14 |
+------------+
5 rows in set (0.00 sec)
两位的年份
格式:YYMMDD 或者YY-MM-DD
如果前两位输入的是70-99,则自动填充为1970-1900
如果前两位是00-69,则自动填充为2000-2069
mysql> INSERT test_date(a) VALUES('70-07-03');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT test_date(a) VALUES('21-07-03');
Query OK, 1 row affected (0.17 sec)
mysql> SELECT * FROM test_date;
+------------+
| a |
+------------+
| 2020-07-03 |
| 2020-08-16 |
| 4007-09-02 |
| 4007-01-01 |
| 4009-08-14 |
| 1970-07-03 |
| 2021-07-03 |
+------------+
7 rows in set (0.00 sec)
mysql> INSERT test_date(a) VALUES(CURRENTTIME());
ERROR 1305 (42000): FUNCTION imooc_user.CURRENTTIME does not exist
mysql> INSERT test_date(a) VALUES(CURRENT_DATE());
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM test_date;
+------------+
| a |
+------------+
| 2020-07-03 |
| 2020-08-16 |
| 4007-09-02 |
| 4007-01-01 |
| 4009-08-14 |
| 1970-07-03 |
| 2021-07-03 |
| 2020-11-14 |
| 2020-11-14 |
+------------+
9 rows in set (0.00 sec)
DATETIME类型 占用字节8
DATETIME类型同date
mysql> INSERT test_datetime(a) VALUES('1007-09-12 12:12:12');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT test_datetime(a) VALUES('740305121212');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM test_datetime;
+---------------------+
| a |
+---------------------+
| 1007-09-12 12:12:12 |
| 1974-03-05 12:12:12 |
+---------------------+
2 rows in set (0.00 sec)
mysql> INSERT test_datetime(a) VALUES(NOW());
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM test_datetime;
+---------------------+
| a |
+---------------------+
| 1007-09-12 12:12:12 |
| 1974-03-05 12:12:12 |
| 2020-11-14 16:32:26 |
+---------------------+
3 rows in set (0.00 sec)
TIMESTAMP类型 占用字节4
mysql> CREATE TABLE test_timestamp(
-> a TIMESTAMP
-> );
Query OK, 0 rows affected (0.51 sec)
mysql> INSERT test_timestamp(a) VALUES('1978-10-24 12:13:14');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM test_timestamp;
+---------------------+
| a |
+---------------------+
| 1978-10-24 12:13:14 |
+---------------------+
1 row in set (0.00 sec)
插入CURRENT_TIMESTAMP
mysql> INSERT test_timestamp(a) VALUES(CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.16 sec)
mysql> SELECT * FROM test_timestamp;
+---------------------+
| a |
+---------------------+
| 1978-10-24 12:13:14 |
| 2020-11-14 16:48:32 |
+---------------------+
2 rows in set (0.01 sec)
插入null
mysql> INSERT test_timestamp(a) VALUES(NULL);
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM test_timestamp;
+---------------------+
| a |
+---------------------+
| 1978-10-24 12:13:14 |
| 2020-11-14 16:48:32 |
| NULL |
+---------------------+
3 rows in set (0.00 sec)
什么都不写
mysql> INSERT test_timestamp VALUES();
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM test_timestamp;
+---------------------+
| a |
+---------------------+
| 1978-10-24 12:13:14 |
| 2020-11-14 16:48:32 |
| NULL |
| NULL |
+---------------------+
5 rows in set (0.00 sec)
YEAR类型 占用字节1
year类型的范围是1901-2155
mysql> CREATE TABLE test_year(
-> a YEAR
-> );
Query OK, 0 rows affected (1.22 sec)
mysql> INSERT test_year(a) VALUES('1900');
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> INSERT test_year(a) VALUES('2155');
Query OK, 1 row affected (0.03 sec)
mysql> INSERT test_year(a) VALUES('1901');
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM test_year;
+------+
| a |
+------+
| 2155 |
| 1901 |
+------+
2 rows in set (0.00 sec)
注意:0插入的结果是0000,
‘0’插入的结果是2000;
mysql> INSERT test_year(a) VALUES(0);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT test_year(a) VALUES('0');
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM test_year;
+------+
| a |
+------+
| 2155 |
| 1901 |
| 0000 |
| 2000 |
+------+
4 rows in set (0.01 sec)
在输入两位时,同样遵循规则:
00~69: 2000-2069
70~99:1970-1999