【Mysql上分之路】第五篇：Mysql的数据类型

MySQL数据类型

数据类型是指系统中所允许的数据的类型，MySQL数据类型表明了数据表中可以存储什么类型的数据以及数据应该怎么存储，也可以说是数据类型用来限制或允许列中存储的数据

MySQL数据类型分类

MySQL支持多种数据类型：数值类型、日期/时间类型、字符串类型、二进制类型

数值类型

数值类型分为整数类型、浮点类型、定点数类型，整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL

整数类型

整数类型又称数值型数据，数值型数据类型主要用来存储数字，MySQL 提供了多种数值型数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大，整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件

Mysql中整数类型
类型名称	说明	存储需求
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个字节
MEDIUMINT	中等大小的数	3个字节
INT	普通大小的数	4个字节
BIGINT	大整数	8个字节

注意：显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来

如果在整数型数据类型中添加 unsigned zerofill 则会在不满足数据长度的情况下，前面自动补0，已达到对应的位数，需要注意的是 vachar 这样的数据类型是会真实的控制住数据长度的

不同的整数类型有不同的取值范围，并且需要不同的存储空间，根据实际需求选择最合适的类型，有利于提高查询效率和节省存储空间

浮点数类型和定点数类型

Mysql使用浮点数和定点数来表示小数。

浮点类型有两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）

定点类型：DECIMAL

浮点类型和定点类型都可以用（M,N）表示，其中M为精度，即表示总共的位数；N为标度，表示小数的位数

Mysql中的小数类型
类型名称	说明	存储需求
FLOAT	单精度浮点数	4个字节
DOUBLE	双精度浮点数	8个字节
DECIMAL(M,D)，DEC	压缩的"严格"定点数	M±2个字节

FLOAT 类型的取值范围

• 有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
• 无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38

DOUBLE 类型的取值范围

• 有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
• 无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308

DECIMAL类型的取值范围

• DECIMAL类型与FLOAT和DOUBLE不同，DECIMAL实际是以串的方式存放
• DECIMAL的最大取值范围可能与DOUBLE一样，但是其有效的取值范围是由M和D的值决定
• DECIMAL的存储空间并不是固定的，而是由其精度值M所决定，占用M±2个字节

不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理

FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）

注意：在Mysql中，定点数以字符串的形式存储，对于精度比较高的需求(货币、科学数据)使用DECIMAL类型存储

日期/时间类型

MySQL 中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP

日期与时间数据类型
类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1个字节
TIME	HH:MM:SS	-838:59:59~838:59:59	3个字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-31	3个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC ~ 2038-01-19-03:14:07 UTC	4个字节

1、YEAR

YEAR类型是一个单字节类型用来表示年份，在存储的时候只需要一个字节

以4位字符串或者4位数字格式表示的YEAR，范围为1901~2155，输入格式为’YYYY’或YYYY

2、TIME

TIME类型只需要时间信息的值，在存储时需要3个字节，格式为’HH:MM:SS’。

TIME类型的取值范围是-838:59:59~838:59:59，差值这么大是因为TIME类型除了表示一天的时间，还可能是过去的时间和两个事件之间的时间间隔

可以使用各种格式指定TIME值：

• ①‘D HH:MM:SS’格式：D表示日，可以取0~34之间的值。在插入数据库时，D被转换成小时保存，格式为 ‘D*24+HH’，如果TIME值中有指定冒号的简写值，则假定最左边的两位为时（11:22表示的是11::22::00）
• ②‘HHMMSS’格式或者HHMMSS：没有间隔符的字符串或者HHMMSS形式的简写值，Mysql在解释值的时候，假定从最右边的两位为秒（1122表示的是00:11:22）

3、DATE

DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3个字节。在给DATE类型的字段进行赋值时，可以使用字符串类型或者数字类型的数据输入，只要符合日期类型的格式就行。

• ①‘YYYY-MM-DD’或者YYYY-MM-DD字符串格式或者YYYY-MM-DD、YYYYMMDD数字格式
• ②使用CURRENT_DATE()或者NOW()函数，插入当前系统日期

CURRENT_DATE()返回的是当前日期值，不包括时间部分；NOW()返回日期和时间值，在保存到数据库时只保存日期部分

4、DATETIME

