MySQL基础教程：关于varchar（N）

一前言
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”了 CentOS6下安装RedisMySQL参数调优最佳实践相关资讯 MySQL基础教程

高性能MySQL（第3版）中文PDF带目（10/26/2014 10:03:50）
如何在MySQL中的获取IP地址的网段（02/18/2013 12:23:33）
C++和C#访问MySQL的简单代码示例（12/21/2012 09:04:10）

MySQL SELECT同时UPDATE同一张表（02/19/2013 07:20:18）
Linux修改MySQL最大并发连接数（02/15/2013 15:37:21）
MySQL SET类型字段的SQL操作知识（12/19/2012 12:10:48）

本文评论查看全部评论（0）