Loading [MathJax]/jax/output/CommonHTML/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >第11章、数据类型

第11章、数据类型

作者头像
幺鹿
发布于 2018-08-21 08:02:40
发布于 2018-08-21 08:02:40
1.7K00
代码可运行
举报
文章被收录于专栏:Java呓语Java呓语
运行总次数:0
代码可运行

数字类型

MySQL 支持标准 SQL 中所有数据类型。 所有的这些类型包括:

  • 精确的数据类型:INTEGER,SMAILLINT,DECIMAL,NUMERIC;
  • 近似的数据类型:FLOAT,REAL,DOUBLE PRECISION;

关键字 INT 是 INTEGER 的别名,关键字 DEC 和 FIXED 是 DECIMAL的别名。 在 MyISAM/MEMORY/InnoDB和NDB表中支持BIT 数据类型,BIT 数据类型用于存储 bit 值。

整型

MySQL 支持 SQL 标准整数类型INTEGER(或INT)和SMAILLINT。在此之外,MySQL 还扩展了数据类型:TINYINT、MEDIUMINT、BIGINT。下表显示了每种整数类型所需的存储空间和范围。

类型

存储空间(字节)

最小值(signed)

最大值(signed)

最小值(unsigned)

最大值(unsigned)

tinyint

1

-2^7

2^7-1

0

2^8-1

smallint

2

-2^15

2^15-1

0

2^16-1

meduimint

3

-2^23

2^23-1

0

2^24-1

int

4

-2^31

2^31-1

0

2^32-1

bigint

8

-2^63

2^63-1

0

2^64-1

精确小数类型

DECIMAL 和 NUMERIC 类型是精确类型。比如货币,需要使用精确类型存储。在MySQL中用DECIMAL实现了NUMERIC,所在下文中有关DECIMAL的描述对NUMERIC也适用。MySQL使用二进制存储DECIMAL的值。

在小数列声明中,精度和小数位数可以(通常是)指定:salary DECIMAL(5,2) 在本例中,5表示数字个数(不含小数点),2表示小数点后的位数。本例中的数据范围是:-999.99至999.99。 在标准 SQL 中当为精度为0时可以省略,即DEMICAL(5)等同于DEMICAL(5,0)。

近似小数类型

FLOAT和DOUBLE类型表示近似的数值数据值。MySQL使用四个字节作为单精度值,八个字节作为双精度值。

BIT数据类型

BIT(M) 允许存储M个BIT值,M的范围是1至64。

数字类型属性

MySQL支持一个扩展,用于根据类型的基本关键字在圆括号中指定整数数据类型的显示宽度。例如,INT(4)指定一个显示宽度为四位的INT。 这个可选的显示宽度可以被应用程序用来显示宽度小于为该列指定的宽度的整数值,方法是用空格填充它们。(也就是说,这个宽度出现在结果集返回的元数据中,无论是否使用都取决于应用程序。)

显示宽度不限制可以存储在列中的值的范围,也不会阻止比列显示宽度更宽的值被正确显示。

所有的整数类型都可以有一个可选的(非标准的)UNSIGNED,当你需要更大的范围时可供选择。同样的浮点类型(精确,非精确)也可以使用 UNSIGNED,但是与整型不同的是,浮点型的 UNSIGNED 不会改变数据的范围。

超出范围和溢出处理

当存储的值超出额定范围后,存储的结果视MySQL的模式决定。 当启用了严格模式,则会按照标准的SQL拒绝数据的写入。若未启用严格模式,则将数据裁剪并写入。

日期类型

DATE, DATETIME, TIMESTAMP

DATE, DATETIME, TIMESTAMP 是互相关联的。这章节将会阐述,它们哪些点是相同的,另外又有哪些点是有差异的。

DATE 日期类型用于具有日期部分但没有时间部分的值。MySQL以'YYYY-MM-DD'格式检索并显示日期值,支持的范围是'1000-01-01' 到 '9999-12-31'。

DATETIME 同时有日期部分和时间部分。MySQL 以'YYYY-MM-DD HH:MM:SS'的格式,支持的范围从'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。

TIMESTAMP 也同时有日期部分和时间部分。它的范围是:'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。

