当前位置:首页 > 文章列表 > 数据库 > MySQL > MySQL字符数据类型char与varchar的区别

MySQL字符数据类型char与varchar的区别

来源:SegmentFault 2023-01-21 21:12:18 0浏览 收藏

本篇文章给大家分享《MySQL字符数据类型char与varchar的区别》,覆盖了数据库的常见基础知识,其实一个语言的全部知识点一篇文章是不可能说完的,但希望通过这些问题,让读者对自己的掌握程度有一定的认识(B 数),从而弥补自己的不足,更好的掌握它。

数据类型差不多是接触mysql一开始就了解的内容,最近遇到几个现象如varchar自动转mediumtext,blob存储性能的问题,不得不回头明确一下关于MySQL常用数据类型的选择。

mysql手册这里 已经讲的很清楚了。它们都是定义字符串型字段时常用的类型,但它们存储和检索的方式有不同,最大长度和尾部的空格是否保留也有差别。

char
类型是使用固定长度空间进行存储,范围0-255。比如
CHAR(30)
能放30个字节,存放abcd时,尾部会以空格补齐,实际占用空间 30bytes 。检索它的时候尾部空格会被去除。

char善于存储经常改变的值,或者长度相对固定的值,比如type、ip地址或md5之类的数据,不容易产生碎片。关于它的效率可以参考这里

varchar
类型保存可变长度字符串,范围0-65535(但受到单行最大64kb的限制)。比如用
varchar(30)
去存放abcd,实际使用5个字节,因为还需要使用额外1个字节来标识字串长度(0-255使用1个字节,超过255需要2个字节)。

varchar善于存储值的长短不一的列,也是用的最多的一种类型,节省磁盘空间。update时varchar列时,如果新数据比原数据大,数据库需要重新开辟空间,这一点会有性能略有损耗,但innodb引擎下查询效率比char高一点。这也是innodb官方推荐的类型。

如果存储时真实长度超过了char或者varchar定义的最大长度呢?

  • 在SQL严格模式下,无论char还是varchar,如果尾部要被截断的是非空格,会提示错误,即插入失败

  • 在SQL非严格模式下,无论char还是varchar,如果尾部要被截断的是非空格,会提示warning,但可以成功

  • 如果尾部要被截断的是空格,无论SQL所处模式,varchar都可以插入成功但提示warning;char也可以插入成功,并且无任何提示

这里特意提到SQL的严格模式,是因为在工作中也遇到过一些坑,参考[MySQL的sql_mode严格模式注意点]()。

贴上官方的一个表格:

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

另外,mysql字段值比较时默认是不区分大小写的,这是由于他们的校对规则(一般是 utf8_general_ci)决定的,按字符比较,所以查询时 值尾部 的空格也是被忽略的,除非建表时对列指定

BINARY
(校对字符集变成utf8_bin)或者
select * from vc where binary v='ab  ';
,就会按字节比较,即比较时区分大小写和尾部空格。

需要注意的是,使用varchar不能因为长度可变就随意分大空间,比如90个字节能放够的列定义成varchar(200),因为开辟内存时是以200字节进行的,遇到需要filesort或tmp table作业可能会带来不利影响。

最后研究一下字符集对存储长度影响,以

create table tc_utf8(c1 int primary key auto_increment, c2 char(30), c3 varchar(N)) charset=utf8;
为例:

字符集为utf8,于是中文每个字符占3个字节,英文还是1个字节,所以N最大为 (65535-1-2-4-303)/3 = 21812,即最多能存放21812个英文、数字、汉字。其中65535是单行最大限制,减1是NULL标识位,减2的是头部的2个字节标识长度,减303的原因是char(30)占用90个字节,最后除以3还是因为utf8最长用3个字节表示一个字符。

但有人会说,utf8的英文字符只需要1个字节表示,并不占用3个字节,在存ASCII字符的情况下N是不是可以更大呢。答案是否定的,因为定义表的时候mysql事先并不知道c3要存的是英文还在中文,只能以最大来计。mysql也是以这种方式来确保行最大65535bytes限制:数据行只要出现一个ascii字符(如英文字母、数字),就永远达不到65535,数据行全中文则刚好满。

