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指的就是字段长度。

CREATE TABLE `sys_test` (
  `t_id` int(11) NOT NULL COMMENT '主键ID',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`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个字符。插入成功。

CREATE TABLE `tb_varchar_test` (
  `varchar255` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `varchar5` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL
) 
-- 插入7个字符插入失败
INSERT into tb_varchar_test VALUES("钓鱼岛是中国的","钓鱼岛是中国的")

-- 插入5个字符插入成功
INSERT into 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适用于存储不定长度的字符串比如：地址、个人简介等。在合适的场景下选用合适的类型才是王道。