DATETIME 和 TIMESTAMP 精确到微秒。 DATETIME 和 TIMESTAMP 数据类型具备自动初始化和更新功能(举例:业务上通常需要的创建时间和更新时间,在MySQL层面上可以自动完成)。

对应 TIMESTAMP 类型,存储时会自动转到 UTC 时区,在检索时则会自动从 UTC时区转到当前时区。( DATETIME不会执行该操作)。默认情况下,每个连接的当前时区是服务器的时间。时区可以在每个连接的基础上设置。只要时区设置保持不变,您将获得与您存储的值相同的值。如果您存储 TIMESTAMP ,然后更改时区并检索该值,则检索到的值与您存储的值不同。发生这种情况是因为同一时区未用于双向转换。当前时区的修改参见系统变量:time_zone

MySQL 允许指定为字符串的值的“宽松”格式,其中任何标点符号可用作日期部分或时间部分之间的分隔符。虽然 MySQL 提供了宽松的存储环境,我们最好仍按照默认的格式进行编码,以避免可读性差引起的干扰。

TIME

TIME 以HH:MM:SSHHH:MM:SS格式化数据。TIME 的范围从 '-838:59:59' 到 '838:59:59'。小时部分如此大的原因是,TIME并不是只用于1天内的场景。也可能用于两次事件间的间隔事件,这可能远大于24小时。

当录入的值超出范围后,将会自动剪切到最近的有效范围内的值。

YEAR

YEAR 以 YYYY格式,范围从 1901 至 2155,或 0000。 若是4位则范围是,1901~2155。 若是录入199,则169转化为2001-2069,7099则转化为19701999。 若是录入数字0,则转化为0000。若是录入字符0或00,则转化为2000。

关于MySQL默认值了解即可(不能依赖默认值),我们需要严格控制录入的值。 在 MySQL 5.7 中已经禁用了YEAR(2);

自动初始化和更新 TIMESTAMP 与 DATETIME

表格中的 TIMESTAMP 或 DATETIME 的列可以初始化当前时间作为默认值,或设置为修改自动更新,或两者共存。

  • 当指定的列中没有值时,使用当前时间作为默认时间。
  • 当该行中任何其他列的值从其当前值更改时,自动更新的列会自动更新为当前时间戳。

要设置自动更新属性时,在列的定义时使用以下语句(顺序不相关):

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP

CURRENT_TIMESTAMP有以下的同义词: CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, 和 LOCALTIMESTAMP()。

要 MySQL 5.7中,新增DATETIME列的支持设置默认值和自动更新。在之前的版本,你如果尝试设置将会得到一个错误。

字符串类型

CHAR VARCHAR

字符串类型CHARVARCHARBINARYVARBINARYBLOBTEXTENUM,和 SET

CHAR和VARCHAR类型相似,但它们被存储和检索的方式不同。它们的最大长度和尾部空格是否保留也不同。

CHAR列长度可以是0到255之间的任何值。CHAR 存储值时,它们将空格填充到指定的长度。当CHAR检索到值时,除非PAD_CHAR_TO_FULL_LENGTH启用了SQL模式,否则将删除尾随空格 。

VARCHAR列中的 值是可变长度的字符串。长度可以指定为0到65535之间的值。

与CHAR不同,VARCHAR会存储一个长度为1字节或2字节的前缀,用于存储长度值,用以表明存储的字节数。

若需存储的字节数少于255,则前缀的长度为1。 否则,前缀的长度为2。

以下表格说明 CHAR 和 VARCHAR 在存储值时候的差异:

image.png

显示为存储在表格最后一行的值仅适用 于不使用严格模式的情况 ; 如果MySQL在严格模式下运行,则超出列长度的值不会被存储,并会导致错误。

这些特性会很有用:

  • 比如在列的可选值是 Y/N时,选择CHAR(1)比VARCHAR(1)节省一个字节空间。
  • 比如既有的数据中存在一些以空格结尾的数据,那么可以先将列改为CHAR保存。然后再修改为VARCHAR。

BINARY VARBINARY

BINARY和VARBINARY类型与CHAR和VARCHAR相似,除了它们包含二进制字符串而非非二进制字符串。也就是说,它们包含字节串而不是字符串。

