数据类型

数值类型

数值类型分 严格数值类型 和 近似数值类型。

• 严格数值类型
  • INTEGER
  • SMALLINT
  • DECIMAL
  • NUMBERIC
• 近似数值类型
  • FLOAT
  • REAL
  • DOUBLE PRECISION
• 扩展之后的数据类型
  • TINYINT
  • MEDIUMINT
  • BIGINT
  • BIT

整数

整数类型	字节	有符号	无符号
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 1677215
INT、INTEGER	4	-2147473648 ~ 2147483647	0 ~ 4294967295
BIGINT	8	-263 ~ 263 - 1	0 ~ 264

在建表时一般会在数据类型后面加上指定长度来表示字段数据类型许可的范围，如 INT(6) 表示 INT 类型的数据、最大长度为 6，不满 6 位会自动填充。若不指定长度，则默认为 INT(11)。

整型的字段可以使用 AUTO_INCREMENT 属性，但一个表中只能有一个 AUTO_INCREMENT 属性，一般用于自增主键。

小数

浮点数类型	字节	最小值	最大值
FLOAT	4	\(\pm\)1.175494351E-38	\(\pm\)3.402823466E+38
DOUBLE	8	\(\pm\)2.2250738585072014E-308	\(\pm\)1.7976931348623157E+308

定点数类型	字节	描述
DEC(M, D) 或 DECIMAL(M, D)	M+2	最大值取值范围与 DOUBLE 相同，给定DECIMAL 的有效取值范围由 M 和 D 决定

小数分 「浮点数」 和 「定点数」，浮点数分「单精度浮点型 Float」和「双精度浮点型」；定点数只有一种「decimal」，在 MySQL 内部中以字符串的形式存在，比浮点数更为精确，适合用来表示精度特别高的数据。

浮点数和定点数都可以使用 (M, D) 的方式来表示。M 表示 整数位+小数位 的位数，D 表示小数位数。M 称为精度，D 称为标度。

mysql> CREATE TABLE test1 (
    ->     aId FLOAT(6,2)   DEFAULT NULL,
    ->     bId DOUBLE(6,2)  DEFAULT NULL,
    ->     cId DECIMAL(6,2) DEFAULT NULL
    -> );
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> INSERT INTO test1 VALUES (1234.12,1234.12,1234.12);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test1;
+---------+---------+---------+
| aId     | bId     | cId     |
+---------+---------+---------+
| 1234.12 | 1234.12 | 1234.12 |
+---------+---------+---------+
1 row in set (0.00 sec)

超出精度的部分会被截断：

mysql> INSERT INTO test1 VALUES (1234.123,1234.123,1234.123);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM test1;
+---------+---------+---------+
| aId     | bId     | cId     |
+---------+---------+---------+
| 1234.12 | 1234.12 | 1234.12 |
| 1234.12 | 1234.12 | 1234.12 | -- 超出精度的部分会被截断
+---------+---------+---------+
3 rows in set (0.00 sec)

如不指定精度和标度，则浮点数会按照实际精度（不超过范围）存储，定点数会省略小数：

