如何理解MySQL中的varchar(N)

52次阅读

共计 5832 个字符，预计需要花费 15 分钟才能阅读完成。

本篇文章给大家分享的是有关如何理解 MySQL 中的 varchar(N)，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。

一前言
varchar(N) N 代表什么意思，能存放多少个中文字符？属于老生常谈的问题了，今天又被一个开发同事问我关于这个问题，索性写一篇文章来具体介绍一下。

二理论知识
先说明一下 MySQL 历来版本对 varchar 的定义:
4.0 版本以下，varchar(50)，指的是 50 字节，如果存放 UTF8 汉字时，只能存 16 个（每个中文 3 字节）
5.0 版本以上，varchar(50)，指的是 50 字符，无论存放的是数字、字母还是 UTF8 中文（每个中文 3 字节），都可以存放 50 个
存储限制
需要额外占用字节存放字符的长度: 小于 255 为 1 个字节，大于 255 则要 2 个字节
编码限制
gbk : 每个字符最多占用 2 个字节
utf8: 每个字符最多占用 3 个字节
utf8mb4 每个字符最多占用 4 个字节, 中文占 3 个字节，emoji 表情符号占用 4 个字节
长度限制
MySQL 定义行的长度不能超过 65535，该数值限制了列的数目，比如 char(128) utf8 字符集, 最多有 65535/(128*3)=170 个汉字。

三测试
环境 Server version: 5.6.26-74.0-log Percona Server
mysql create table t1
– (id int NOT NULL AUTO_INCREMENT primary key,
– name varchar(10)
– ) engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql create table t2
– (id int NOT NULL AUTO_INCREMENT primary key,
– name varchar(10)
– ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql create table t3
– (id int NOT NULL AUTO_INCREMENT primary key,
– name varchar(10)
– ) engine=innodb default charset=gbk;
Query OK, 0 rows affected (0.01 sec)
utf8mb4 字符集
mysql insert into t1(name) values(abcdfeghi
Query OK, 1 row affected (0.00 sec)
mysql insert into t1(name) values(abcdfeghij
Query OK, 1 row affected (0.00 sec)
mysql insert into t1(name) values(abcdfeghijk
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t1(name) values(一二三四五六七八九十
Query OK, 1 row affected (0.00 sec)
mysql insert into t1(name) values(一二三四五六七八九十一
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql show warnings;
+———+——+——————————————-+
| Level | Code | Message |
+———+——+——————————————-+
| Warning | 1265 | Data truncated for column name at row 1 |
+———+——+——————————————-+
1 row in set (0.00 sec)
mysql insert into t1(name) values(0123456789
Query OK, 1 row affected (0.00 sec)
mysql insert into t1(name) values(01234567890
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select id,name,length(name),char_length(name) from t1;
+—-+——————————–+————–+——————-+
| id | name | length(name) | char_length(name) |
+—-+——————————–+————–+——————-+
| 1 | abcdfeghi | 9 | 9 |
| 2 | abcdfeghij | 10 | 10 |
| 3 | abcdfeghij | 10 | 10 |
| 4 | 一二三四五六七八九十 | 30 | 10 |
| 5 | 一二三四五六七八九十 | 30 | 10 |
| 6 | 0123456789 | 10 | 10 |
| 7 | 0123456789 | 10 | 10 |
+—-+——————————–+————–+——————-+
7 rows in set (0.00 sec)
utf8 字符集
mysql insert into t2(name) values(abcdfeghi
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(name) values(abcdfeghij
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(name) values(abcdfeghijk
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t2(name) values(一二三四五六七八九十
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(name) values(一二三四五六七八九十一
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t2(name) values(0123456789
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(name) values(01234567890
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select id,name,length(name),char_length(name) from t2;
+—-+——————————–+————–+——————-+
| id | name | length(name) | char_length(name) |
+—-+——————————–+————–+——————-+
| 1 | abcdfeghi | 9 | 9 |
| 2 | abcdfeghij | 10 | 10 |
| 3 | abcdfeghij | 10 | 10 |
| 4 | 一二三四五六七八九十 | 30 | 10 |
| 5 | 一二三四五六七八九十 | 30 | 10 |
| 6 | 0123456789 | 10 | 10 |
| 7 | 0123456789 | 10 | 10 |
+—-+——————————–+————–+——————-+
7 rows in set (0.00 sec)
gbk 字符集
mysql insert into t3(name) values(abcdfeghi
Query OK, 1 row affected (0.00 sec)
mysql insert into t3(name) values(abcdfeghij
Query OK, 1 row affected (0.00 sec)
mysql insert into t3(name) values(abcdfeghijk
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t3(name) values(一二三四五六七八九十
Query OK, 1 row affected (0.01 sec)
mysql insert into t3(name) values(一二三四五六七八九十一
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t3(name) values(0123456789
Query OK, 1 row affected (0.00 sec)
mysql insert into t3(name) values(01234567890
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select id,name,length(name),char_length(name) from t3;
+—-+——————————–+————–+——————-+
| id | name | length(name) | char_length(name) |
+—-+——————————–+————–+——————-+
| 1 | abcdfeghi | 9 | 9 |
| 2 | abcdfeghij | 10 | 10 |
| 3 | abcdfeghij | 10 | 10 |
| 4 | 一二三四五六七八九十 | 20 | 10 |
| 5 | 一二三四五六七八九十 | 20 | 10 |
| 6 | 一二三四五六七八九十 | 20 | 10 |
| 7 | 0123456789 | 10 | 10 |
| 8 | 0123456789 | 10 | 10 |
+—-+——————————–+————–+——————-+
8 rows in set (0.00 sec)
从上面的测试可以看出, 目前版本中 varchar(N) 定义的长度的单位是字符,length(str) 表示 str 占用的字节数，char_length(str)表示 str 占用的字符数。
不论什么字符集，对于数字和英文字母都是只占用 1 个字符，也占用一个字节。而中文汉字因字符集不同而不同。

四总结
回过头来回答文章开头的问题 varchar(N) 可以存放多少个中文汉字。答案是在 5.0 以后的版本中 varchar(N) 可以存放 N 个汉字。
抛开字符集，如果一行数据全部为 varchar 类型，其最大长度为 65535 个字节。
行长度计算公式如下：
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + delete_flag + 7)/8
+ (number of variable-length columns)
对于 MyISAM，需要额外 1 个位来记录值是否为 NULL；对于 InnoDB，没有区别
对于 row_format 为 fixed，delete_flag 为 1；对于 row_format=dynamic,delete_flag 为 0

根据这个公式，我们便能够解答开头 N 的最大值：(65535-1-2)/3
减 1 是因为实际存储从第 2 个字节开始
减 2 则因为要在列表长度存储实际字符长度
除 3 是因为 utf8 编码限制
再来一道：
create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
N 的最大值：(65535-1-2-4-30*3)/3
则此处 N 的最大值为 (65535-1-2-4-30*3)/3=21812
减 1 和减 2 与上例相同;
减 4 的原因是 int 类型的 c 占 4 个字节;
减 30* 3 的原因是 char(30)占用 90 个字节，编码是 utf8。
如果被 varchar 超过上述的 b 规则，被强转成 text 类型，则每个字段占用定义长度为 11 字节，当然这已经不是“varchar”了。

以上就是如何理解 MySQL 中的 varchar(N)，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。

正文完