BINARY的填充字节是0x00。字节在比较中很重要,包括ORDER BYDISTINCT操作。0x00与空格在比较时是不一样的,0x00要小于空格。

如果您打算使用BINARY数据类型来存储二进制数据,并且您要求检索到的值与存储的值完全相同,那么您应该仔细考虑前面的填充和剥离特征 。

TEXT BLOB

BLOB 是二进制字符串。 它有四个BLOB 类型TINYBLOB,BLOB, MEDIUMBLOB,和LONGBLOB。

TEXT 是非二进制字符串 它有四个TEXT类型 TINYTEXT,TEXT, MEDIUMTEXT,和LONGTEXT。

对于TEXT和BLOB列,插入时没有填充,并且在select时没有字节被删除。

以下是使用这两者的限制点:

  • 使用临时表处理的查询结果中的 实例BLOBTEXT列导致服务器在磁盘上而不是在内存中使用表,因为 MEMORY存储引擎不支持这些数据类型(请参见 第8.4.4节“内部临时在MySQL中使用表“)。使用磁盘会导致性能损失,因此只有在确实需要时才在查询结果中包含 BLOBTEXT列。例如,避免使用 SELECT *,它会选择所有列。
  • max_sort_length排序时仅使用列 的第一个 字节。缺省值为 max_sort_length1024.通过增加max_sort_length服务器启动或运行时的值,可以在排序或分组中增加更多字节 。任何客户端都可以更改其会话max_sort_length 变量的值:
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
    -> ORDER BY comment;

ENUM

枚举是一个字符串对象,其值是从表创建时列规范中显式枚举的允许值列表中选择的值。 它有以下的好处:

  • 紧凑的存储特性,字符串会被映射为数字存储;
  • 良好的可读性;

真实场景下很少用到?

SET

SET 由0~64个以,分隔的字符串构成的,要注意成员值本身是不包含逗号的。关于64的限制,参考Section C.10.5, “Limits Imposed by .frm File Structure”

若在SET中出现重复值,则会产生警告。(若开启严格模式,则是直接抛异常)。SET字符串尾部的空格将会自动被移除。

数据类型默认值

除了一个例外情况外,默认值都必须是常量;它不可以是方法或者表达式;这意味着,你不能设置日期类型的列设置默认值为:NOW()CURRENT_DATE。 截止MySQL5.7唯一的例外情况是,你可以设置日期类型的列默认值为:CURRENT_TIMESTAMP

BLOB、TEXT、JSON、GEOMETRY的列不能设置默认值;

如果列中没有指定明确的默认值,则MySQL的默认值规则如下:

  • 如果列中允许NULL值,则默认值为NULL
  • 如果列中不允许NULL值,则MySQL不指定默认值;

当一个明确表明为NOT NULL的列,被INSERT或者UPDATE的数据为NULL时。MySQL则根据对应模式进行处理:

  • 如果启用严格模式,则将抛出异常并回滚事务;
  • 如果未启用严格模式,MySQL将列设置为列数据类型的隐式默认值。

举例,假定创建数据库表如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
CREATE TABLE t (i INT NOT NULL);

INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));

在本例中 i 没有明确的默认值。

  • 当启用严格模式时,三条指令均将抛出异常并回滚。
  • 当未启用严格模式时,仅第三条指令会抛出异常并回滚。前两个语句插入隐式默认值,但第三个失败,因为缺省值(i)不能产生值。

对于已存在的数据库表,你可以使用SHOW CREATE TABLE来显示隐式的默认值。

隐式默认值定义如下:

  • 对于数字类型其默认值是:0;对于启动了自增序列的数字类型,则默认值是下一个数值;
  • 对于除了TIMESTAMP之外的日期类型,其默认值是用0填充的字符串。比如:DATETIME其默认值是0000-00-00 00:00:00。对于TIMESTAMP的默认值,则是当前的时间戳对应值。
  • 对于除了枚举类型之外的字符串值,其默认值是空字符串;对于枚举类型,其默认值则是枚举类型中的第一个值。

小知识: SERIAL DEFAULT VALUE 是对应数字类型下 NOT NULL AUTO_INCREMENT UNIQUE 的别名。

数据类型存储要求

