MySQL - 3 - 支持的数据类型

MySQL支持的数据类型

参考资料

《深入浅出MySQL全文》

仅做学习记录，不深究。

数值类型

MySQL支持所有标准SQL中的数值类型，其中包括严格数值类型和近似数值数据类型

严格数值类型：integer、smallint、decimal、numeric

近似数值数据类型：float、real、double precision

MySQL 中的数值类型

整数类型	字节	最小值	最大值
TINYINT	1	有符号-128 无符号 0	有符号 127 无符号 255
SMALLINT	2	有符号-32768 无符号 0	有符号 32767 无符号 65535
MEDIUMINT	3	有符号-8388608 无符号 0	有符号8388607 无符号1677215
INT、INTEGER	4	有符号-2147483648 无符号 0	有符号 2147483647 无符号 4294967295
BIGINT	8	有符号-9223372036854775808 无符号 0	有符号 9223372036854775807 无符号 18446744073709551615
浮点数类型	字节	最小值	最大值
FLOAT	4	±1.175494351E-38	±3.402823466E+38
DOUBLE	8	±2.2250738585072014E-308	±1.7976931348623157E+308
定点数类型	字节	描述
DEC(M,D)， DECIMAL(M,D)	M+2	最大取值范围与 DOUBLE 相同， 给定 DECIMAL 的有效取值范围由 M 和 D决定
位类型	字节	最小值	最大值
BIT(M)	1～8	BIT(1)	BIT(64)

紫色标出来的，是常用的数值类型。

日期时间类型

MySQL中有很多中数据类型可以用于日期和时间的表示，不同版本可能有所差异。

MySQL 中的日期和时间类型

日期和时间类型	字节	最小值	最大值
DATE	4	1000-01-01	9999-12-31
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	2038 年的某个时刻
TIME	3	-838:59:59	838:59:59
YEAR	1	1901	2155

这些数据类型的主要区别如下：

• 如果要用来表示年月日，通常用 DATE 来表示。

• 如果要用来表示年月日时分秒，通常用 DATETIME 表示。

• 如果只用来表示时分秒，通常用 TIME 来表示。

• 如果需要经常插入或者更新日期为当前系统时间，则通常使用 TIMESTAMP 来表示。TIMESTAMP 值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为 19 个字符。如果想要获得数字值，应在 TIMESTAMP 列添加+0。

• 如果只是表示年份，可以用 YEAR 来表示，它比 DATE 占用更少的空间。YEAR 有 2 位或4 位格式的年。默认是 4 位格式。在 4 位格式中，允许的值是 1901～2155 和 0000。在2 位格式中， 允许的值是 70～69， 表示从 1970～2069 年。 MySQL 以 YYYY 格式显示 YEAR值。 

从上面可以看出，每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的 SQLMode下，系统会进行错误提示，并将以零值来进行存储。不同日期类型零值的表示如下面所示

MySQL 中日期和时间类型的零值表示

数据类型	零值表示
DATETIME	0000-00-00 00:00:00
DATE	0000-00-00
TIMESTAMP	00000000000000
TIME	00:00:00
YEAR	0000

可以用now()函数插入当前日期

mysql> insert emp values ('cyx',now(),6000,5);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
| cyx    | 2017-11-28 | 6000.00 |      5 |
+--------+------------+---------+--------+
6 rows in set (0.02 sec)

Date、Time、DATETIME是最常用的日期类型，我们看看下面的例子：

mysql> create table t(
    -> d date,
    -> t time,
    -> dt datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-01-05 | 03:40:29 | 2018-01-05 03:40:29 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

看结果，显而易见，DATETIME是DATE和TIME的组合。

字符串类型

MySQL中提供了多种对字符串数据的存储类型，不同版本可能有所差异。

MySQL 中的字符类型

字符串类型	字节	描述及存储需求
CHAR（M）	M	M 为 0～255 之间的整数
VARCHAR（M）	M 为 0～65535 之间的整数，值的长度+1 个字节
TINYBLOB	允许长度 0～255 字节，值的长度+1 个字节
BLOB	允许长度 0～65535 字节，值的长度+2 个字节
MEDIUMBLOB	允许长度 0～167772150 字节，值的长度+3 个字节
LONGBLOB	允许长度 0～4294967295 字节，值的长度+4 个字节
TINYTEXT	允许长度 0～255 字节，值的长度+2 个字节
TEXT	允许长度 0～65535 字节，值的长度+2 个字节
MEDIUMTEXT	允许长度 0～167772150 字节，值的长度+3 个字节
LONGTEXT	允许长度 0～4294967295 字节，值的长度+4 个字节
VARBINARY（M）	允许长度 0～M 个字节的变长字节字符串，值的长度+1 个字节
BINARY（M）	M	允许长度 0～M 个字节的定长字节字符串

上面标注出来的是常用的。

char 和 varchar 类型

char和varchar很相似，都用来保存MySQL中较短的字符串。

二者的主要区别在于存储方式的不同：

char列的长度固定为创建表时声明的长度，长度可以从0~255的任何值。

varchar列中的值为可变长字符串，长度可以指定为0~255或65535之间的值。

在检索的时候，char列删除了尾部的空格，而varchar则保留这些空格。