还有一种特殊情况:

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.12 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 rows in set (0.13 sec)

mysql> create table tc_utf8_21812(c1 int primary key auto_increment, c2 char(30), c3 varchar(21812)) charset=utf8;
Query OK, 0 rows affected (0.10 sec)

mysql> create table tc_utf8_21813(c1 int primary key auto_increment, c2 char(30), c3 varchar(21845)) charset=utf8;
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table tc_utf8_21846(c1 int primary key auto_increment, c2 char(30), c3 varchar(21846)) charset=utf8;
Query OK, 0 rows affected, 1 warnings (0.10 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1246 | Converting column 'c3' from VARCHAR to TEXT |
+-------+------+---------------------------------------------+
1 rows in set (0.14 sec)

即在非严格模式下,因为N=21813 > 21812,所以报 Row size too large 错误。但N=21846 > (65535/3)时,只是出现warnings,varchar自动变成了mediumtext 类型。

细心的朋友可能注意到上面开始我看了一下字符集

show variabels like "char%";
,因为接下来要说明另外一个问题:客户端字符集与database不一样的情况。

我们回到 N

CREATE TABLE `tc_utf8` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(30) DEFAULT NULL,
  `c3` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入一些数据:

mysql> set names utf8;
mysql> insert into tc_utf8(c2,c3) values('en_30',repeat('a',30));
Query OK, 1 rows affected (17.87 sec)

mysql> insert into tc_utf8(c2,c3) values('en_31',repeat('b',31));
Query OK, 1 rows affected, 1 warnings (0.10 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c3' at row 1 |
+---------+------+-----------------------------------------+
1 rows in set (0.14 sec)

mysql> insert into tc_utf8(c2,c3) values('zh_30',repeat('中',30));
Query OK, 1 rows affected (0.18 sec)

mysql> insert into tc_utf8(c2,c3) values('zh_31',repeat('文',31));
Query OK, 1 rows affected, 1 warnings (0.09 sec)

意料之中,汉字同样被截断

ysql> select c2,c3,length(c3),char_length(c3) from tc_utf8;
+-------+------------+-----------------+------------------------------------------------------------------------+
| c2    | length(c3) | char_length(c3) | c3                                                                     |
+-------+------------+-----------------+------------------------------------------------------------------------+
| en_30 |         30 |              30 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa                                         |
| en_31 |         30 |              30 | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb                                         |
| zh_30 |         90 |              30 | 中中中中中中中中中中中中中中中中中中中中中中中中中中中中中中 |
| zh_31 |         90 |              30 | 文文文文文文文文文文文文文文文文文文文文文文文文文文文文文文 |
+-------+------------+-----------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

上面的en_30代表insert的时候存入30英文字符。可以看到30个a占用30个字节,30个汉字占用90个字节,大于30的会被截断,证实了文章一开头的说法。

mysql> set names latin1;

mysql> insert into tc_utf8(c2,c3) values('zh_30_latin1',repeat('中',30));
Query OK, 1 rows affected, 1 warnings (0.10 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c3' at row 1 |
+---------+------+-----------------------------------------+
1 rows in set (0.14 sec)

mysql> insert into tc_utf8(c2,c3) values('zh_10_latin1',repeat('中',10));
Query OK, 1 rows affected (0.10 sec)

mysql> insert into tc_utf8(c2,c3) values('zh_10_latin1',repeat('文',10));
Query OK, 1 rows affected (0.11 sec)

mysql> insert into tc_utf8(c2,c3) values('zh_11_latin1',repeat('文',11));
Query OK, 1 rows affected, 1 warnings (0.12 sec)

截断

上面的实验显示,db table是utf8,但客户端连接时使用latin1,在非严格模式下 varchar(30) 只能存10个汉字,多余的尾部被截断了

我们来看一下占用字节的情况:(2,3行的乱码是意料之中的)

mysql> select c1,c2,c3,length(c3),char_length(c3) from tc_utf8;
+----+--------------+--------------------------------+------------+-----------------+
| c1 | c2           | c3                             | length(c3) | char_length(c3) |
+----+--------------+--------------------------------+------------+-----------------+
| 1  | en_30        | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | 30         | 30              |
| 2  | en_31        | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb | 30         | 30              |
| 3  | zh_30        | ?????????????????????????????? | 90         | 30              |
| 4  | zh_31        | ?????????????????????????????? | 90         | 30              |
| 5  | zh_30_latin1 | 中中中中中中中中中中 | 60         | 30              |
| 6  | zh_10_latin1 | 中中中中中中中中中中 | 60         | 30              |
| 7  | zh_10_latin1 | 文文文文文文文文文文 | 80         | 30              |
| 9  | zh_11_latin1 | 文文文文文文文文文文 | 80         | 30              |
+----+--------------+--------------------------------+------------+-----------------+
8 rows in set (0.14 sec)

看到char_length函数算出的中、英文字符个数都是30,但一个“中”占6字节,一个“文”占8字节,是不是很诧异,这中间有数次的编码转换过程,有兴趣 可以参考 http://mysql.rjweb.org/doc.php/charcoll ,是可以模拟出来的。

在严格模式下就没这么复杂了,所以尽量使用 STRICT_TRANS_TABLES ,避免意外的情况带入生产环境。早期设计的时候就要保持客户端与数据库字符集一致。

参考


本文链接地址:http://seanlook.com/2016/04/28/mysql-char-varchar-set/


以上就是《MySQL字符数据类型char与varchar的区别》的详细内容,更多关于mysql的资料请关注golang学习网公众号!

版本声明
本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
MySQL的windows安装命令行安装版本和基本使用方法MySQL的windows安装命令行安装版本和基本使用方法
上一篇
MySQL的windows安装命令行安装版本和基本使用方法
使用docker分离服务与数据库
下一篇
使用docker分离服务与数据库
查看更多
最新文章
查看更多
课程推荐
  • 前端进阶之JavaScript设计模式
    前端进阶之JavaScript设计模式
    设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
    542次学习
  • GO语言核心编程课程
    GO语言核心编程课程
    本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
    508次学习
  • 简单聊聊mysql8与网络通信
    简单聊聊mysql8与网络通信
    如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
    497次学习
  • JavaScript正则表达式基础与实战
    JavaScript正则表达式基础与实战
    在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
    487次学习
  • 从零制作响应式网站—Grid布局
    从零制作响应式网站—Grid布局
    本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
    484次学习
查看更多
AI推荐
  • 茅茅虫AIGC检测:精准识别AI生成内容,保障学术诚信
    茅茅虫AIGC检测
    茅茅虫AIGC检测,湖南茅茅虫科技有限公司倾力打造,运用NLP技术精准识别AI生成文本,提供论文、专著等学术文本的AIGC检测服务。支持多种格式,生成可视化报告,保障您的学术诚信和内容质量。
    19次使用
  • 赛林匹克平台:科技赛事聚合,赋能AI、算力、量子计算创新
    赛林匹克平台(Challympics)
    探索赛林匹克平台Challympics,一个聚焦人工智能、算力算法、量子计算等前沿技术的赛事聚合平台。连接产学研用,助力科技创新与产业升级。
    50次使用
  • SEO  笔格AIPPT:AI智能PPT制作,免费生成,高效演示
    笔格AIPPT
    SEO 笔格AIPPT是135编辑器推出的AI智能PPT制作平台,依托DeepSeek大模型,实现智能大纲生成、一键PPT生成、AI文字优化、图像生成等功能。免费试用,提升PPT制作效率,适用于商务演示、教育培训等多种场景。
    58次使用
  • 稿定PPT:在线AI演示设计,高效PPT制作工具
    稿定PPT
    告别PPT制作难题!稿定PPT提供海量模板、AI智能生成、在线协作,助您轻松制作专业演示文稿。职场办公、教育学习、企业服务全覆盖,降本增效,释放创意!
    53次使用
  • Suno苏诺中文版:AI音乐创作平台,人人都是音乐家
    Suno苏诺中文版
    探索Suno苏诺中文版,一款颠覆传统音乐创作的AI平台。无需专业技能,轻松创作个性化音乐。智能词曲生成、风格迁移、海量音效,释放您的音乐灵感!
    57次使用
微信登录更方便
  • 密码登录
  • 注册账号
登录即同意 用户协议隐私政策
返回登录
  • 重置密码