[大数据]搞懂 mysql 数据类型那些事(varchar、int、unsigned)

搞懂 mysql 数据类型那些事(varchar、int、unsigned)

在 mysql 中我们创建表的时候就会声明列的类型，比如 char(12)、varchar(100)、int(10)、bigint(20)。 有的时候可能可能还会看到 unsigned 这个关键字，那么这些有什么猫腻呢，你是不是清楚呢，那今天 我们就一起来探讨一下

1、char、varchar

1.1 char

1.1.1 char 的存储规则

char(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。
当检 索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。
CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义 char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

1.2 varchar

1.2.1 varchar的存储规则

4.0版本以下，varchar(20)，指的是20字节，如果存放 UTF8 汉字时，只能存6个（每个汉字3字节）。
5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字 节），都可以存放20个，最大大小是65532字节。
varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度。

官方是这么说的：
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.
In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value.
A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

1.2.2 varchar的控制位

MySQL 中的 varchar 字符类型还保留了1个字节来留其它控制信息。

1.2.2.1 示例 1

示例 1 ：若一张表中只有一个字段 varchar (N)类型，utf8编码，则N最大值为多少？

create table tb_test1(
	a varchar(N) // 则 N 最大值 = (65535 - 1 - 2) / 3 = 21844。
) default charset=utf8，

减 1 的原因是实际行存储从第 2 个字节开始。
减 2 的原因是 varchar 头部的 2 个字节表示长度。
除 3 的原因是字符编码是 utf8

sql测试：

create table tb_test1(a varchar(21844)) default charset=utf8;
Query OK, 0 rows affected (0.38 sec)

drop table tb_test1;
Query OK, 0 rows affected (0.00 sec)

create table tb_test1(a varchar(21845)) default charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for 
the used table type, not counting BLOBs, is 65535. You have to 
change some columns

1.2.2.2 示例 2

示例 2 ：若一张表中有一个字段 varchar(N) 类型，并且有其它的字段类型，utf8 编码，则N的最大值为多少？

create table tb_test2(
	a int, 
	b char(20), 
	c varchar(N) // 则：N最大值 = (65535 - 1 - 2 - 4 - 20 * 3) / 3 = 21822
) default charset=utf8;

减 1 的原因是实际行存储从第 2 个字节开始。
减 2 的原因是 varchar 头部的 2 个字节表示长度。
减 4 的原因是 a 字段的 int 类型占 4 个字节。
减 20 * 3 的原因是 char(20) 占用 60 个字节，编码是 utf8。

sql测试：

create table tb_test2(
	a int, 
	b char(20), 
	c varchar(21822)
) default charset=utf8;
Query OK, 0 rows affected (0.28 sec)

drop table tb_test2;
Query OK, 0 rows affected (0.20 sec)

create table tb_test2(
	a int, 
	b char(20), 
	c varchar(21823)
) default charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

1.2.2.3 示例 3

示例 3：若一张表中有多字段 varhcar(N) 类型，并且有其它的字段类型，gbk 编码，则 N 的最大值为多少？

create table tb_test3(
	a int, 
	b char(20), 
	c varchar(50), 
	d varchar(N) // 则：N最大值 = (65535 - 1 - 1 - 2 - 4 - 20 * 2 - 50 * 2) / 2 = 32693
) default charset=gbk;

第 1 个减 1 的原因是实际行存储从第 2 个字节开始。
第 2 个减 1 表示第二个 varchar(50) 头部一个 1 个字节表示长度(小于255)。
减 2 的原因是 varchar 头部的 2 个字节表示长度。
减 4 的原因是 int 类型占 4 个字节。
减 20 * 2 的原因是 char(20) 占用40个字节，编码是 gbk。
减 50 * 2 的原因是 varchar(50) 占用100个字节，编码是 gbk。

sql 测试：

create table tb_test3(
	a int, 
	b char(20),
	c varchar(50), 
	d varchar(32694)
) default charset=gbk;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

create table tb_test3(
	a int, 
	b char(20), 
	c varchar(50), 
	d varchar(32693)
) default charset=gbk;
Query OK, 0 rows affected (0.18 sec)

最后我们自己看一下例子:

DROP TABLE if EXISTS tb_test4;
create table tb_test4(
	a int, 
	b char(20),
	c varchar(50), 
	e varchar(50),
	d varchar(N)
) default charset=gbk;

-- N 表示的最大值：(65535 - 1 - 4 - 20 *2 - 1 - 50 * 2 - 1 - 50 *2 - 2 ) / 2 = 32643
-- 1 表示从第 2 个字节开始
-- 4 表示 int 占 4 个字节
-- 20 * 2 表示 char(20) 占用 40 个字节
-- 1 表示 varchar(50) 表示的长度
-- 50 *2 表示 varchar(50) 占用的字节
-- 1 表示第 2 个 varchar(50) 表示的长度
-- 50 *2 表示第 2 个 varchar(50) 占用的字节
-- 2 表示 d 字段用于表示长度的字节数

-- 如果 N 大于 32643 则报错
DROP TABLE if EXISTS tb_test4;
create table tb_test4(
	a int, 
	b char(20),
	c varchar(50), 
	e varchar(50),
	d varchar(32644)
) default charset=gbk;


-- 报错信息
DROP TABLE if EXISTS tb_test4
> OK
> Time: 0.004s
create table tb_test4(
	a int, 
	b char(20),
	c varchar(50), 
	e varchar(50),
	d varchar(32644)
) default charset=gbk
> 1118 - Row size too large. The maximum row size for the used table type, not 
> counting BLOBs, is 65535. This includes storage overhead, check the manual. 
> You have to change some columns to TEXT or BLOBs
> Time: 0s

1.3 varchar和char 的区别

1.3.1 定长和变长

char 表示定长，长度固定
varchar表示变长，即长度可变。
当所插入的字符串超出它们的长度时，视情况来处理，如果是严格模式，则会拒绝插入并提示错误信息，如果是宽松模式，则会截取然后插入。

如果插入的字符串长度小于定义长度时，则会以不同的方式来处理，
如 char(10)，表示存储的是10个字符，无论你插入的是多少，都是10个，如果少于10个，则用空格填满。
而 varchar(10)，小于10个的话，则插入多少个字符就存多少个。

varchar 怎么知道所存储字符串的长度呢？实际上，对于 varchar 字段来说，需要使用一个（如果字符串长度小于255）或两个字节（长度大于255）来存储字符串的长度。但是因为他需要有一个prefix来表示他具体bytes数是多少（因为varchar是变长的，没有这个长度值他不知道如何读取数据）。

1.3.2 存储的容量不同

对 char 来说，最多能存放的字符个数 255，和编码无关。
而 varchar 呢，最多能存放 65532 个字符。varchar 的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节

varchar 的编码长度限制
字符类型若为 gbk，则个字符最多占 2 个字节，最大长度不能超过 32766;
字符类型若为utf8，则每个字符最多占 3 个字节，最大长度不能超过 21845。
字符类型若为utf8mb4，则每个字符最多占 4 个字节，最大长度不能超过 16383。
若定义的时候超过上述限制，则 varchar 字段会被强行转为 text 类型，并产生 warning。

行长度限制
导致实际应用中 varchar 长度限制的是一个行定义的长度。
MySQL 要求一个行的定义长度不能超过 65535。若定义的表长度超过这个值，则提示 :

ERROR 1118 (42000): Row size too large.The maximum row size for the used table type,
not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

这就是说，比如创建一个表，表结构中有两个 varhcar 类型字段，那么这两个字段的总长度不能超过65535。官方说明如下：

Every table has a maximum row size of 65,535 bytes.
This maximum applies to all storage engines, but a given engine might have 
additional constraints that result in a lower effective maximum row size.

具体的限制，可以参考官方文档：

表列数和行大小的限制

1.4 超过长度后的存储现象(mysql 版本 5.7.36)

1.4.1 char

CREATE TABLE tb_test1 (
	id bigint(20) not null auto_increment primary key ,
	name char(5) not null default ''
);

-- 执行插入语句，name 的值超过 5 个字符：
insert into tb_test1 (name) values('bamboo1'),('jessica');

-- 然后控制台报错了：
-- insert into tb_test1 (name) values('bamboo1'),('jessica')
-- 1406 - Data too long for column 'name' at row 1
-- Time: 0.003s


-- 查询后，可以看到没有结果返回，说明没有插入成功 
SELECT  * FROM tb_test1;

然后插入小于 5 个字符再执行插入语句 ：

insert into tb_test1 (name) values('jack'),('allen');

-- 看控制台可以看到插入成功了，影响行数是 2.
-- insert into tb_test1 (name) values('jack'),('allen')
-- > Affected rows: 2
-- > Time: 0.001s

-- 查询返回结果了
SELECT  * FROM tb_test1;

1.4.2 varchar

-- 创建表
DROP TABLE IF EXISTS tb_test2;
CREATE TABLE tb_test2 (
	id bigint(20) not null auto_increment primary key ,
	name varchar(5) not null default ''
);

-- 执行插入语句(name 的值大于 5 )
insert into tb_test2 (name) values('bamboo1'),('jessica');

-- 执行语句后的控制台信息
insert into tb_test2 (name) values('bamboo1'),('jessica')
> 1406 - Data too long for column 'name' at row 1
> Time: 0.002s

-- 查询后，可以看到没有结果返回，说明没有插入成功 
SELECT  * FROM tb_test2;

-- 执行插入语句(name 的值小于 5 )
insert into tb_test2 (name) values('jack'),('allen');

-- 执行语句后的控制台信息
insert into tb_test2 (name) values('jack'),('allen')
> Affected rows: 2
> Time: 0.002s

-- 查询返回结果了
SELECT  * FROM tb_test2;

2、int

2.1 int(1)、int (10) 有啥区别?

这 2 个声明并没有多大区别，数据表示的范围都是一样的。
int(M): M (对应于上面的 1 和 10) indicates the maximum display width for integer types.1
在 integer 数据类型中，M 表示最大显示宽度。

在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。
说白了，除了显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。
另外，int(M) 只有跟 zerofill 结合起来，才能使我们清楚的看到不同之处。

测试一下

mysql> drop table if exists t;
mysql> create table t(id int zerofill);
mysql> insert into t(id) values(10);
mysql> select * from t;
+------------+
| id         |
+------------+
| 0000000010 |
+------------+
mysql> alter table t change column id id int(3) zerofill;
mysql> select * from t;
+------+
| id   |
+------+
|  010 |
+------+
mysql>
mysql> alter table t change column id id int(4) zerofill;
mysql> select * from t;
+------+
| id   |
+------+
| 0010 |
+------+
mysql>
mysql> insert into t(id) values(1000000);
mysql> select * from t;
+---------+
| id      |
+---------+
|    0010 |
| 1000000 |
+---------+
-- 从上面的测试可以看出，“(M)”指定了 int 型数值显示的

从上面的测试可以看出，“(M)”指定了 int 型数值显示的宽度，如果字段数据类型是 int(4)，则：当显示数值 10 时，在左边要补上 “00”；
当显示数值 100 是，在左边要补上“0”；当显示数值 1000000 时，已经超过了指定宽度“（4）”，
因此按原样输出。 在使用 MySQL 数据类型中的整数类型（tinyint、smallint、 mediumint、 int/integer、bigint）时，非特殊需求下，在数据类型后加个“(M)”，是没有任何意义的。

int(1)和int(11)在实际使用中，如果不使用 zerofill 是没有任何区别的，而且int型最大只能存储4294967295这个整数，我们可以发现其实只有10位。

综上所述，当我们需要用整形来存储一个字段类型的时候，应该尽量估算出该字段所需要的实际长度，
比如tinyint可存储无符号最大值是255（1个字节长度，即2的8次方），
smallint可存储无符号最大值是65535（2个字节长度，即2的16次方），
mediumint可存储无符号最大值是16777215（3个字节长度，即2的24次方），
而int型最大可存储4294967295（3个字节长度，即2的32次方）

2.2 unsigned 的含义

默认的 int 类型，取值范围是 -2147483648-2147483647 之间，
而 unsigned 的取值范围是 0-4294967295 之间。

默认的 int 类型，允许插入负数，unsigned 设置后，无法插入负数。

测试一下：

2.2.1 不声明 unsigned 的表

-- 1、创建 tb_test_int 表
CREATE TABLE `tb_test_int` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 2、向 tb_test_int 表中插入数据
insert into `tb_test_int` values(2147483647);
insert into `tb_test_int` values(-2147483648);

-- 3、查询表中数据
select * from tb_test_int;

-- 4、尝试一下，在这两个区间之外进行插入数据，是否可以成功插入。
insert into `tb_test_int` values(2147483648);

-- 控制台输出，可以看到报错了
-- insert into `tb_test_int` values(2147483648)
-- > 1264 - Out of range value for column 'id' at row 1
-- > Time: 0s


insert into `tb_test_int` values(-2147483649);
-- 控制台输出，可以看到报错了
-- insert into `tb_test_int` values(-2147483649)
-- > 1264 - Out of range value for column 'id' at row 1
-- > Time: 0s

-- 上面可以看到两条 Sql 均都报出异常，插入的值超出了范围，没办法进行插入数据，只能在 int 范围区间内进行数据插入。

2.2.2 声明 unsigned 的表

-- 1、创建 tb_test_int_un 表
DROP TABLE if EXISTS tb_test_int_un;
CREATE TABLE `tb_test_int_un` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 2、查看一下创建表语句
SHOW CREATE TABLE tb_test_int_un;
CREATE TABLE `tb_test_int_un` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 在 sql 语句中，我并没有指定 int 类型的字节长度，执行完 sql 以后，unsigned 会默认设置 int 字节长度为 10。


-- 2、向 tb_test_int_un 表中插入数据
insert into `tb_test_int_un` values(2147483647);

-- 控制台输出，正整数是可以的
-- insert into `tb_test_int_un` values(2147483647)
-- > Affected rows: 1
-- > Time: 0.001s

insert into `tb_test_int_un` values(-2147483648);
insert into `tb_test_int_un` values(-1);

-- 控制台输出，可以看到负数是不行的
-- insert into `tb_test_int_un` values(-2147483648)
-- > 1264 - Out of range value for column 'id' at row 1
-- > Time: 0s
-- 


-- 3、查询表中数据
select * from tb_test_int_un;

-- 4、尝试一下，在这两个区间之外进行插入数据，是否可以成功插入。
insert into `tb_test_int_un` values(2147483648);

-- 控制台输出，可以看到报错了
-- insert into `tb_test_int_un` values(2147483648)
-- > Affected rows: 1
-- > Time: 0.001s


-- 上面可以看到 sql 执行报出异常，插入的值超出了范围，没办法进行插入数据，只能在 int 范围区间内进行数据插入。