MySQL中的列类型之字符串类型总结

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL中的列类型之字符串类型总结

在SQL中,将字符串类型分为了六类:char,varchar,text,blob,enum和set。微信截图_20231123213404.png

【1】定长字符串char

磁盘(二维表)在定义结构的时候,就已经确定了最终数据的存储长度。

Char(M):M代表length,可以存储的长度,单位为字符,最大长度值可以为255。

char(4):在UTF8环境下,需要43=12个字节存储空间;GBK编码环境下需要42=8个字节存储空间(在java中使用两个字节来表示一个字符)。

Java基本类型占用的字节数:

1字节: byte , boolean

2字节: short , char

4字节: int , float

8字节: long , double

注:1字节(byte)=8位(bits)

【2】变长字符串varchar

变长字符串在分配空间的时候按照最大长度的空间分配,实际用了多少根据具体的数据来定。

Varchar(M) : M表示字符长度,理论可以存储65536个字节。会多出1-2个字节来确定存储的实际长度。UTF-8下varchar(M)中M最大为21845。GBK下M最大为32767。

不要忘记了,varchar需要留有字节储存长度;如果是21845,那么对应字节为:21845*3+2=65537>65535! **所以 UTF8下,varchar实际最大长度为21844 , GBK:32766(实际值不是32767哦)

Varchar(10):储存10个汉字,UTF8下使用10*3+1=31个字节

在生产环境中如何选择定长或者变长字符串类型呢?

定长的磁盘空间比较浪费,但是效率高:如果数据长度基本一样,就选择定长;如身份证,手机号码等。

变长的磁盘空间比较节省,但是效率低:如果数据不能确定长度(不同数据长度有变化),如姓名,地址等。

MySQL记录中,如果有任何一个字段允许为空,那么系统会自动从整个记录中保留一个字节来存储null。如果想释放该字节,那么所有字段设置 not null(无论有多少个字段允许为空,只会保留一个字节去存储null)。

【3】文本字符串

如果数据量超过255个字符,通常会使用文本字符串。

文本字符串根据存储的数据格式进行分类:

  • Text:存储文字;
  • Blob:存储二进制数据,如图片、音频等。

通常二进制数据实际上数据库存储路径,物理文件放在磁盘上面。

对于经常变更的数据来说,CHAR比VARCHAR更好,因为CHAR不容易产生碎片。对于非常短的列,CHAR比VARCHAR在存储空间上更有效率。

使用时要注意只分配需要的空间,更长的列排序时会消耗更多内存。尽量避免使用TEXT/BLOB类型,查询时会使用临时表,导致严重的性能开销。

MySQL中text文本字符串不占用记录长度,额外存储(正确理解应是文本字符串的数据内容额外存储,不占用记录长度)。但是text文本字符串也是记录的一部分,那么一定需要占用记录的部分长度–10个字节(保存数据的地址与长度)!

【4】枚举

枚举:enum,事先将所有出现的结果都设计好,实际存储的数据必须是预先规定的数据的一个。

  • ① 规范数据格式,数据只能是规定的数据中的其中一个;

 

  • ② 节省存储空间,枚举通常有一个别名-单选框;枚举实际存储的是数值而不是字符串本身。
  • ③ 如果列表成员为1~255,则需要1个字节存储;如果列表成员为255~65535,则需要2个字节存储;最多65535个成员!
create table my_enum(
gender enum('男','女','保密')
)charset utf8;
desc my_enum;


aa14c8a9864249cba739fdc7d5632b80.png

测试如下:

insert into my_enum VALUES('男');--有效数据
insert into my_enum VALUES('人妖');--无效数据

7a97852d8c7b4abb9415368bdbfe8295.png

Tips:

在MySQL中,系统也是自动转换数据格式的,尤其是字符串转数字。如字符串若非数字,将转换为0;若以数字开头则转换为对应的开头数字。

select 1+'hello',1+'1hello',1+'2hello','1.0hello',1+'1.1hello',1+'1.1.1hello',1+'1.2.1hello';

b65309f1df694955accc74a310970d22.png

查看一下刚才插入的数据在MySQL是如何存储的:

select gender, gender+0 from my_enum 

eac507555b624e92824a6c7a6b476b87.png

枚举原理

枚举在进行数据规范的时候(定义的时候),系统会自动建立一个数字与枚举元素的对应关系(关系放到日志中)。