磁盘上表数据的存储要求取决于几个因素。不同的存储引擎以不同方式存储原始数据。表格数据可能会被压缩,无论是列还是整行,都会使表或列的存储需求计算复杂化。

尽管磁盘上的存储布局有所不同,但内部MySQL APIs使用了适用于所有存储引擎的一致数据结构,通信并交换关于表行的信息。

本节包括MySQL支持的每个数据类型的存储要求的指导方针和信息。

在MySQL内部能够支持的最大单行的大小是65535字节,即便存储引擎支持更大也不行。当然这个数据不包括:BLOB、TEXT类型(对于 BLOB、TEXT 列而言,它只在65535中贡献9至12字节);对于 BLOB、TEXT类型,真实的数据内容并不存储在行缓冲区,故它们的值所占用的内存不受行缓冲区的限制。不同的引擎存储于分配数据的方式均有差异。

INNODB 存储引擎

这部分内容请直接参见Section 14.8.1.2, “The Physical Row Structure of an InnoDB Table”

其他的存储引擎,暂不展开;

选择正确的列类型

为了优化存储,你需要选择最精确的数据类型;以范围1~9999的整型数据为例,MEDIUMINT UNSIGNED是最佳的选择;既能满足存储需求,又可以使用最少的存储量(参见上文的存储字节);

如果对精度不敏感,则使用DOUBLE是不错的选择;如果需要高精度,BIGINT是不错的替代,这使您可以用64位整数进行所有计算,然后根据需要将结果转换回浮点值。

其它数据库SQL与MySQL的类型对照表

image.png

提示:MySQL的正确的读法是:My-S-Q-L,每个字母需要分开念,是不能将SQL连成单词去读。尽管大部门人都是连着读的:)。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2018.04.20 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
MySQL之表的数据类型
一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob.com/mysql/mysql-data-types.html http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html mysql常用数据类型概览 #1. 数字: 整型:tinyinit int bigint 小数: float :在位数比较短的
人生不如戏
2018/04/10
5.7K0
MySQL之表的数据类型
MySQL基础数据类型
  存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的
