MySQL 数据类型剖析

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 数据类型剖析

1. MySQL中的数据类型


类型 类型举例
整数类型 TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型 FLOAT、DOUBLE
定点数类型 DECIMAL
位类型 BIT
日期时间类型 YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型 ENUM
集合类型 SET
二进制字符串 类型 BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型 JSON对象、JSON数组
空间数据类型 单值类型:GEOMETRY、POINT、LINESTRING、POLYGON;
集合类型:MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

常见数据类型的属性,如下:

MySQL关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增,适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集


2. 整数类型


2.1 类型介绍


整数类型一共有 5 种,包括 TINYINTSMALLINTMEDIUMINTINT(INTEGER)BIGINT

它们的区别如下表所示:


2.2 可选属性


2.2.1 M


M : 表示显示宽度,M的取值范围是(0, 255)。例如,int(5):当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ ZEROFILL ”使用,表示用“0”填满宽度,否则指定显示宽度无效。

如果设置了显示宽度,那么插入的数据宽度超过显示宽度限制,会不会截断或插入失败?

答案:不会对插入的数据有任何影响,还是按照类型的实际宽度进行保存,即 显示宽度与类型可以存储的值范围无关 。从MySQL 8.0.17开始,整数数据类型不推荐使用显示宽度属性。


整型数据类型可以在定义表结构时指定所需要的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值。


2.2.2 UNSIGNED


UNSIGNED : 无符号类型(非负),所有的整数类型都有一个可选的属性UNSIGNED(无符号属性), 无符号整数类型的最小取值为0。所以,如果需要在MySQL数据库中保存非负整数值时,可以将整数类 型设置为无符号类型。

int类型默认显示宽度为int(11),无符号int类型默认显示宽度为int(10)。


2.2.3 ZEROFILL


ZEROFILL : 0填充,(如果某列是ZEROFILL,那么MySQL会自动为当前列添加UNSIGNED属性),如果指定了ZEROFILL 只是表示不够M位时,用0在左边填充,如果超过M位,只要不超过数据存储范围即可。

原来,在 int(M) 中,M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。也就是说,int(M),必须和 UNSIGNED ZEROFILL 一起使用才有意义。 如果整数值超过M位,就按照实际位数存储。只是无须再用字符 0 进行填充。


3. 浮点类型


3.1 类型介绍


浮点数和定点数类型的特点是可以 处理小数 ,你可以把整数看成小数的一个特例。因此,浮点数和定点 数的使用场景,比整数大多了。 MySQL支持的浮点数类型,分别是 FLOAT、DOUBLE、REAL。

  • FLOAT 表示单精度浮点数;
  • DOUBLE 表示双精度浮点数;

问题1:FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢?

FLOAT 占用字节数少,取值范围小;DOUBLE 占用字节数多,取值范围也大。


问题2:为什么浮点数类型的无符号数取值范围,只相当于有符号数取值范围的一半,也就是只相当于 有符号数取值范围大于等于零的部分呢?

MySQL 存储浮点数的格式为: 符号(S) 、 尾数(M) 和 阶码(E) 。因此,无论有没有符号,MySQL 的浮 点数都会存储表示符号的部分。因此, 所谓的无符号数取值范围,其实就是有符号数取值范围大于等于 零的部分。


3.2 数据精度说明


对于浮点类型,在MySQL中单精度值使用 4 个字节,双精度值使用 8 个字节。


  • MySQL允许使用 非标准语法 (其他数据库未必支持,因此如果涉及到数据迁移,则最好不要这么 用): FLOAT(M,D) 或 DOUBLE(M,D) 。这里,M称为 精度 ,D称为 标度 。(M,D)中 M=整数位+小 数位,D=小数位。 D<=M<=255,0<=D<=30。

例如,定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。


  • FLOAT和DOUBLE类型在不指定(M,D)时,默认会按照实际的精度(由实际的硬件和操作系统决 定)来显示。


  • 说明:浮点类型,也可以加 UNSIGNED ,但是不会改变数据范围,例如:FLOAT(3,2) UNSIGNED 仍然只能表示0-9.99的范围。


  • 不管是否显式设置了精度(M,D),这里MySQL的处理方案如下:
  1. 如果存储时,整数部分超出了范围,MySQL就会报错,不允许存这样的值
  2. 如果存储时,小数点部分若超出范围,就分以下情况:

