MySQL-没有必要的varchar(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。对于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-长度及存储汉字问题汇总/ ]