changxin7
2022/05/06
5K0
MySQL基础数据类型
MySQL数据类型的优化选择
缺点:碎片问题。字符串update变长时,一个行的占用空间变长,但是在页内没有更多空间存储,MYISAM将行拆成不同片段存储,InnoDB分裂页使行可以放进页内;
星哥玩云
2022/08/16
7480
MySQL数据类型选择「建议收藏」
在MySQL数据类型设置方面,尽量采用更小的数据类型,因为它们占用的存储空间更小,通常有更好的性能,花费更少的硬件资源。并且,尽量把字段定义为NOT NULL,避免使用NULL。
全栈程序员站长
2022/11/05
8740
高性能MySQL (一):Schema与数据类型优化
本文摘录总结自《高性能MySQL》(第三版),将以每章一篇文章的方式带大家读这本数据库经典之作。总结精华,帮大家快速抓住重点信息,节省宝贵时间。
solocoder
2022/04/06
1.1K0
数据库之数据类型详解
查看表的详细信息如下(在创建表的时候没有指定其长度,但是每一列都有自己默认的长度):
小手冰凉
2020/04/24
4.6K0
MySql基础之数据类型精讲(重点)
整数类型一共有 5 种,包括 TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)和 BIGINT。 它们的区别如下表所示:
冬天vs不冷
2025/01/21
1300
MySql基础之数据类型精讲(重点)
MySQL性能优化(二):选择优化的数据类型
良好的设计是高性能的基石,应该根据系统的实际业务需求、使用场景进行设计、优化、再调整,在这其中往往需要权衡各种因素,例如,数据库表究竟如何划分、字段如何选择合适的数据类型等等问题。
xcbeyond
2020/03/22
1.4K0
MySQL性能优化(二):选择优化的数据类型
Mysql数据类型
在MySQL中支持的主要整数类型是TINYINT,SMALLINT,MEDIUMINT,INT 和 BIGINT。这些类型在很大程度上是相同的,只有它们存储的值的大小是不相同的。MySQL以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展。当从数据库检索一个值时,可以把这个值加长到指定的长度。例如,指定一个字段的类型为 INT(6),就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是,使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。万一我们需要对一个字段存储一个超出许可范围的数字,MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是,MySQL 会在不合规定的值插入表前自动修改为 0。
用户3876103
2024/11/03
1390
表的数据类型
一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob.com/mysql/mysql-data-types.html http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html mysql数据类型概览 #1. 数字: 整型:tinyinit int bigint 小数: float :在位数比较短的情况
用户1214487
2018/01/23
4.6K0
表的数据类型
MySQL中的数据类型和schema优化
最近在学习MySQL优化方面的知识。本文就数据类型和schema方面的优化进行介绍。
草捏子
2020/11/09
1.2K0
MySQL中的数据类型和schema优化
mysql基本数据类型
mysql数据库分多钟数据类型,大类可以分为三种:数值类型、时间(日期)和字符(串)类型。
OECOM
2020/07/01
1.5K0
第四章《MySQL的数据类型和运算符》
一、数据类型介绍: (1)数据表由多个字段组成,每一个字段都指定了自己的数据类型,指定了数据类型后,也就决定了向字段插入数据的内容; (2)不同的数据类型也决定了MySQL在存储数据的时候使用的方式,以及在使用数据的时候选择什么运算符进行运算; (3)数值数据类型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL (4)日期/时间数据:YEAR、TIME、DATE、DATETIME、TIMESTAMP (5)字符串数据类型:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET 二、数值类数据类型: (1)数值类数据类型主要用来存储数字,不同的数值类型提供不同的取值范围,可以存储的值范围越大,需要的存储空间也越大; (2)数值型分为:整数类型,浮点数类型,定点数类型;
全栈程序员站长
2021/06/08
1K0
第四章《MySQL的数据类型和运算符》
MYSQL数据库数据类型
我们不推荐使用非严格模式下建立table,因为它会可能造成数据丢失的情况,所以我们必须在5.6版本中将mysql设置为严格模式。
小小咸鱼YwY
2019/07/24
2.6K0
mysql工程师之04 数据类型
取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~256)。
张哥编程
2024/12/13
1270
04 数据类型
创建表的时候,我们在类型这里给出了不同的选项,比如有int ,varchar。数据类型不同,储存的内容也不相同。下面来看看MySQL的数据类型介绍
张哥编程
2024/12/13
830
MySQL建表数据类型的选择
首先数据选择有几个简单原则: 更小的通常更好。一般情况下,应该尽量使用可以正确存储数据的最小数据类型。例如只需要存 0~200,tinyint unsigned 更好。更小的数据类型通常更快,因为它们占用更少的磁盘、内存和 CPU 缓存,并且处理时需要的 CPU 周期也更少。 简单就好。简单数据类型的操作通常需要更少的 CPU 周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较 比 整型比较更复杂。这里有两个例子:一个是应该使用 MySQL 内建的类型(date, time, d
星哥玩云
2022/08/17
5.5K0
mysql数据类型有哪几种_数据库 数据类型
注:定点数以字符串形式存储,对精度要求高时使用decimal较好;尽量避免对浮点数进行减法和比较运算。
全栈程序员站长
2022/11/06
2.6K0
mysql数据类型有哪几种_数据库 数据类型
【MySQL笔记】数字类型、时间和日期类型、字符串类型
使用MySQL数据库存储数据时,不同的数据类型决定了MySQL存储数据方式的不同。MySQL数据库提供了多种数据类型,其中包括数字类型、时间和日期类型、字符串类型。本期我们一起来学习MySQL的数据类型吧~
颜颜yan_
2022/12/01
4.3K0
【MySQL笔记】数字类型、时间和日期类型、字符串类型
MySQL支持的数据类型
整型类型中,按照取值范围和存储方式不同,分为tinyint,smallint,mediumint,int和bigInt这五个类型。如果超出类型范围的操作,会发生”Out of range”错误提示。 对于整型数据,MySQL还支持在类型后面的小括号内指定显示宽度,例如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度,如果不显示指定宽度则默认为int(11)。一般配合zerofill使用,顾名思义,zerofill就是用”0”填充的意思,也就是在数字位数不够的空间用“0”填满。
秋白
2019/02/21
2.9K0
MySQL支持的数据类型
相关推荐
MySQL之表的数据类型
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验