不知道大家第一眼看标题的时候有没有理解,什么是“字段类型长度”,这里我来解释下,就比如我们在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 |
| 数据类型语法 | 最大尺寸 |
| --- | --- |
| 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 | 它是一个多表面对象,表示多个多边形元素的集合。它是一种二维几何。 |
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约束达到想要的效果
- 对于字符串类型,指定的宽度要尽可能的贴合业务需要存储数据大小的范围,就可以更加高效的利用存储空间
原文链接