mysql基本数据类型

概述

要想学好mysql,了解其支持的基本数据类型以及内部原理是极为重要的,只有这样,我们才能根据不同的业务要求来选择不同的数据类型,实现最佳的存储效果和查询性能,因而本文就着重总结一下mysql支持的数据类型以及内部的存储原理。

总体来说,mysql一共分成了四类:数值类型、日期和时间类型、字符串类型、二进制类型等。

数值类型

数值类型是最为基础的类型,在业务开发中存储递增主键ID、金额、数量等属性时,都会经常选择数值类型来进行存储。整体上将数值类型分为整形、浮点型和定点数类型三类,其中整型对应我们现实生活中常用的整数类型;而浮点型则是对应现实生活中的小数类型;定点数类型则是为了存储精确的小数而被设计出来的。

整型

整型之中,根据是否有符号又被分为无符号数和有符号数,同时不同存储空间以及整数表示范围的考量,整数类型又被分成了 TINYINTSMALLINTMEDIUMINTINT以及 BIGINT五类,各个类别的类型以及所占空间和含义如下表所示:

类型 占用的存储空间 无符号数的取值范围 有符号数的取值范围 含义 TINYINT 1字节 0~$2^8 $
(-2^{7})(2^{7}-1)

非常小的整数 SMALLINT 2字节 0~
(2^{16}-1)(-2^{15})(2^{15}-1)

小的整数 MEDIUMINT 3字节 0~
(2^{24}-1)(-2^{23})(2^{23}-1)

中等大小的整数 INT(别名INTEGER) 4字节 0~
(2^{32}-1)(-2^{31})(2^{31}-1)

标准的整数,跟Java中的int类型相同 BIGINT 8字节 0~
(2^{64}-1)(-2^{64})(2^{64}-1)

大整数

具体使用时,为某个变量设定变量类型时加上对应的类型关键字即可;在区分无符号数以及有符号数时,则需要加上 UNSIGINED关键字来加以区分,加上该关键字后,则表示使用的无符号数,比如 TINYINT UNSIGNED表示的就是无符号TINYINT类型的数。

浮点型

浮点型主要用来存储小数,其实现和存储范围和我们在其他编程语言中学习的类型,分为单精度浮点型(FLOAT)和双精度浮点型(DOUBLE)两类,两者的存储空间和表示范围如下表所示:

类型 占用的存储空间 绝对值最小的非0值 绝对值最大值 含义 FLOAT 4个字节 ±1.175494351E-38 ±4.402823466E+38 单精度浮点型 DOUBEL 8字节 ±2.22507385072014E-308 ±1.7976931348623157E+3008 双精度浮点型

关于具体浮点数的内存存储原理和存储范围的确定,有兴趣的学生可以参考上一篇文章《我在这里不再重复》。

[En]

For the determination of the internal storage principle and storage range of specific floating-point numbers, interested students can refer to a previous article, “” I will not repeat it here.

从表中可以看到浮点型数据可以将大量十进制小数转成二进制进行存储,但实际存储过程中许多小数存在误差,即 存在精度损失。为了解决该问题,在MYSQL中引入了来进行实现对小数的精确存储。

定点数类型

作为一种精确存储小数的方法,定点类型的设计思想和存储原理与浮点数有很大的不同。定点类型的具体结构如下表所示:

[En]

As a way to accurately store decimals, the design idea and storage principle of fixed-point type are quite different from floating-point numbers. The specific structure of fixed-point type is shown in the following table:

类型 占用的存储空间 取值范围 DECIMAL(M,D) 取决于M和D的值 取决于M和D的值

从表中可以看出,定点类型的数字由两部分组成:

[En]

It can be seen from the table that a fixed-point type of number consists of two parts:

  • M:表示该小数最多包含的有效数字个数。比如2.3有效数字个数为2;0.2,有效数字个数为1
  • D:表示该小数保留小数点后十进制数字的个数,简单来讲就是 小数的位数。比如2.3中D的值为1;8.321中D的值为3。

为了保证定点数不丢失小数精度,在存储过程中采用如下存储策略:

[En]

In order to ensure that the fixed-point number does not lose the decimal accuracy, the following storage strategy is adopted during storage:

