Mysql 温故知新系列 【数据类型详解】

小知识，大挑战！本文正在参与「程序员必备小知识」创作活动

本文已参与 「掘力星计划」 ，赢取创作大礼包，挑战创作激励金。

---

mysql 中的数据类型可以概括为：数值类型、时间类型和字符串类型

数值类型

细分下来，有整数和浮点数两种，再根据字节来区分。

字节一般是 8位 的二进制，如果需要带符号，就会占用第一位来表示符号，用 0 表示整数，1 表示负数

整数有 4 种，分别需要占用 1,2,3,4,8 个字节

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	$(-2^7, 2^7 -1)$	$(0, 2^8 -1)$	小整数值
SMALLINT	2 Bytes	$(-2^{15}, 2^{15} -1)$	$(0, 2^{16} -1)$	大整数值
MEDIUMINT	3 Bytes	$(-2^{31}, 2^{31} -1)$	$(0, 2^{32} -1)$	大整数值
INT或INTEGER	4 Bytes	$(-2^{63}, 2^{63}-1)$	$(0, 2^{64} -1)$	大整数值
BIGINT	8 Bytes	$(-2^{127}, 2^{127} -1)$	$(0, 2^{128} -1)$	极大整数值

浮点型有 2 种，分别需要占用 4,8 个字节

类型	大小	范围（有符号）	范围（无符号）	用途
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

针对于 DECIMAL 这种比较特殊的类型，有如下的规则：

• M是表示有效数字数的精度。 M范围为1〜65。
• D是表示小数点后的位数。 D的范围是0~30。MySQL要求D <= M。

DECIMAL 主要用于一些需要保存定长小数的项，比如，我们存储的值要求保存数位小数。

时间类型

常规使用有 3 种，日期（Date），时间（TIME），日期+时间组合（DATETIME）

较少使用的 2 种：年份（YEAR），时间戳（TIMESTAMP)

他们各自的区别整理如下：

类型	大小 ( bytes)	范围	格式	用途
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-1-19 11:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳
YEAR	1	1901/2155	YYYY	年份值

注意：TIMESTAMP 定义从 1970-01-01 开始，这个类型支持 4 个字节，最大值为 $2^{31}-1$，就可以知道时间戳能使用的最大值到 2038-1-19 11:14:07

字符串类型

常规的字符串类型有 CHAR, VARCHAR

文本类型有 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

二进制星星的文本类型有 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

他们的区别如下：

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYTEXT	0-255 bytes	短文本字符串
TEXT	0-65 535 bytes	长文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据

其中：char 和 varchar 是最最常用的类型

通过 char(n) 声明的字段，数据库就一定会占用这么长字节的位置，即便实际字符串只有寥寥数位；好处是会方便 mysql 进行数据检索，不会产生碎片空间，所以推荐对可预见长度的字符类型使用 char 来定义，比如 身份证、电话、姓名、银行卡、物料编码 等等

使用 varchar(n) 声明的字段，默认情况下，不会有空间浪费的情况。比如定义字段可用 10 位，实际数据仅使用 1 位，mysql 内部也只用 1 位，不会去占用另外的 9 位；好处是对空间的利用率较高，但若数据不断变化调整，mysql 内部就需要将数据转移到新的空间，因为原本并没有占用预留的空间。如此往复，空间的碎片率就会非常高

总结起来：定长字符类型建议用 char；不定长并且不怎么变化的数据类型，建议用 varchar；如果数据改动频繁，长度又是参差不齐，要么空间换时间用 char，要么时间换空间用 varchar

创作不易，期待大家的鼓励❤~