mysql 中varchar的长度设定

最近在工作中遇到一个问题,项目中数据库的一个字段设定的长度要被用尽了,需要调整.这个字段是varchar类型的.于是直面而来的一个问题就是:varchar类型字段长度到底能设置为多大?

---

首先确定的一点是mysql中varchar(n) ,这个n代表的是字符数(不是字节数).

举个例子:

Table	Create Table
user	CREATE TABLE `user` ( `id` bigint(20) NOT NULL, `name` varchar(5) NOT NULL, `age` smallint(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into user values (111,'我是中国人',20);
1 row affected in 51 ms

增加一个字符

insert into user values (222,'我是中国人呢',20);
[22001][1406] Data truncation: Data too long for column 'name' at row 1

提示超长

insert into user values (222,'abcde',20);
 1 row affected in 61 ms

增加一个字符

insert into user values (333,'abcdef',20);
[22001][1406] Data truncation: Data too long for column 'name' at row 1

提示超长

可以看出 n 代表的是字符数

那这个n最大是多少呢?

计算公式为:

# 21840.6667 = 21840
select (65535 - 8 - 2 - 1 -2) / 3;

解析:
varchar的设定受到mysql对一行数据允许最大字节数的限制.
mysql要求一个行的定义长度不超过65535(2^16 - 1) 所以有65535
减8的原因是字段id的bigint类型占用8个字节.
减2的原因是varchar头部的2个字节表示长度(超过255为2,小于255为1).
减1的原因是实际行的存储从第2个字节开始.
减2的原因是字段age的smallInt类型占用2个字节.
除以3的原因是一个utf8字符通常占用3个字节.

验证:

alter table user modify name varchar(21841);
[42000][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

更换为21840

alter table user modify name varchar(21840);
 2 rows affected in 165 ms

Field	Type	Null	Key	Default	Extra
id	bigint(20)	NO	PRI	NULL
name	varchar(21840)	YES		NULL
age	smallint(6)	YES		NULL