一文搞懂MySQL表字段类型长度的含义

不知道大家第一眼看标题的时候有没有理解，什么是“字段类型长度”，这里我来解释下，就比如我们在MySQL建表的时候，比如下面这个建表语句：

CREATE TABLE `user` (
  `id` int(10) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

其中id、name、age是该表的三个不同字段，而每个类型+数字就是该字段的类型长度，比如int(10)就是该字段是int类型长度为10，这下大家应该能理解什么是MySQL表字段类型长度了吧（其实在后续的讲解中会发现这个并不是长度，而是宽度）

那么重点问题来了，每一种类型后面括号里的数字都有什么含义呢，下面我们来剖析下：

首先MySQL表的字段都支持哪些类型?

1 MySQL支持的数据类型

1.1 类型分类

分为几个大类：

1.2 具体的数据类型

细节数据类型如下：

• 数值类型

类型	大小（Bytes）	范围（有符号）	范围（无符号）
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	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)
FLOAT	4	(-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	(-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的值

• 日期和时间类型

类型	大小（Bytes）	范围	格式
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD
TIME	3	-838:59:59/838:59:59	HH:MM:SS
YEAR	1	1901/2155	YYYY
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4	0/2038结束时间（第2147483647秒）	YYYYMMDD HHMMSS

• 字符串类型

类型	大小（Bytes）
CHAR	0-255
VARCHAR	0-65535
TINYBLOB	0-255
TINYTEXT	0-255
BLOB	0-65 535
TEXT	0-65 535
MEDIUMBLOB	0-16 777 215
MEDIUMTEXT	0-16 777 215
LONGBLOB	0-4 294 967 295
LONGTEXT	0-4 294 967 295

• 二进制大对象数据类型 (BLOB)

数据类型语法	最大尺寸
TINYBLOB	最多可以容纳 255 个字节。
BLOB(size)	最多可以容纳 65,535 字节。
MEDIUMBLOB	最多可以容纳 16,777,215 字节。
LONGBLOB	最多可以容纳 4gb 或 4,294,967,295 字节。

• 空间数据类型

它是一种特殊的数据类型，用于保存各种几何和地理值。

数据类型	说明
GEOMETRY	它是一个点或点的集合，可以保存具有位置信息的任何类型的空间值。
POINT	几何中的一个点代表一个位置。它存储 X、Y 坐标的值。
POLYGON	它是一个表示多边几何的平面。它可以由零个或多个内部边界和一个外部边界定义。
LINESTRING	它是具有一个或多个点的曲线。如果它只包含两个点，它代表直线。
GEOMETRYCOLLECTION	它是一种具有零个或多个几何值的集合。
MULTILINESTRING	它是一个多曲线几何体，具有一组线字符串值。
MULTIPOINT	它是多个点元素的集合。在这里，这些点不能以任何方式连接或排序。
MULTIPLYGON	它是一个多表面对象，表示多个多边形元素的集合。它是一种二维几何。

• JSON 数据类型

MySQL 从5.7.8 版本开始提供 JSON 数据类型的支持，与之前将 JSON 数据以字符串形式存储在列中相比，JSON 数据类型增加了以下支持：

（1）它提供了 JSON 文档的自动验证。

（2）它提供了对于JSON的最佳存储格式。

（3）提供了相关操作JSON类型数据的函数

1.3 数据类型说明

MySQL的数据类型长度是固定的，而不是由建表时指定的，unsigned表示无符号类型

CREATE TABLE `table01` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `num` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

当插入负数时就会报错

INSERT into table01 VALUE(1,'zs',-10)

ERROR 1264 (22003): Out of range value for column 'num' at row 1

插入数据超过最大长度时也会报错

INSERT into table01 VALUE(2,'ls',1000000000000)

ERROR 1264 (22003): Out of range value for column 'num' at row 1

2 建表时指定的长度概念

2.1 对于数值类型

我们大家都喜欢在建表时指定类型的大小，但是这个大小并不是数据类型的大小，而是宽度，这个数字无论是大是小占用的存储都是一样的，无论是int类型还是varchar类型，我们看一个例子：

CREATE TABLE `table03` (
  `num1` int(10) DEFAULT NULL,
  `num2` int(10) unsigned zerofill DEFAULT NULL,
  `num3` int(5) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据

insert into table03 value(1,1,1)
insert into table03 value(11111,11111,11111)
insert into table03 value(111111,111111,111111)
insert into table03 value(12345678901,12345678901,12345678901)

结果：

解释下zerofill：zerofill默认为int(10),当使用zerofill 时，默认会自动加unsigned（无符号）属性,插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0

2.2 对于字符串类型

我们来试下：

CREATE TABLE `table04` (
  `str1` varchar(10) DEFAULT NULL,
  `str2` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据：

insert into table04 value('a','a');
insert into table04 value('abc','abc');

报错：

mysql> insert into table04 value('abc','abc');
ERROR 1406 (22001): Data too long for column 'str2' at row 1

结果：

解释下varchar类型指定宽度的作用：

• VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N，MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存

• VARCHAR(N)，如果N<256时会使用一个字节来存储长度，如果N>=256则使用两个字节来存储长度

3 结论

MySQL建表语法再次说明：

create table 表名( 
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件], 
    ......
);

对某些值的宽度短于该列宽度的值进行左填补显示的，而不是为了限制在该列中存储值的宽度，也不是为了限制那些超过该列指定宽度的值的可被显示的数字位数，因此我们得出一下结论：

• 对于数值类型，可以不指定宽度，使用默认长度即可，如需指定宽度可配合unsigned、zerofill约束达到想要的效果
• 对于字符串类型，指定的宽度要尽可能的贴合业务需要存储数据大小的范围，就可以更加高效的利用存储空间