【MySQL基础】常见数据类型及选用建议

为MySQL表字段选取合适的数据类型对于提高数据库空间使用效率和整体性能有着重要意义。MySQL中常见数据类型包括：

（1）数值类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT；浮点数类型FLOAT和DOUBLE；定点数类型DECIMAL。

（2）日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。

（3）字符串类型：包括CHAR、VARCHAR、BLOB和TEXT等。

下面详细介绍各种常见类型。

1.整数类型

类型	字节	范围（有符号）	范围（无符号）
TINYINT	1	(-128~127)	(0~255)
SMALLINT	2	(-32,768~32,767)	(0~65,535)
MEDIUMINT	3	(-8,388,608~8,388,607)	(0~16,777,215)
INT/INTEGER	4	(-2,147,483,648~2,147,483,647)	(0~4,294,967,295)
BIGINT	8	(-9,223,372,036,854,775,808~9,223,372,036,854,775,807)	(0~18,446,744,073,709,551,615)

说明

不同整数类型的主要区别在于存储空间和取值范围。整数类型可以添加一些可选属性，如：指定显示宽度、指定无符号类型、指定填充位数。

1）常规形式

示例：

create TABLE TMP1(
x INT,
y BIGINT,
PRIMARY KEY(x)
);

常规形式的定义较为简单。除常规形式外，还有其他几种附加属性可以设置，包括指定显示宽度、指定无符号类型、指定填充位。

2）指定显示宽度

即类型后面加数字，例如INT(3)。但需要注意：INT(3)仅表示显示宽度，实际存储范围不变，当插入数据超过显示宽度时，仍然能够完整显示该数值。例如：

create TABLE TMP2(
x INT(3),
y BIGINT,
PRIMARY KEY(x)
);

INSERT INTO TMP2(x,y) VALUES(10000,10000);

查询x,y，发现结果为：

mysql> select x,y from TMP2;
+-------+-------+
| x     | y     |
+-------+-------+
| 10000 | 10000 |
+-------+-------+
1 row in set (0.00 sec)

3）指定无符号类型(UNSIGNED)

示例：

create TABLE TMP3(
x INT(3) UNSIGNED,
y BIGINT UNSIGNED,
PRIMARY KEY(x)
);

上述建表语句定义了x,y为无符号类型。当向无符号类型插入负数时将报：out of range错误。

4）指定填充位(ZEROFILL)

示例：

create TABLE TMP4(
x INT(3) UNSIGNED ZEROFILL,
y BIGINT UNSIGNED,
PRIMARY KEY(x)
);
INSERT INTO TMP4(x,y) VALUES(10,10);
INSERT INTO TMP4(x,y) VALUES(10000,10);

上述建表语句指定了x的显示宽度为3，不足3位时用零补足（超过三位正常显示）。

查询x,y，发现结果为：

mysql> select x,y from TMP4;
+-------+------+
| x     | y    |
+-------+------+
|   010 |   10 |
| 10000 |   10 |
+-------+------+
2 rows in set (0.00 sec)

选用建议

对于整数类型，选取的原则就是：在确保取值范围足够的前提下，使用占用空间较小的类型。但需要注意，千万不要为了节省空间而去选择勉强够用的类型，这样万一今后数据超出范围后导致系统异常的成本远大于多一点存储空间的成本。

2.浮点数与定点数类型

类型	字节	范围（有符号）	范围（无符号）
FLOAT	4	0和-3.402823466E+38 ~ -1.175494351E-38	0和1.175494351E-38 ~ 3.402823466E+38
DOUBLE	8	0和-1.7976931348623157E+308 ~ -2.2250738585072014E-308	0和2.2250738585072014E-308 ~ 1.7976931348623157E+308
DECIMAL/DECIMAL(M,D)	M+2	-	-

说明

FLOAT和DOUBLE属于浮点数类型，取值范围如上表。

DECIMAL为定点数类型，实际是以串存放的，可能的最大取值范围与DOUBLE一样，但有效的取值范围由M和D的值决定。设D不变时，M越大取值范围越大。

注意1：对于浮点数和定点数类型，当小数部分超出精度范围时会四舍五入；当整数部分超出范围时会报错。

注意2：FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示；DECIMAL在不指定精度时，默认整数为10，小数为0。

注意3：对浮点数进行减法和比较运算时容易出问题，应尽量避免此类操作。

注意4：与定点数相比，浮点数的优点是长度相同的情况下可表示更大的数据范围，缺点是容易出现精度问题。

选用建议

由于定点数以字符串形式存储，适合精度要求高、对误差容忍度低的场景，比如科学数据、货币等。

3.字符串类型

类型	存储需求（字节）	说明
CHAR (M)	M个字节	M为0~255之间的整数
VARCHAR(M)	存储值的长度+1个字节	M为0~65535之间的整数
TINYBLOB	存储值的长度+1个字节	允许长度0~255字节
BLOB	存储值的长度+2个字节	允许长度0~65535字节
MEDIUMBLOB	存储值的长度+3个字节	允许长度0~16777215字节
LONGBLOB	存储值的长度+4个字节	允许长度0~4294967295字节
TINYTEXT	存储值的长度+1个字节	允许长度0~255字节
TEXT	存储值的长度+2个字节	允许长度0~65535字节
MEDIUMTEXT	存储值的长度+3个字节	允许长度0~16777215字节
LONGTEXT	存储值的长度+4个字节	允许长度0~4 294 967 295字节

说明

下面说一下几种字符串类型的特点：

1）CHAR与VARCHAR

• CHAR(M)为固定长度字符串，在定义时指定字符串列长。VARCHAR(M)是长度可变的字符串，M表示最大列长度，占用的空间为字符串的实际长度加1。
• 当检索到CHAR值时，尾部的空格将被删除。VARCHAR在值保存和检索时尾部的空格仍保留。

2）TEXT与BLOB

• 保存较大文本时，通常会选择TEXT或者BLOB。
• BLOB列存储的是二进制字符串（字节字符串），TEXT列存储的是非二进制字符串（字符字符串）
• BLOB列没有字符集，排序和比较基于列值字节的数值；TEXT列有一个字符集，根据字符集对值进行排序和比较。

使用建议

• CHAR与VARCHAR：CHAR是固定长度，通常处理速度比VARCHAR要快，缺点是浪费存储空间。因此，对存储不大但在速度上有要求的可以使用CHAR类型，反之可以使用VARCHAR类型来实现。实际生产上，VARCHAR使用更为广泛，尤其是对于InnoDB存储引擎，其数据表的存储格式不分固定长度和可变长度，使用VARCHAR更为有利。
• BLOB与TEXT：BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大容量的信息。BLOB主要存储图片、音频信息等，而TEXT只能存储纯文本文件。

4. 日期类型

常见日期类型包括：

类型	字节	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳
YEAR	1	1901/2155	YYYY	年份值

说明

上述日期类型除了DATETIME和TIMESTAMP比较相似外，其他类型容易区分。下面详细介绍DATETIME和TIMESTAMP的特点。

DATETIME和TIMESTAMP区别：

1）存储字节和支持的范围不同，TIMESTAMP能表示的日期范围较小。

2）TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前。DATETIME只存储实际输入日期值。

3）底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。

4）两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。

选用建议

DATETIME与TIMESTAMP：在实际开发工作中使用的大多数类型是DATETIME，由于其表示日期时间信息完整且取值范围大，一般优先选择。但是对于涉及日期/时间计算的场景，可以选择 TIMESTAMP。