将十进制小数用小数点分隔开,分别把小数点左右的两个十进制整数存储起来

比如存储8.32时,分别将8和32分开存储,这样就相当于保存了8.32这一精确的小数。

具体使用时,不同的M和D值会影响到存储的小数范围,本着”能少用存储空间就少用存储空间”的原则,mysql在设计时,采用如下策略来对DECIMAL(M,D)数据类型分配存储空间,存储数据。我们以DECIMAL(16,4)为例:

  • 第一步 划分位数:首先按照M和D的大小来划分,整数位和小数位,在本例中,总的有效数字位数为16,可存储的小数位数为4,可存储的整数位数为12。划分的示意图如下图所示:(注意在本图中,每一个方格代表的是十进制位而不是二进制位。)

mysql基本数据类型
  • 第二步 分组:从小数点位置开始,分别向两边进行分组操作,将每个整数每隔9个十进制位划分成一组,划分结果如下图所示:

mysql基本数据类型
从图中可以看到,在分组时,如果不够9个十进制位,那么最终也会被单独划分成一组,比如第一组和第三组。
  • 第三步 转换二进制:针对每个组中的十进制数字,分别将其转成二进制数字进行存储。为了有效利用存储空间,在存储时组中包含的十进制数字位数同步,则所占用的存储空间也不同,具体对应关系表如下:

组中包含的十进制位数 占用存储空间 备注 1或2 1字节 最多需要存储十进制数99,一个字节可存储范围为(-128,127)满足要求 3或4 2字节 最多需要存储十进制数9999,两个字节可存储范围为(-32,768,32,767)满足要求 5或6 3字节 最多需要存储十进制数999,999,三个字节可存储范围为(-8,388,608,8,388,607)满足要求 7或8或9 4字节 最多需要存储十进制数999,999,999,4个字节可存储范围(-2,147,483,648,2,147,483,647)满足要求

从表中可以看出,在选择存储位数时,可表示的存储范围实际上是大于需要表示的数字范围的,因此此种存储方案是可行的的。当然有的小伙伴会问了,似乎这样存储是有空间浪费的?这也是没办法的事情,只能通过牺牲空间来缓存存储精度了,在工程实践中经常也会有这种tradeoff 🐶~~

  • 第四步 最高位置为1:按照上述方式转换完成后将二进制序列的最高位设置成1(这一步有其他用途,具体用途此处先~), 然后将对应数据存储

此处以存储小数1234567890.1234为例:

  1. 首先将该十进制数进行划分,分成如下三组:

1 234567890 1234

其中前两组是整数部分,最后一组是小数部分。第一组存储整数1;第二组存储整数2345678890;第三组存储1234;
  1. 分别将每一组的十进制数转成二进制,为了便于表示此处均用16进制来表示,最终三组数的结果如下:

0x0001 0x0DFB38D2 0x04D2

将三组16进制数连接起来之后的结果为 0x00010DFB38D204D2

  1. 将最高位设置为1,获取到的最终的待存储的16进制数为 0x80010DFB38D204D2
  2. 最终将该数据按照对应关系表来进行存储,最终的存储情况如下图所示:

mysql基本数据类型

当然,这个时候可能会有朋友问,如果我存了负数怎么办?例如,您需要存储-1234567890.1234商店,而不是存储1234567890.1234。

[En]

Of course, at this time, some friends may ask, what if I store a negative number? For example, instead of storing 1234567890.1234, you need to store * -1234567890.1234stores .*

此时只需要将1234567890.1234对应的二进制存储数 0x80010DFB38D204D2每一位都对应执行取反操作即可,即得到 0x7FFEF204C72DFB2D即可。

从上述内容中,我们可以得知,DECIMAL数据类型在存储数据时,其存储精度和范围是可选的根据D和M值的变化而变化,而且不同的D值和M值最终实际占用的空间也是不同的。

在默认情况下,M的值为10,D的值为0;另外DECIMAL数据类型能够表示的数据范围也不是无限大的,其中允许M的最大值为65,D的最大值为30,而且D的值是小于等于M的值。

日期和时间类型

在许多业务场景中,我们需要在数据库中存储日期和时间等信息,这需要日期和时间格式来存储。

[En]

In many business scenarios, we need to store information such as date and time in the database, which requires a date and time format for storage.

