mysql中的varchar到底能存多长的字符

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

这个问题真的不简单。

我本地的数据库是mysql5.5

先看一下建表语句:

 
 
  1. CREATE TABLE `shop` ( 
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID', 
  3.   `shop_id` int(11) NOT NULL COMMENT '商店ID', 
  4.   `goods_id` smallint(6) NOT NULL COMMENT '物品ID', 
  5.   `pay_type` tinyint(1) NOT NULL COMMENT '支付方式', 
  6.   `price` decimal(10,2) NOT NULL COMMENT '物品价格', 
  7.   `comment` varchar(21839) NOT NULL COMMENT '备注', 
  8.   PRIMARY KEY (`id`), 
  9.   UNIQUE KEY `shop_id` (`shop_id`,`goods_id`) 
  10. ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='商店物品表' 

再看一下表结构:

 
 
  1. mysql> describe shop; 
  2. +----------+----------------+------+-----+---------+----------------+ 
  3. | Field    | Type           | Null | Key | Default | Extra          | 
  4. +----------+----------------+------+-----+---------+----------------+ 
  5. | id       | int(11)        | NO   | PRI | NULL    | auto_increment | 
  6. | shop_id  | int(11)        | NO   | MUL | NULL    |                | 
  7. | goods_id | smallint(6)    | NO   |     | NULL    |                | 
  8. | pay_type | tinyint(1)     | NO   |     | NULL    |                | 
  9. | price    | decimal(10,2)  | NO   |     | NULL    |                | 
  10. | comment  | varchar(21839) | NO   |     | NULL    |                | 
  11. +----------+----------------+------+-----+---------+----------------+ 

当我试图给varchar字段的长度加1时,杯具了:

 
 
  1. mysql> ALTER TABLE `shop` CHANGE `comment` `comment` VARCHAR( 21840 ) NOT NULL COMMENT '备注'; 
  2. 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只能存放21839个字符呢?

---------------------------华丽无敌的分隔线---------------------------

让我们先从最简单的情况入手。我们先创建如下的表,就一个varchar字段:

 
 
  1. mysql> explain table_test; 
  2. +-------+----------------+------+-----+---------+-------+ 
  3. | Field | Type           | Null | Key | Default | Extra | 
  4. +-------+----------------+------+-----+---------+-------+ 
  5. | abc   | varchar(21844) | NO   |     | NULL    |       | 
  6. +-------+----------------+------+-----+---------+-------+ 

首先要知道的是,mysql的记录行长度是有限制的,不是无限长的,这个长度是64K,即65535个字节,对所有的表都是一样的。

另外要知道的是编码。
utf8编码一个字符占3个字节;
gbk编码一个字符占2个字节;
latin1编码一个字符占1个字节。

可以用如下的程序来验证一下,已知程序文件的编码是utf-8:

 
 
  1. $str = '中'
  2. echo mb_strlen($str); 
  3.  
  4. $str = mb_convert_encoding($str"gbk""utf-8"); 
  5. echo mb_strlen($str); 
  6.  
  7. $str = mb_convert_encoding($str"latin1""gbk"); 
  8. echo mb_strlen($str); 

输出:

 
 
  1. 321 

因为我们的表的编码是utf8,所以65535 / 3 = 21845,这就是varchar能存放的最大长度了。
但当我试图将varchar的长度设置成21845时报错了,说长度超过最大长度了,为什么呢?

这是因为mysql对于变长类型的字段会有1-2个字节用来保存字符长度。
当字符数小于等于255时,mysql只用1个字节来记录,因为2的8次方减1只能存到255。
当字符数多于255时,就得用2个字节来存长度了。

所以实际上我们可用的字节数是65535 - 2 = 65533字节。
因此,varchar的最大长度只能到 65533 / 3 = 21844 余 1。

咦,还有一个字节没用到呢。
为了证明我们的确还有一个字节,我们可以往表中再建一个占1个字节的tinyint字段看看能不能成功。如下:

 
 
  1. mysql> explain table_test; 
  2. +-------+----------------+------+-----+---------+-------+ 
  3. | Field | Type           | Null | Key | Default | Extra | 
  4. +-------+----------------+------+-----+---------+-------+ 
  5. | abc   | varchar(21844) | NO   |     | NULL    |       | 
  6. | def   | tinyint(1)     | NO   |     | NULL    |       | 
  7. +-------+----------------+------+-----+---------+-------+

当我们试图将tinyint字段类型改为占2个字节的smallint时,mysql报错了。可见,我们的理解是正确的。

---------------------------华丽无敌的分隔线---------------------------

有了上面的铺垫,再来看我们的问题就简单了。

先了解一下各种数值类型所占的字节。

int 4字节
smallint 2字节
tinyint 1字节
decimal 变长

对于decimal类型我觉得需要详细的说一下,手册上说的不是很明白。

让我们先看一下英文手册的原文:

 
 
  1. Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes.  
  2. Storage for the integer and fractional parts of each value are determined separately.  
  3. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes.  
  4. The storage required for excess digits is given by the following table. 

官方的翻译如下:

 
 
  1. 使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。
  2. 每个值的整数和分数部分的存储分别确定。
  3. 每个9位数的倍数需要4个字节,并且“剩余的”位需要4个字节的一部分。
  4. 下表给出了超出位数的存储需求: 

下面这个表给出了剩余数字与字节长度的对应关系。

Leftover Digits Number of Bytes
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4

对于上面这段文字,我不知道你明不明白,反正我是没有明白。

我来举个例子,相信你就明白了:

提出一个问题:decimal(10,2)占几个字节?

要搞清楚这个问题,我们需要先弄清楚几个数字的含义。
10指的是整数与小数部分的总长度,2指的是小数部分的长度。
那么整数部分就只有10 - 2 = 8位了。
因为整数与小数的存储是各自独立确定的,所以他们各自所占空间的总和就是所占的总空间了。
对应上表可知,整数部分8位占了4个字节,小数部分2位占了1个字节,所以decimal(10,2)总共占了4 + 1 = 5个字节。

同理,如果是decimal(6,2),整数部分(6 - 2 = 4)位占2字节,小数部分2位占1字节,总共占3字节。

---------------------------华丽无敌的分隔线---------------------------

现在我们来算一下上面的表的varchar字段到底能存多少字符:

下表列出每个字段所占的字节数:

int(11) 4字节
int(11) 4字节
smallint(6) 2字节
tinyint(1) 1字节
decimal(10,2) 5字节

余下的字节数是: 65535 - (4 + 4 + 2 + 1 + 5) - 2 =  65535 - 16 - 2 = 65517。

65535是总字节数,括号内是除varchar字段外其他字段所占字节数,-2是字符串长度计数字节数,

因为是编码是utf8,所以字符数要除以3,65517 / 3 = 21839

这个数就是该表的varchar类型能存放的最大字符数了,这样我们就解答了本文开头提出的问题。

总结一下:

  • 1.mysql记录行的长度是65535字节;
  • 2.utf8编码占3字节,gbk编码占2字节,latin1编码占1字节;
  • 3.对于变长字段如varchar,mysql会用额外的字节来存储字符长度,255个字符以内用1个字节存,多于255个字符用2字节存;
  • 4.decimal类型的字段长度不固定,整数与小数部分所占字节数总和为总字节数,可以各自按表推算。 

参考文献:










本文转自 ustb80 51CTO博客,原文链接:http://blog.51cto.com/ustb80/1072001,如需转载请自行联系原作者
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
20天前
|
存储 关系型数据库 MySQL
MySQL 字段类型探究:深入理解 Varchar(50) 与 Varchar(500)
在MySQL数据库中,`VARCHAR`类型是一种常用的字符串存储类型,它允许定义一个可变长度的字符串。然而,`VARCHAR(50)`和`VARCHAR(500)`之间的差异不仅仅是长度的不同,它们在存储和性能方面也有显著的区别。本文将深入探讨这两种字段类型的区别,以及它们在实际应用中的选择。
62 3
|
20天前
|
存储 关系型数据库 MySQL
MySQL 字段类型深度解析:VARCHAR(50) 与 VARCHAR(500) 的差异
在MySQL数据库中,`VARCHAR`类型是一种非常灵活的字符串存储类型,它允许存储可变长度的字符串。然而,`VARCHAR(50)`和`VARCHAR(500)`之间的差异不仅仅是长度的不同,它们在存储效率、性能和使用场景上也有所不同。本文将深入探讨这两种字段类型的区别及其对数据库设计的影响。
35 2
|
2月前
|
存储 关系型数据库 MySQL
MySQL 字符字段长度设置详解:语法、注意事项和示例
MySQL 字符字段长度设置详解:语法、注意事项和示例
217 0
|
4月前
|
关系型数据库 MySQL
MySQL——删除指定字符
MySQL——删除指定字符
44 1
|
5月前
|
关系型数据库 MySQL
mysql模糊查询指定根据第几个字符来匹配
mysql模糊查询指定根据第几个字符来匹配
232 1
|
7月前
|
存储 关系型数据库 MySQL
MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%🚀
本篇文章来讨论MySQL字段的字符类型选择并深入实践char与varchar类型的区别以及在千万数据下的性能测试
MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%🚀
|
6月前
|
分布式计算 DataWorks 关系型数据库
DataWorks操作报错合集之在数据集成到MySQL时,遇到特殊字符导致的脏数据如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
7月前
|
存储 关系型数据库 MySQL
|
7月前
|
存储 关系型数据库 MySQL
深入理解MySQL中varchar和text的区别
在MySQL中,varchar和text都是用于存储文本数据的数据类型。varchar是可变长度字符串,存储时按实际长度分配空间,适合存储较短的、长度可变的字符串,如用户名。text类型用于存储大量文本,始终占用足够空间,适合文章内容。varchar在存储和查询时可能更快,可被索引,而text需特殊搜索技术。在数据库设计时,应根据存储需求和性能平衡选择。
632 0
|
19天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
33 1