DATETIME类型用在同时包含日期和时间信息的值，在存储时需要8个字节。日期格式为’YYYY-MM-DD HH:MM:SS’。在给DATETIME类型的字段进行赋值时，可以使用字符串类型或者数字类型的数据输入，只要符合日期类型的格式就行。

• ①‘YYYY-MM-DD’或者YYYY-MM-DD字符串格式或者YYYY-MM-DD、YYYYMMDD数字格式

5、TIMESTAMP

TIMESTAMP显示格式和DATETIME一致，但是显示宽度限定在19字符。日期格式为：YYYY-MM-DD HH:MM:SS，在存储时需要4个字节，但是TIMESTAMP列的取值范围小于DATETIME的取值范围。

TIMESTAMP和DATETIME除了在字节存储和取值范围有所不同外，在存储日期日期数据时，DATETIME按实际输入的格式存储，即输入什么就存什么，与时区无关；而TIMESTAMP值的存储是以UTC(世界标准时间)格式保存，存储当前时间时需要对当前时区转换，检索时再从UTC换回当前时区

字符串类型

字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据

字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找

Mysql中字符串类型：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET

Mysql中字符串类型
类型名称	说明	存储需求
CHAR（M）	固定长度非二进制字符串	M个字节，1<=M<=255
VARCHAR（M）	变长非二进制字符串	L+1字节，L<=M和1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，L<2^8
TEXT	小的非二进制字符串	L+2字节，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，L<2^32
LONGTEXT	大的非二进制字符串	L+4字节，L<2^64
ENUM	枚举类型，只能有一个枚举字符串值	1或2字节，取决于枚举值的数量（最大65535）
SET	一个设置，字符串对象可以由0或多个成员	1、2、4、8个字节，取决于集合成员的数量（最大64）

VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸

一个VARCHAR（10）列能保存最大长度为10字符的一个字符串，实际存储的是字符串的长度L，再加上记录字符串长度的一个字节，即存储需求为L+1字节

1、CHAR和VARCHAR

CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时右侧填充空格以达到指定的长度，M表示列长度，M的范围是0~255个字符。CHAR(4)定义一个固定长度的字符串列，其包含的字符个数最大为4。当检索到CHAR值时，尾部用来填充的空格被删除掉。

VARCHAR(M)为长度可变的字符串，M表示为最大列长度，M的范围为0~65535，VARCHAR最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度+1。

VARCHAR(10)定义了一个最大长度为10的字符串，插入的字符串长度只有4个字符，而实际存储的字符串为4个字符+一个字符串结束符

2、TEXT类型

TEXT类型主要用来保存的是非二进制字符串，如文章内容、评论等，当保存或查询TEXT列的值时，不删除尾部空格。

TEXT的类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，不同的TEXT类型的存储空间和数据长度不同。

3、ENUM类型

ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值

• 语法格式：字段名 ENUM（‘值1’,‘值2’,…‘值n’）
• 字段名：要定义的字段名
• 值n：枚举列表中第n个值

ENUM类型的字段在取值时，只能从指定的枚举列表中取，而且一次只能取一个。如果枚举列表成员中有空格时，其尾部的空格将自动删除。ENUM值在内部用整数表示，每一个枚举值均有一个索引值：列表值允许的成员值从1开始编号，Mysql存储的就是这个索引编号，最多有65535个元素。

ENUM值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL值排在其他所欲枚举值前。

4、SET类型

SET是一个字符串对象，可以有0或多个值，SET列最多可以有64个成员，其值为表创建时规定的一列值。

• 语法格式：set(‘值1’,‘值2’…‘值n’)
• 与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号，当创建表时，SET成员值的尾部空格将自动被删除掉
• ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合

如果插入SET字段中列值有重复的，Mysql会自动删除重复的值；插入SET字段时的顺序不重要，Mysql存入数据库时会按照SET定义的顺序显示；如果插入了不正确的值，默认情况下Mysql会忽略这些值

二进制类型

MySQL支持两类字符型数据：文本字符串和二进制字符串，二进制字符串类型有时候也直接被称为“二进制类型”

Mysql中二进制字符串：BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUNBLOB、LONGBLOB

Mysql中二进制字符串
类型名称	说明	存储需求
BIT(M)	位字段类型	大约(M+7)/8字节
BINARY(M)	固定长度二进制字符串	M个字节
VARBINARY(M)	可变长度二进制字符串	M+1个字节
TINYBLOB(M)	非常小的BLOB	L+1字节，L<2^8
BLOB(M)	小的BLOB	L+2字节，L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节，L<2^32
LONGBLOB(M)	非常大的BLOB	L+4字节，L<2^64

