还记得当年用刚入行用SQLServer时, 也是喜欢使用 varchar(255), nvarchar(255) 的.
起因
最近在整理代码规范,按照之前oracle的习惯,定了以下的字段长度设定规范:
- 名称字段:
varchar(200)
- 较长的名称字段/简介字段:
varchar(500)
- 特别长的描述字段:
varchar(2000)
- 超过2000中文字的字段:
text
为什么是200长度,而不是100或300,也是拍脑袋想的,类似DND里的房规。
但在被问起为什么不设置为经常见到的varchar(255)
时,一时回答不上来。趁这个机会,把字段长度这块的知识汇总梳理一下。
为什么会经常被设置为varchar(255)
MySQL 4.1版本之前,varchar
的最大长度是255 byte字节(也有一说是5.0.3版本之前)。查了下这个版本发布都是2004年的事情了。惯性真恐怖,我可不相信还有多少系统是从2004年升级过来的。
varchar(50)
和varchar(255)
有性能上的差别么?
对于INNODB
,varchar(50)
和varchar(255)
这两者在存放方式上完全一样:1-2 byte
保存长度,实际的字符串存放在另外的位置,每个字符1 byte
到4 byte
不定(视编码和实际存储的字符而定)。所以将一个字段从varchar(50)
长度改成varchar(100)
长度不会导致表的重建。但如果把长度从varchar(50)
改成varchar(256)
就不一样了,表示长度会需要用到2 byte
或更多。
既然255
长度以下对INNODB
都一样,而且我们平时基本上也不太会使用到MYISAM
,那么是不是为了省心,我们就可以把255长度以下的字段的类型都设置成varchar(255)了呢?
非也!
因为内存表介意。
虽然我们不会明文创建内存表,但所有的中间结果都会被数据库引擎存放在内存表。我们可以通过EXPLAIN
或者SHOW STATUS
可以查看MYSQL是否使用了内存表用来帮助完成某个操作。
而内存表会按照固定长度来保存。以utf-8
编码为例,对于varchar(255)
,每一行所占用的内存就是长度的2 byte + 3 * 255 byte
= 767 bytes。对于100条数据,光一个varchar字段就占约 75Mb 内存。如果我们改用varchar(50)
,就可以节约80%的内存空间。
除此之外,255长度也可能会对索引造成坑。MySQL在5.6版本及之前的最大长度是767 byte。但MySQL 5.5版本后开始支持4个byte的字符集utf8mb4(沙雕表情用到的字符太多,长度不够用)。255 * 4 > 767,所以索引就放不下varchar(255)长度的字段了。虽然MySQL在5.7版本后将限制改成了3072 byte,但如果是多字段的联合索引还是有可能会超过这个限制。
所以我们的结论就是:在长度够用的情况下,越短越好。
varchar的最大长度是多少
varchar的最大长度是65535 byte。所以
- 字符类型若为gbk,每个字符最多占2个字节,最大长度不能超过32766字符
- 字符类型若为utf8 (同utf8mb3),每个字符最多占3个字节,最大长度不能超过21845字符
- 字符类型若为utf8mb4,每个字符最多占4个字节,最大长度不能超过16383字符 但导致varchar长度限制的, 通常是一行定义的长度, 就是表里所有字段定义的长度总和。这个限制也是65535 byte。如果超出长度,会报错:
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。
这也是为什么阿里开发规范中这么要求:
【强制】varchar是可变长字符串,不预先分配存储空间,长度不要超过5000,如果存储长度大于此值,定义字段类型为text,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
varchar(50)是能保存12个还是16个汉字,还是25个,抑或50个??
以前SQL Server
的nvarchar
转 Oracle
的varchar2
时造成的固有印象,让我一直觉得varchar
保存中文字时长度需要打对折或除以3。
但这个也是MySQL 5.0版本之前的事。现在varchar(n)是几,就能存几个中文字。
不过也需要注意统计字数使用CHARACTER_LENGTH
而非LENGTH
SELECT LENGTH("轻松工作") AS a, CHARACTER_LENGTH("轻松工作") AS b;
-- 返回: 12 4