mysql> CREATE TABLE test2 (
    -> aId FLOAT   DEFAULT NULL,
    -> bId DOUBLE  DEFAULT NULL,
    -> cId DECIMAL DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test VALUES (12.34567, 12.34567, 12.34567);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM test;
+---------+----------+------+
| aId     | bId      | cId  |
+---------+----------+------+
| 12.3457 | 12.34567 |   12 |
+---------+----------+------+
2 rows in set (0.00 sec)

位类型

位类型	字节	最小值	最大值
BIT(M)	1~8	BIT(1)	BIT(64)

日期时间类型

类型	日期格式	日期范围	字节
YEAR	YYYY	1901 ~ 2155	1
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-30	3
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC	4

YEAR

year 有三种方式表示：

• 用 4 位的数字或者字符串表示，两者效果相同，表示范围 1901 - 2155，插入超出范围的数据会报错。
• 以 2 位**字符串**格式表示，范围为 00~99。
  • '00'~'69' 表示 2000~2069；
  • '70'~'99' 表示1970~1999；
  • '0' 和 '00' 都会被识别为 2000，超出范围的数据也会被识别为 2000。
• 以 2 位**数字**格式表示，范围为 1~99。
  • 1~69 表示 2001~2069；
  • 70~99 表示 1970~1999；
  • 但 0 值会被识别为0000，这和 2 位字符串被识别为 2000 有所不同

mysql> CREATE TABLE test3 (
    -> theYear YEAR
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test3 VALUES
    -> (1997),      -- 插入年份
    -> ('2001');    -- 插入年份
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test3;
+---------+
| theYear |
+---------+
|    1997 |
|    2001 |
+---------+
2 rows in set (0.00 sec)

插入 2 位字符串的栗子：

mysql> INSERT INTO test3 VALUES
    -> ('05'),
    -> ('69'),
    -> ('70'),
    -> ('99'),
    -> ('0'),
    -> ('00');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test3;
+---------+
| theYear |
+---------+
|    2005 |
|    2069 |
|    1970 |
|    1999 |
|    2000 |
|    2000 |
+---------+
8 rows in set (0.00 sec)

插入 2 位数字的栗子：

mysql> INSERT INTO test3 VALUES
    -> (05),
    -> (69),
    -> (70),
    -> (99),
    -> (0),
    -> (00);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test3;
+---------+
| theYear |
+---------+
|    2005 |
|    2069 |
|    1970 |
|    1999 |
|    2000 | -- 字符串的 00 是 2000 年
|    2000 |
|    2005 | --  ------插入的数据从这开始
|    2069 |
|    1970 |
|    1999 |
|    0000 | -- 数字的 00 是 0000 年
|    0000 |
+---------+
14 rows in set (0.00 sec)

TIME

类型	日期格式	日期范围	字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3

mysql> CREATE TABLE test4 (
    -> theTime TIME
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test4 VALUES
    -> ('18:05:06'),
    -> ('21:45'),
    -> ('2 12:49'),
    -> ('2 12:49:15'),
    -> ('2 05'),
    -> ('18');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test4;
+----------+
| theTime  |
+----------+
| 18:05:06 |    -- -> ('18:05:06'),   正常输入
| 21:45:00 |    -- -> ('21:45'),      表示 21点45分
| 60:49:00 |    -- -> ('2 12:49'),    表示 2天+12小时49分
| 60:49:15 |    -- -> ('2 12:49:15'), 表示 2天+12小时49分15秒
| 53:00:00 |    -- -> ('2 05'),       表示 2天+5小时
| 00:00:18 |    -- -> ('18'),         表示18秒
+----------+
6 rows in set (0.00 sec)

DATE

类型	日期格式	日期范围	字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-30	3

DATE 的表示一般很多种，如：

• 字符串形式
  • 'YYYY-MM-DD'
  • 'YYYYMMDD'
  • 'YY-MM-DD'
  • 'YYMMDD'
• 数字形式
  • YYYYMMDD
  • YYMMDD

mysql> CREATE TABLE test5 (
    -> theDate DATE
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test5 VALUES
    -> ('1949-10-01'),
    -> ('19970701'),
    -> ('00-01-01'),
    -> ('70-01-01'),
    -> (19491001),
    -> (000101),
    -> (700101);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test5;
+------------+
| theDate    |
+------------+
| 1949-10-01 |  -- -> ('1949-10-01'),
| 1997-07-01 |  -- -> ('19970701'),
| 2000-01-01 |  -- -> ('00-01-01'), 00~69 表示 2000 年 ~ 2069 年
| 1970-01-01 |  -- -> ('70-01-01'), 70~99 表示 1970 年 ~ 1999 年
| 1949-10-01 |  -- -> (19491001),
| 2000-01-01 |  -- -> (000101),     00~69 表示 2000 年 ~ 2069 年
| 1970-01-01 |  -- -> (700101);     70~99 表示 1970 年 ~ 1999 年
+------------+
7 rows in set (0.00 sec)

mysql> INSERT INTO test5 VALUES (9901011);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test5;
+------------+
| theDate    |
+------------+
| 1949-10-01 |
| 1997-07-01 |
| 2000-01-01 |
| 1970-01-01 |
| 1949-10-01 |
| 2000-01-01 |
| 1970-01-01 |
| 1999-01-01 |
| 0990-10-11 |  -- (9901011) 表示 990 年 10 月 11 r
+------------+
9 rows in set (0.00 sec)

DATETIME

类型	日期格式	日期范围	字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8

mysql> CREATE TABLE test6 (
    -> theDateTime DATETIME
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test6 VALUES
    -> ('1997-07-01 00:00:00'),
    -> (19970701000000),
    -> ('00-01-01 00:00:00'),
    -> (000101000000),
    -> ('70-01-01 00:00:00'),
    -> (700101000000);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test6;
+---------------------+
| theDateTime         |
+---------------------+
| 1997-07-01 00:00:00 | -- -> ('1997-07-01 00:00:00'),
| 1997-07-01 00:00:00 | -- -> (19970701000000),
| 2000-01-01 00:00:00 | -- -> ('00-01-01 00:00:00'),
| 2000-01-01 00:00:00 | -- -> (000101000000),
| 1970-01-01 00:00:00 | -- -> ('70-01-01 00:00:00'),
| 1970-01-01 00:00:00 | -- -> (700101000000);
+---------------------+
6 rows in set (0.00 sec)

看过 YEAR、DATE、DATETIME，其实我们大概可以了解到，在 MySQL 时间数据类型中，年份如果写全则是字面上的年份，如果简写，那么 00~69 表示 2000 ~ 2069，70~99 表示 1970 ~ 1999。

TIMESTAMP

类型	日期格式	日期范围	字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC	4

TIMESTAMP 类型和 DATETIME 类型的格式相同，存储 4 个字节（比DATETIME少），取值范围比 DATETIME 小。

使用场景

• 仅用来存储 年，通常使用 YEAR，因为比 DATE 需要更少的空间
• 用来存储 年月日，通常用 DATE
• 用来存储 时分秒，通常用 TIME
• 用来存储 年月日时分秒，通常用 DATETIME
• 用来存储当前时间，通常使用 TIMESTAMP
• 通常更方便的做法是用一个 INT 类型来记录时间戳

字符串类型

字符串类型	字节	描述和存储需求
CHAR(M)	M	M 为 0~255 的整数
VARCHAR(M)	值的长度 + 1字节	M 为 0~65535 的整数
TINYBLOB	值的长度 + 1字节	允许长度为 0~255字节
BLOB	值的长度 + 2字节	允许长度为 0~65535字节
MEDIUMBLOB	值的长度 + 3字节	允许长度为 0~167772150字节
LONGBLOB	值的长度 + 4字节	允许长度为 0~4294967295字节
TINYTEXT	值的长度 + 2字节	允许长度为 0~255字节
TEXT	值的长度 + 2字节	允许长度为 0~65535字节
MEDIUMTEXT	值的长度 + 3字节	允许长度为 0~167772150字节
LONGTEXT	值的长度 + 4字节	允许长度为 0~4294967295字节
VARBINARY(M)	值的长度 + 1字节	允许长度为 0~M字节的**变长**字节字符串
BINARY(M)	M	允许长度为 0~M字节的**定长**字节字符串

CHAR 和 VARCHAR

字符串类型	字节	描述和存储需求
CHAR(M)	M	M 为 0~255 的整数
VARCHAR(M)	值的长度 + 1字节	M 为 0~65535 的整数

CHAR 适合一些长度固定的数据，如 MD5 编码、手机号码等； VARCHAR 适合长度不固定的数据，如备注、描述等。

eg:

CREATE TABLE test7 (
    name CHAR(18),
    note VARCHAR(500)
);

• name 字段不管输入多少字节的数据，都占用 18 字节的空间，最长不能超过 18 字节；
• note 字段输入多少字节的数据，就占用多少字节的数据，但不能超过限定的 500 字节。
• CHAR 会截断尾部的空格，VARCHAR 则会保留尾部的空格。

BINARY 和 VARBINARY

BLOB

TEXT

ENUM

SET