MySQL提供了多种日期和时间类型,各种类型的表示范围和占用空间情况如下表所示:

类型 占用的存储空间(字节) 取值范围 含义 YEAR 1 1901~2155 年份值 DATE 3 ‘1000-01-01’~’9999-12-31 日期值 TIME 3 ‘-838:59:59’~’838:59:59’ 时间值 DATETIME 8 ‘1000-01-01 00:00:00’~ ‘9999-12-31 23:59:59’ 日期和时间值 TIMESTAMP 4 ‘1970-01-01 00:00:01’~ ‘2038-01-19 03:14:07’ 时间戳

字符串类型

在使用数据库过程中,我们最为常用的类型应该就是字符串类型。在MySQL中为了应对不同的应用场景,在设计字符串时,提供了多种类型可供选择:

类型 最大长度 存储空间要求 含义 CHAR(M) M个字符 M × W字节 固定长度的字符串 VARCHAR(M) M个字符 L+1或者L+2字节 可变长度的字符串 TINYTEXT
(2^8-1)

字节 L+1字节 非常小型的字符串 TEXT
(2^{16}-1)

字节 L+2字节 小型的字符串 MEDIUMTEXT
(2^{24}-1)

字节 L+3字节 中等大小的字符串 LONGTEXT
(2^{32}-1)

字节 L+4字节 大型的字符串

其中M表示该数据类型最多能存储的字符数量,M表示在特定字符集下编码一个字符需要的字节数;L代表实际向该类型的列中存储字符串在特定字符集下所占的字节数。

CHAR(M)类型

CHAR(M)中的M表示最多可以存储的字符数量。其取值范围为0~255,默认值是1。其中CHAR(0)是一种比较特殊的类型只能存储空字符串”或者NULL值。

在不同的字符集编码情况下,CHAR(M)的所占用的存储空间是不同的。如果在某个字符集编码类型下,存储一个字符需要W个字节则针对该编码情况下CHAR(M)类型所占有的存储空间便为M×W字节。比如在ASCII字符集下的CHAR(5)类型,ASCII字符集编码一个字符需要1个字节,因此在该编码集下,CHAR(5)类型占用的存储空间为5×1=5字节。

如果实际存储的字符串在特定编码集下占用的存储空间小于M×W字节,剩余空间则会用空格字符(也就是’ ‘)来进行填充。比如表中某一列的值使用ASCII字符集下面的CHAR(5)类型,当我们想要将字符串’abc’存储到该列时需要实际占用的存储空间由3字节小于该类型下的5字节存储空间,因而会把剩余的2个字节用空格字符进行填充。

很明显,这种存储方式下,当M非常大,而实际存储的字符却比较短时,会对存储空间有较大浪费。

VARCHAR(M)

在实际业务开发过程中,某个列存储的字符串它的大小是长短不一的,如果直接使用CHAR(M)类型存储会有较大的空间开销,因而MySQL中提供了VARCHAR类型来解决该问题。

VARCHAR(M)中的M也表示最多可以存储的字符数量,理论上能够存储的范围为1~65535。但MySQL中规定表的 一行数据占用的存储空间总共不得超过65535个字节,也就是说VARCAHR(M)类型实际能够容纳的字符串数量是小于65535的。

在实际存储时VARCHAR(M)类型的数据实际占用的存储空间是不确定的,需要针对不同情况进行分析。为什么这样说呢?因为VARCHAR(M)类型所对应的存储空间实际上是由如下两部分构建成的:

  • 第一部分:真正的字符串内容。具体大小跟存储的长度和所用编码方式有关,此处假定采用特定字符编码后所占用的字节数为L。
  • 第二部分:额外占用的字节数。这部分字节主要用来记录VARCHAR(M)类型字符串在存储时所占用的字节长度,即时L的2进制表示数。 这部分额外占用的字节数跟字符串实际存储的字符串长度以及编码方式有关。此处采用的编码方式下,存储一个字符所用字节数为W则有L=M×W。那么额外字节数为:
  • 当L < 256,1个字节便可以存储L的二进制数,需要 1个字节来表示所占用的字节数量。此时,整个VARCHAR(M)类型所占用的字节数量为 L+1个字节。
  • 当L >=256时,需要 2个字节来表示所占用的字节数量。此时,整个VARCHAR(M)类型所占用的字节数量为 L+2个字节。

    1个字节占用8bit,能表示的最大无符号数为(2^8-1)即255。

