MySQL字段长度详解 附实操分析

MySQL字段长度、字符、字节实操详解

一、前言

搜了下有关方面的内容发现良莠不齐。大部分人对这方面的内容也比较模糊，先附上MYSQL常用类型图。

二、字节

首先需要达成共识的是：

1个Byte字节等于8个bit位

。

bit

是最小一级的信息单位，可以表示一个0或1（即二进制）；
那么由此我们可以计算，一个字节其实可以表示256种取值。

计算方式如下：
    一个Byte是8位二进制数，既然是二进制，所以每一位只有两种可能：0、1
    取值从［00000000］→［11111111］根据排列组合，2^8=256 所以有256种取值。

三、字符

字符其实是一个统称，字母、数字、运算符号、标点符号和其他符号，以及一些功能性符号都属于字符。比如：&、中、A。
那到底一个字符占用多少字节呢？这个问题其实取决于数据库所采用的编码格式。
一般来说一个英文字符（A、b）都占用1个字节。在UTF8编码下，一个中文汉字占3个字节；在GBK编码下，一个中文汉字占用2个字节。

完了，gg了！   ==> 七个字符
hello,世界    ==> 八个字符

四、字段长度

这个概念来源于当新建或者编辑表，设计字段会有涉及。比如下表中的int(11)、varchar(255)，括号中的11、255指的就是字段长度。

CREATETABLE`sys_test`(`t_id`int(11)NOTNULLCOMMENT'主键ID',`name`varchar(255)DEFAULTNULLCOMMENT'姓名',`age`tinyint(4)DEFAULTNULLCOMMENT'年龄',PRIMARYKEY(`t_id`))

注意：这里的字段长度准确来说指的是显示的宽度，不同数据类型对这里的宽度处理也不一样。

这也是很多人模糊的地方，这里加上实操与例子进行说明。

4.1 例 int(1) 、int(4) 、int(11)

数值类型这里以int(1) 、int(4) 、int(11)为例，其实这三者能存储的数字范围是一样的，都是只能存储

负2^31 到 2^31-1

或者

无符号的 0 到 2^32-1

。

1. 创建一张测试表，结构如下。
2. 同时向这张表三个字段都插入 123456789，未超过int类型的取值范围，插入成功。 可见不管是int(1)、还是int(4)插入一个位数为9的数都能插入成功，而且显示正常。
3. 同时向这张表三个字段都插入 12345678910，超过了int类型的取值范围，插入失败。
4. 我们重新修改表结构，将所有字段这里的 填充零 勾选上。保存后会发现，未勾选的无符号也会被勾选上。这里的填充0指的是当你设置了长度比如说int(4)，4个长度而你只插入了3位数的值比如 999 时，会自动的在缺少的位数前面补0，查询出来的结果变成 0999。而前面补零的操作在业务场景下，只在值为自然数的时候有意义。所以当你勾选填充零时，这个字段就自动变成了无符号字段，只能存储自然数，取值范围：0~2^32-1。
5. 勾选填充零后，只允许存储自然数，插入负数报错。
6. 同时向这张表三个字段都插入 999，插入成功。查询出来的结果里，字段不够的位数会补0。

CREATE TABLE `tb_int_test` (
  `int1` int(1) DEFAULT NULL,
  `int4` int(4) DEFAULT NULL,
  `int11` int(11) DEFAULT NULL
)-- 插入成功
INSERT into tb_int_test VALUES(123456789,123456789,123456789)-- 查询结果
SELECT * FROM tb_int_test

-- 插入失败 越界
INSERT into tb_int_test VALUES(12345678910,12345678910,12345678910)-- 插入失败 越界
INSERT into tb_int_test VALUES(-1,-2,-3)-- 插入成功。查询出来的结果里，字段不够的位数会补0。
INSERT into tb_int_test VALUES(999,999,999)

综上，我们可以得出一个结论：

在数值类型里面：在满足取值范围的前提下，字段的长度指的是当该字段被设置为填充零、无符号时，最多填充零的数量。
int(1) 最多填充1个0、int(4)最多填充4个0…int(M)经常搭配填充零（zerofill）使用，达到补零显示的功能。

4.2 为什么int常被设置为int(11)

通过上述结论，我们可知要使int(M)中的M取值有意义，就必须要在满足int类型取值范围的前提下进行。
而int类型能存储

负2^31 到 2^31-1

或者

无符号的 0 到 2^32-1

。即

-2147483648 ~ 2147483647

或者

0 ~ 4294967295

。
不考虑无符号的情况下，

-2147483648

的位数为11（这里的负号也有一位），设置为int(11)意义在此，而当考虑无符号的情况下，只需要10位，设置int(10)就可以了。

4.3 varchar(5) 与 varchar(255)

1. 创建一张表，结构如下：
2. 同时向这张表两个字段都插入7个字符。varchar5字段插入失败，越界。
3. 同时向这张表两个字段都插入5个字符。插入成功。

CREATETABLE`tb_varchar_test`(`varchar255`varchar(255)COLLATE utf8_unicode_ci DEFAULTNULL,`varchar5`varchar(5)COLLATE utf8_unicode_ci DEFAULTNULL)-- 插入7个字符插入失败INSERTinto tb_varchar_test VALUES("钓鱼岛是中国的","钓鱼岛是中国的")-- 插入5个字符插入成功INSERTinto tb_varchar_test VALUES("我是中国人","我是中国人")

综上，我们可以得出一个结论：

在字符类型中，char(1)、varchar(255)，这里的字符长度指的是能插入数据的最大宽度，超过这个宽度插入报错。

4.4 char 与 varchar

char的长度是固定的，而varchar的长度是可变的。
也就是说，定义一个char(10) 和 varcha(10)，如果存进去的是

csdn

，而varchar消耗的长度为4，char所占的长度固定为10，除了字符

csdn

外后面会补充六个空格占位符，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

char的存取效率要比varchar要快得多，因为其长度固定，方便程序的存储与查找；varchar空间利用率高，而char读取速度快，但以空间换取时间效率，因为其长度固定，所以难免会有多余的空格占位符占据空间。

所以char适用于存储固定长度的字符串比如：身份证号、手机号等；varchar适用于存储不定长度的字符串比如：地址、个人简介等。在合适的场景下选用合适的类型才是王道。