在进行数据插入的时候,系统自动将字符转换成对应的数字进行存储;然后在进行数据提取的时候,系统将其转换为字符进行显示。

枚举插入数字:

insert into my_enum VALUES('1'),('2'),('3');--会插入'关系'中对应的字符串,存储的是字符串对应的数字;
select gender, gender+0 from my_enum 

fe66336338f64e068ac5361fd8f0b23b.png

【5】Set集合字符串

集合跟枚举很相似,实际存的是数字而不是字符串本身(集合是多选),区别与枚举,别名可以称为复选框!!!

Set(元素列表),可以使用元素列表中的多个元素,元素之间可以使用逗号分隔。

说明:1,2,3,4,8 ;分别对应tinyint,smallint,mediumint,int,bigint。在底层存储的时候,使用整型存储的(存储字符对应的数字,而不是字符本身,参考enum)。

如有8个元素,那么使用1个子节存储(tinyint);16个元素使用两个字节存储(smallint);33个元素使用8个字节存储(bigint)。远比varchar或者char占用磁盘空间少得多。

如果是varchar,8个元素,那么需要24+1=25个字节;但是set只需要1个字节。

create table my_set(
hobby SET('篮球','足球','乒乓球','羽毛球','排球','台球','网球','棒球')
)charset utf8;
DESCRIBE my_set


476a302c7adf48e69b08488fa16369f8.png

insert into my_set VALUES('足球,篮球');--插入的时候不用考虑顺序,服务器存储的时候会自动寻找对应位置,看下面分析。。
insert into my_set VALUES('足球');
insert into my_set VALUES('足球篮球');--无效数据,未用逗号分隔
insert into my_set VALUES('小球,篮球');--不存在的数据
insert into my_set VALUES(4);--直接插入数字
.....
select hobby,hobby+0 from my_set ORDER BY hobby ASC;


0e63c44e759f47538b85fcbd15d58df9.png

是不是存的数字出乎意料?不再是1,2,3。。。

分析如下图:

那么插入数字255时(因为八个位全为1),对应全部字符串:

insert into my_set VALUES(255);
select hobby,hobby+0 from my_set ORDER BY hobby ASC;

c2bcb7795a1a445cb35493904ad83275.png

set如此节省磁盘空间,但是生产中并未常使用!因为效率优先。所以enum和set使用频率并不如varchar高!!!

和Enum类型类似,里面可以保存0~64个成员。和Enum类型最大的区别是:SET类型一次可以选取多个成员,而Enum只能选一个。根据成员个数不同,存储所占的字节也不同。微信截图_20231123214153.png


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
分布式计算 DataWorks MaxCompute
DataWorks产品使用合集之需要将mysql 表(有longtext类型字段) 迁移到odps,但odps好像没有对应的类型支持,该怎么办
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
7天前
|
DataWorks 关系型数据库 MySQL
DataWorks产品使用合集之用脚本的方式同步数据到MySQL,怎么指定列作为目标表为唯一行
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
24天前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版操作报错之在处理MySQL的DECIMAL类型时出现了报错,是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
6天前
|
分布式计算 DataWorks MaxCompute
DataWorks产品使用合集之新创建的工作空间,任务提交了,但是周期实例里面没任何数据,是什么导致的
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
10天前
|
关系型数据库 MySQL 数据库
MySQL数据库——触发器-案例(Insert类型、Update类型和Delete类型)
MySQL数据库——触发器-案例(Insert类型、Update类型和Delete类型)
16 0
|
1月前
|
存储 关系型数据库 MySQL
9.2.5.2 【MySQL】XDES 类型
9.2.5.2 【MySQL】XDES 类型
19 0
9.2.5.2 【MySQL】XDES 类型
|
1月前
|
存储 关系型数据库 MySQL
MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%🚀
本篇文章来讨论MySQL字段的字符类型选择并深入实践char与varchar类型的区别以及在千万数据下的性能测试
MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%🚀
|
1月前
|
关系型数据库 MySQL Linux
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)
|
1天前
|
SQL 存储 关系型数据库
深入理解MySQL:数据库管理与性能优化
第一章:MySQL基础 MySQL概述:简要介绍MySQL的历史、特点和应用领域
|
1天前
|
SQL 关系型数据库 MySQL
精通MySQL:从数据库管理到性能优化
第一章:MySQL入门 MySQL简介:了解MySQL的起源、发展历程以及在Web开发中的重要性

推荐镜像

更多