其他TEXT类型

除了VARCHAR(M)类型外,MySQL提供了TINYTEXT、TEXT、MEDIUMTEXT以及LONGTEXT这4种可以存储可变长度的字符串。这些TEXT类型规定最大的存储长度,可以让我们根据实际的业务需要进行选择。

  • TINYTEXT:最多可以存储(2^8-1)字节,其中额外需要占用1个字节来存储长度。
  • TEXT:最多存储(2^{16}-1)字节,其中额外需要2个字节来存储长度。
  • MEDIUMTEXT:最多存储(2^{24}-1)字节,其中额外需要3个字节来存储长度。
  • LONGTEXT:最多存储(2^{32}-1)字节,其中额外需要4个字节来存储长度。

在章节中说到,”MySQL中规定表的 一行数据占用的存储空间总共不得超过65535个字节“,但这一规定对TEXT类型的数据不起作用,因而当表中的数据较长时,可以考虑选择TEXT类型来进行存储。

枚举和集合类型

枚举(ENUM)类型和集合(SET)类型是我们在其他编程语言中常见的类型,比如当我们定义性别字段的类型时便可以采用枚举类型,保证这一列只能填”男”或者”女”。其使用方式为 ENUM('str1','str2',...)

SET类型则标出该列中的值可以从给定字符串列表中选择一个或者多个。比如针对兴趣列可以定义为(‘吃饭’,’睡觉’,’打豆豆’),这样兴趣列中的值则只能在这三个字符串中选择一个或者多个。

总的来说ENUM和SET类型都是一种特殊的字符串类型,从字符串列表中选择单个值或者多个值时可能会用到,此处不再详述。

二进制类型

在一些特殊的应用场景中,我们需要存储二进制位,所以我们使用二进制类型。一般来说,二进制类型分为四种类型:

[En]

In some special application scenarios, we need to store binary bits, so we use binary types. Generally speaking, binary types are divided into four types:

- 二进制类型
    - BIT类型
    - BINAR(M)
    - VARBINARY(M)
    - BLOB类型

BIT类型

BIT类型主要是用来存储单个或者多个二进制位所用到的类型:

类型 占用的存储空间 含义 BIT(M) 近似为(M+7)/8 存储M个二进制位的值

其中M的取值范围为1~64,而且M可以省略,它的默认值为1。

由于计算机在存储数据的时候都是按照字节来进行存储,因而当使用BIT类型时,如果存储的比特数不够整数个字节,则MySQL会对其进行填充,保证是整数个字节,比如:

  • BIT(1):仅仅存储一个字节,但实际占用空间为(1+7)/8=1字节,填充了7bit。
  • BIT(2):仅仅存储了一个字节,但实际占用空间(2+6)/8=1字节,填充了6bit。
  • BIT(9):仅仅存储了9个字节,但实际占用空间(9+7)/2=2字节,填充了7bit。

BINARY(M)与VARBINARY(M)

这两种类型与上一章节中的CHAR(M)和VARCHAR(M)类型对应关系相同,不同点在于CHAR(M)和VARCHAR(M)使用来存储字符而BINARY(M)与VARBINARY(M)则是用来存储二进制数。

BLOB类型

与TEXT类型类似,BLOB类型也分成了TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB类型四种。主要用来存储字节。常用来存储不是非常大的图片、音频和视频数据。如果比较大的二进制类型数据,一般不直接存储到数据库中,而是存储到文件系统中,在数据库中仅仅存放其文件路径。

总结

本文总结了在MySQL数据库中常用的数据类型及其相关原理,在MySQL中总体上支持的数据类型有数值类型、字符串类型、二进制类型三大类,分别用于存储三类常见的数据。

