Mysql数据类型介绍

#写的不好轻喷

整数类型

• Mysql中提供多种对于数值的数据类型
• 不同的数据类型取值范围不同
• 取值范围越大，需要的存储空间也越大

日期和时间类型

• Mysql中提供多种用于存储日期和时间的类型
• 根据需要选择，并注意格式

YEAR(M) 

M可以选择2或4 分别对用YY和YYYY 。 如果是2位数的年份，存储范围为1970-2069，如果是4位数的年份，存储范围为1901-2155？ 做实验都为1901-2155

mysql> CREATE TABLE date1 (d1 year(4) DEFAULT NULL,d2 year(4) DEFAULT NULL );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into date1 values(0,0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into date1 values('0','0');当用单引号将数字括起来，Mysql会自动把数字当做年份最右边的数字
Query OK, 1 row affected (0.00 sec)

mysql> select * from date1;
+------+------+
| d1   | d2   |
+------+------+
| 0000 | 0000 |
| 2000 | 2000 |
+------+------+
2 rows in set (0.00 sec)

TIME

可以理解为过了多少时间

mysql> insert into time1 values(22:39:40);

mysql> insert into time1 values('22:39:40');
Query OK, 1 row affected (0.00 sec)

mysql> insert into time1 values('839:39:40');
ERROR 1292 (22007): Incorrect time value: '839:39:40' for column 't1' at row 1
mysql> select * from time1;
+----------+
| t1       |
+----------+
| 22:39:40 |
+----------+
1 row in set (0.00 sec)

mysql> insert into time1 values(1122); 过了11分22秒，Mysql认为最右边的数字是秒
Query OK, 1 row affected (0.00 sec)

mysql> select * from time1;
+----------+
| t1       |
+----------+
| 22:39:40 |
| 00:11:22 |
+----------+
2 rows in set (0.00 sec)

mysql> insert into time1 values('2 11:22');
Query OK, 1 row affected (0.01 sec)

mysql> select * from time1;
+----------+
| t1       |
+----------+
| 22:39:40 |
| 00:11:22 |
| 59:22:00 |
+----------+
3 rows in set (0.00 sec)

两天 48小时 + 11小时 22分钟

mysql> insert into time1 values('2 10');
Query OK, 1 row affected (0.00 sec)

mysql> select * from time1;
+----------+
| t1       |
+----------+
| 58:00:00 |
+----------+
1 row in set (0.00 sec)

两个小时 + 十小时

mysql> insert into time1 values('2210');
Query OK, 1 row affected (0.00 sec)

mysql> insert into time1 values('10');
Query OK, 1 row affected (0.01 sec)

mysql> select * from time1;
+----------+
| t1       |
+----------+
| 58:00:00 |
| 00:22:10 |
| 00:00:10 |
+----------+
3 rows in set (0.00 sec)

DATE

mysql> create table date2(col1 date);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into date2 values('2014-11-28');
Query OK, 1 row affected (0.01 sec)

mysql> select * from date2;
+------------+
| col1       |
+------------+
| 2014-11-28 |
+------------+
1 row in set (0.00 sec)

mysql> insert into date2 values(2014-11-28);
ERROR 1292 (22007): Incorrect date value: '1975' for column 'col1' at row 1

mysql> insert into date2 values('14-11-28');
Query OK, 1 row affected (0.02 sec)

mysql> select * from date2;
+------------+
| col1       |
+------------+
| 2014-11-28 |
| 2014-11-28 |
+------------+
2 rows in set (0.00 sec)

DATETIME

mysql> create table date3 (col1 datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into date3 values('2014-11-28');
Query OK, 1 row affected (0.00 sec)

mysql> insert into date3 values('2014-11-28 23:23:30');
Query OK, 1 row affected (0.01 sec)

mysql> select * from date3;
+---------------------+
| col1                |
+---------------------+
| 2014-11-28 00:00:00 |
| 2014-11-28 23:23:30 |
+---------------------+
2 rows in set (0.00 sec)

mysql> insert into date3 values('50-11-28 23:23:30');
Query OK, 1 row affected (0.01 sec)

mysql> select * from date3;
+---------------------+
| col1                |
+---------------------+
| 2014-11-28 00:00:00 |
| 2014-11-28 23:23:30 |
| 2050-11-28 23:23:30 |
+---------------------+
3 rows in set (0.00 sec)

mysql> insert into date3 values('50~11*28 23:23:30');
Query OK, 1 row affected (0.01 sec)

mysql> select * from date3;
+---------------------+
| col1                |
+---------------------+
| 2014-11-28 00:00:00 |
| 2014-11-28 23:23:30 |
| 2050-11-28 23:23:30 |
| 2050-11-28 23:23:30 |
+---------------------+
4 rows in set (0.00 sec)

TIMESTAMP

受系统时区影响

mysql> create table t_tt ( col1 timestamp);
Query OK, 0 rows affected (0.12 sec)

mysql> desc t_tt;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| col1  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> insert into t_tt values(now());      now()当前时间
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_tt;
+---------------------+
| col1                |
+---------------------+
| 2014-11-24 08:15:42 |
+---------------------+
1 row in set (0.00 sec)

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.02 sec)

mysql> set time_zone='+10:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +10:00 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> select * from t_tt;
+---------------------+
| col1                |
+---------------------+
| 2014-11-24 10:15:42 |    注意！更改系统时区（不是操作系统而是Mysql）以后，表中时间也改变了
+---------------------+
1 row in set (0.00 sec)