若四舍五入后,整数部分没有超出范围,则只警告,但能成功操作并四舍五入删除多余 的小数位后保存。例如在FLOAT(5,2)列内插入999.009,近似结果是999.01。

若四舍五入后,整数部分超出范围,则MySQL报错,并拒绝处理。如FLOAT(5,2)列内插 入999.995和-999.995都会报错。


  • 从MySQL 8.0.17开始,FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用, 将来可能被移除。另外,关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了,将来也可能 被移除。


浮点数类型有个缺陷,就是不精准


在编程中,如果用到浮点数,要特别注意误差问题,因为浮点数是不准确的,所以我们要避免使用“=” 来判断两个数是否相等。

同时,在一些对精确度要求较高的项目中,千万不要使用浮点数,不然会导致结果错误,甚至是造成不可挽回的损失。那么,MySQL 有没有精准的数据类型呢?当然有,这就是定 点数类型: DECIMAL 。


4. 定点数据类型


4.1 类型介绍


MySQL中的定点数类型只有 DECIMAL 一种类型。

使用 DECIMAL(M,D) 的方式表示高精度小数。其中,M被称为精度,D被称为标度。0<=M<=65, 0<=D<=30, D<M。 例如,定义DECIMAL(5,2)的类型,表示该列取值范围是-999.99~999.99。


  • DECIMAL(M,D)的最大取值范围与DOUBLE类型一样,但是有效的数据范围是由M和D决定的。 DECIMAL 的存储空间并不是固定的,由精度值M决定,总共占用的存储空间为M+2个字节。也就是说,在一些对精度要求不高的场景下,比起占用同样字节长度的定点数,浮点数表达的数值范围 可以更大一些。
  • 定点数在MySQL内部是以 字符串 的形式进行存储,这就决定了它一定是精准的。
  • 当DECIMAL类型不指定精度和标度时,其默认为DECIMAL(10,0)。当数据的精度超出了定点数类 型的精度范围时,则MySQL同样会进行四舍五入处理。
  • 浮点数 vs 定点数

-

浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,适 用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、流 体动力学等)

定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (比如涉 及金额计算的场景)


开发中经验:

“由于 DECIMAL 数据类型的精准性,在我们的项目中,除了极少数(比如商品编号)用到整数类 型外,其他的数值都用的是 DECIMAL,原因就是这个项目所处的零售行业,要求精准,一分钱也 不能差。 ”


阿里巴巴《Java开发手册》:

【 强制 】小数类型为 DECIMAL,禁止使用 FLOAT 和 DOUBLE。 说明:在存储的时候,FLOAT 和 DOUBLE 都存在精度损失的问题,很可能在比较值的时候, 得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围,建议将数据拆成整数和小 数并分开存储。


5. 位类型:BIT


BIT类型中存储的是二进制值,类似010110。

二进制字符串类型 长度 长度范围 占用空
BIT(M) M 1 <= M <= 64 约为(M + 7)/8个字节

BIT类型,如果没有指定(M),默认是1位。这个1位,表示只能存1位的二进制值。这里(M)是表示二进制 的位数,位数最小值为1,最大值为64。


6. 日期与时间类型


用得最多的日期时间类型,就是 DATETIME 。

虽然 MySQL 也支持 YEAR(年)、 TIME(时间)、 DATE(日期),以及 TIMESTAMP 类型,但是在实际项目中,尽量用 DATETIME 类型。因为这个数据 类型包括了完整的日期和时间信息,取值范围也最大,使用起来比较方便。毕竟,如果日期时间信息分 散在好几个字段,很不容易记,而且查询的时候,SQL 语句也会更加复杂。 此外,一般存注册时间、商品发布时间等,不建议使用 DATETIME 存储,而是使用 时间戳 ,因为 DATETIME虽然直观,但不便于计算。