1、BIT类型

BIT类型为位字段类型，M表示每个值的位数，范围为1~64，如果M被省略则默认为1。如果为BIT(M)列分配值的长度小于M位，在值的左边用0填充。BIT数据类型用二进制的形式来保存位字段值。如以二进制的形式保存数据13，13的二进制形式为1101，需要4位的BIT类型，即定义列类型为BIT(4)。

2、BINARY和VARBINARY

• BINARY和VARBINARY类似于CHAR和VARCHAR，但是BINARY和VARBINARY包含了二进制字节字符串
• BINARY类型的长度是固定的，指定长度之后，不足最大长度将它们右边填充’\0’，补齐以达到指定长度
• 指定类型BINARY(3),当插入’a’时，存储的内容为’a\0\0’
• VARCHAR类型的长度是可变的，指定好长度之后，其长度可以在0到最大值之间
• 指定类型VARCHAR(3),当插入值的长度为1，则实际存储的空间长度为1+1

3、BLOB

BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB分为4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

BLOB的存储范围
数据类型	取值范围
TINYBLOB	2^8-1字节
BLOB	2^16-1字节
MEDIUMBLOB	2^24-1字节
LONGBLOB	2^32-1字节

注意：BLOB存储的是二进制字符串（字节字符串），TEXT存储的是非二进制字符串（字符字符串）；BLOB没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较

数据类型的选择

Mysql提供了大量的数据类型对应着存储不同类型的数据的需求，为了优化存储，提高数据库性能，应选择最为精准存储需求的数据类型，避免存储空间的浪费

1、整数和浮点数

• 需求中不要小数就使用整数类型来保存数据；需要小数则使用浮点数类型
• 根据整数类型的取值范围选择最合适的整数类型来保存数据
• DOUBLE类型的存储精度比FLOAT类型高

2、浮点数和定点数

在长度一定的情况下，浮点数的取值范围大于定点数。但是对于精度比较高的则使用DECIMAL来存储数据。

3、日期与时间类型

• 同时记录日期和时间，则可以使用TIMESTAMP和DATETIME类型，由于TIMESTAMP列的取值范围小于DATETIME的取值范围，存储范围较大的日期使用DATETIME
• 默认情况下插入一条记录并没有指定TIMESTAMP这个字段值，系统会默认TIMESTAMP设为当前时间

4、CHAR和VARCHAR

• CHAR是固定长度字符，VARCHAR是可变长度字符；CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格
• CHAR是固定长度所以处理速度比VARCHAR的速度更快。但是浪费空间
• 对于MyISAM存储引擎最好使用固定长度的数据带边可变长的数据列，这样可以使表静态化，从而使数据检索更块，用空间换时间
• 对于InnoDB引擎：使用可变长度的数据列，InnoDB的存储格式不分固定长度和可变长度，使用CHAR不一定比VARCHAR好，VARCHAR是按照实际长度存储，比较节省空间，对磁盘IO和数据存储总量比较好

5、ENUM和SET

• ENUM只能取单值，数据列表是一个枚举集合；SET可以取多值，空字符串也是一个合法的SET值，在需要取多个值的时候，适合使用SET类型，比如存储一个人的兴趣爱好
• ENUM和SET的值是以字符串的形式出现的，但是在内部MySql是以数值的形式存储他们

6、BLOB和TEXT

• BLOB是存储的二进制字符串，TEXT是非二进制字符串，二者都可以存储大容量信息。
• BLOB主要存储图片、音频信息，等TEXT只能存储纯文本文件

总结

MySQL数据类型表明了数据表中可以存储什么类型的数据以及数据应该怎么存储，也可以说是数据类型用来限制或允许列中存储的数据

MySQL支持多种数据类型：数值类型、日期/时间类型、字符串类型、二进制类型

Mysql提供了大量的数据类型对应着存储不同类型的数据的需求，为了优化存储，提高数据库性能，应选择最为精准存储需求的数据类型，避免存储空间的浪费

赶紧学习起来吧！我是一个正在努力找回自我的人，希望能和一起学习的人成长，有错误的地方请各位大佬帮忙指正，如果觉得有帮助就点个赞当作对我的一个小肯定❤👍，peace&love