Original: https://www.cnblogs.com/goWithHappy/p/mysql_data_type.html
Author: vcjmhg
Title: mysql基本数据类型

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505482/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 关于form表单action属性的问题

    通过另一个jsp表单的action跳转到当前jsp undefined* 通过servlet跳转到当前jsp,也就是通过请求转发 <form action="fir…

    数据库 2023年6月16日
    0118
  • Guava中的封装的Map操作

    引入依赖 <dependency> <groupId>com.google.guavagroupId> <artifactId>guava…

    数据库 2023年6月14日
    0196
  • MySQL高可用安装

    MySQL HA部署 环境准备 创建本地yum源 确认关闭 SELinux 防火墙设置 MySQL安装 使用 root 用户操作创建相关的用户组和用户 上传/解压介质 设置自启动 …

    数据库 2023年6月16日
    0103
  • linux学习之联网问题解决

    (centos7)linux隔日重启后发现无法联网解决方案 1.运行命令 ip addr 查看 ip地址 2.运行命令 vi /etc/sysconfig/network-scri…

    数据库 2023年6月16日
    0132
  • 如何构建你自己的计算机网络知识体系?

    大家好,我是小牛肉,不知道各位曾经有没有和我一样的困惑,就是有些知识好像已经看了好多遍了,但是知识点在脑子中是分散的,没办法串联起来,别人问一个问题我能答出来一点,但是你让我自己从…

    数据库 2023年6月6日
    0127
  • 动手实验查看MySQL索引的B+树的高度

    一:文中几个概念 h:统称索引的高度;h1:主键索引的高度;h2:辅助索引的高度;k:非叶子节点扇区个数。 二:索引结构 叶子节点其实是双向链表,而叶子节点内的行数据是单向链表,该…

    数据库 2023年6月14日
    0156
  • 手写spring的ioc的流程截图(笔记-1)

    spring ioc是什么? IoC 容器是 Spring 的核心,也可以称为 Spring 容器。Spring 通过 IoC 容器来管理对象的实例化和初始化,以及对象从创建到销毁…

    数据库 2023年6月6日
    0119
  • 3 访问修饰符public,private,protected以及不写(默认)时的区别

    private 私有的,只对本类公开。 default 类的成员不写访问修饰符时默认为default,默认对于同一个包中的其它类相当于公开(public),对于不是同一个包中的其它…

    数据库 2023年6月6日
    0157
  • 博客怎么写才能更安全和简洁

    前言 博客实现本地存储 Markdown语法的介绍 博客对于我们普通人来说就是为了更好的去实现个人知识的一个整理融合然后把知识共享可以帮助其他去实现自己的一些工作或者学习中的一些疑…

    数据库 2023年6月6日
    0135
  • java Script

    JavaScript JavaScript(简称”JS”)是一种具有函数优先的轻量级,解释型或即时编译型的高级编程语言,弱类型,脚本语言 三大部分 核心(E…

    数据库 2023年6月16日
    0125
  • SQL与数据库编程学习笔记-day2

    SQL与数据库编程学习笔记-day2 修改数据库密码; – 登出数据库(修改数据库密码必须在数据库外执行命令); * – Ps:登出命令:quit* 操作完…

    数据库 2023年5月24日
    0121
  • python-django框架中使用七牛云

    1:注册七牛云账号 https://www.qiniu.com/ js文件 链接:https://pan.baidu.com/s/1BW1svHqEsXrrTNtRobKkpg 提…

    数据库 2023年6月6日
    0148
  • 分享一个有意思的错误

    subList方法拆分集合问题 JAVA技术交流群:737698533 分享一个有意思的错误,先看代码 public static void main(String[] args)…

    数据库 2023年6月16日
    0154
  • python基础(待补充)

    第一篇:计算机的基础知识 编程语言的介绍 计算机介绍和五大组成 平台与软件跨平台介绍 CS、BS架构和网络通信协议 操作系统的介绍 cpu详解 存储器详解 操作系统启动流程和BIO…

    数据库 2023年6月14日
    0149
  • apk生成多渠道的安装包

    一、前言->需求 最近公司的项目需要试上线,安卓包会放到多个渠道进行推广,玩家会进行下载安装登录,后台为了得到渠道包的下载使用数据,就会给每个渠道包加入了不同的渠道ID以便统…

    数据库 2023年6月14日
    0161
  • MySQL之多表查询、Navicat及pymysql

    一、多表查询 1.1 数据准备 — 建表 create table dep( id int primary key auto_increment, name varchar(20…

    数据库 2023年5月24日
    0137
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球