7. CHAR 与 VARCHAR


CHAR 和 VARCHAR 类型都可以存储比较短的字符串。

字符串(文本)类型 特点 长度 长度范围 占用的存储空间
CHAR(M) 固定长度 M 0 <= M <= 255 M 个字节
VARCHAR(M) 可变长度 M 0 <= M <= 65535 (实际长度 + 1) 个字节

CHAR 类型:


  • CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。
  • 如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在 右侧填充 空格以达到指定的长 度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。
  • 定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数。


VARCHAR 类型:


  • VARCHAR(M) 定义时, 必须指定 长度M,否则报错。
  • MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字 3字节) ;MySQL5.0版本以上,varchar(20):指的是20字符。
  • 检索VARCHAR类型的字段数据时,会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空 间为字符串实际长度加1个字节。


使用场景:

类型 特点 空间上 时间上 使用场景
CHAR(M) 固定长度 浪费存储空间 效率高 存储不大,速度要求高
VARCHAR(M) 可变长度 节省存储空间 效率低 非CHAR的情况
  • 情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占 个byte用于存储信息长度,本来打算节约存储的,结果得不偿失。
  • 情况2:固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根 据长度的特性就消失了,而且还要占个长度信息。
  • 情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一 个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。
  • 情况4:InnoDB 存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有 区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因 素是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度 的,其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。


【 强制 】VARCHAR 是可变长字符串,不预先分配存储空间,长度不要超过 5000。如果存储长度 大于此值,定义字段类型为 TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。


8. ENUM类型


ENUM 类型也叫作枚举类型,ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时, ENUM类型只允许从成员中选取单个值,不能一次选取多个值。

其所需要的存储空间由定义ENUM类型时指定的成员个数决定。

ENUM 类型 长度范围[1,65535],占用 1、2个字节。


  • 当ENUM类型包含1~255个成员时,需要1个字节的存储空间;
  • 当ENUM类型包含256~65535个成员时,需要2个字节的存储空间。
  • ENUM类型的成员个数的上限为65535个。
-- 建表
CREATE TABLE test_enum(
  season ENUM('春','夏','秋','冬','unknow')
);

-- 添加数据
INSERT INTO test_enum
VALUES('春'),('秋');

# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');

# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);

# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');

# 当ENUM类型的字段没有声明为NOT NULL时,插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5天前
|
关系型数据库 MySQL 存储
【MySQL】——数据类型及字符集
【MySQL】——数据类型及字符集
171 0
【MySQL】——数据类型及字符集
|
5天前
|
机器学习/深度学习 存储 关系型数据库
MySQL数据库:常用数据类型
【2月更文挑战第7天】
35 0
|
5天前
|
SQL 关系型数据库 MySQL
【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法;熟练掌握MySQL的编码、数据类型等基础知识;掌握实体完整性的定义和维护方法、掌握参照完整性
【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法;熟练掌握MySQL的编码、数据类型等基础知识;掌握实体完整性的定义和维护方法、掌握参照完整性
115 1
|
3天前
|
存储 关系型数据库 MySQL
MySQL数据类型详解及实例应用
MySQL数据类型详解及实例应用
|
5天前
|
存储 SQL 关系型数据库
MySql中有哪些数据类型(数据库学习——三)
MySql中有哪些数据类型(数据库学习——三)
41 1
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
|
5天前
|
SQL 存储 关系型数据库
【MySQL-6】DDL的表结构的数据类型盘点&案例演示
【MySQL-6】DDL的表结构的数据类型盘点&案例演示
|
5天前
|
存储 SQL 关系型数据库
【mysql】mysql中的数据类型知多少?
【mysql】mysql中的数据类型知多少?
|
5天前
|
关系型数据库 MySQL
MySQL基础(二:常用数据类型及MySQL创建过程实例)
MySQL基础(二:常用数据类型及MySQL创建过程实例)
MySQL基础(二:常用数据类型及MySQL创建过程实例)
|
5天前
|
存储 缓存 关系型数据库
【Mysql】Schema与数据类型优化
【Mysql】Schema与数据类型优化
16 0

推荐镜像

更多