起因
最近在整理代码规范,按照之前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。对于100条数据,光一个varchar字段就占约1GB内存。如果我们该用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,每个字符最多占3个字节,最大长度不能超过21845字符
字符类型若为utf8mb,每个字符最多占4个字节,最大长度不能超过16383字符
但通常导致varchar长度限制的通常是一行定义的长度,就是表里所有字段定义的长度总和。这个限制也是65535 byte。如果超出长度,会报错:
1
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
【强制】varchar是可变长字符串,不预先分配存储空间,长度不要超过5000,如果存储长度大于此值,定义字段类型为text,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
varchar(50)是能保存16个汉字,还是25个,抑或50个?
以前SQL Server的nvarchar转Oracle的varchar2时造成的固有印象,让我一直觉得varchar保存中文字时长度需要打对折或除以3。
但这个也是MySQL 5.0版本之前的事。现在varchar(n)是几,就能存几个中文字。
不过也需要注意统计字数使用CHARACTER_LENGTH而非LENGTH
1
2
3
4
-- 返回为12
SELECT LENGTH("轻松工作");
-- 返回为4
SELECT CHARACTER_LENGTH("轻松工作");
为什么还是用MySQL
为什么MySQL坑那么多,不改用PostgreSQL?
相比MySQL,我个人更偏好PostgreSQL,能从各种设计细节就感觉得到很规范。但无奈国内分布式数据库方案基本都是基于MySQL的。。。虽然我们的场景在今年年内暂时也看不到用分布式的必要性,但万一有了呢。。。
先发优势真是可怕。
参考资料
感谢这篇stackexchange的详细回答
database design - MySQL - varchar length and performance - Database Administrators Stack Exchange
关于内存表的详细介绍
MySQL · 特性分析 · 内部临时表
为什么索引长度会有767 byte或3072 byte的限制的详细解释
关于InnoDB索引长度限制的tips - 追风刀·丁奇 - ITeye博客
本文永久链接 [ https://galaxyyao.github.io/2019/07/30/MySQL-没有必要的varchar-255-长度及存储汉字问题汇总/ ]