《MySQL》第十二篇 数据类型

MySQL 支持多种数据类型，学习好数据类型，才能更好的学习 MySQL 表的设计，让表的设计更加合理。

一. 整数类型

类型	大小	SIGNED(有符号)	UNSIGNED(无符号)	说明
tinyint	1字节	-128~127	0~255	小整型
smallint	2字节	-32768~32767	0~65535	小整型
mediumint	3字节	-8388608~8388607	0~16777215	中整型
int	4字节	-2147483648~2147483647	0~49294967295	整型
bigint	8字节	-9223372036854775808~9223372036854775807	0~18446744073709551615	大整型

tinyint
tinyint 为小整数类型，存储空间为1个字节（8位），有符号范围-128 ~ 127，无符号范围 0 ~ 255,此类型通常在数据库中表示类型的字段，如某一字段 type 表示学科,其中 “type=1” 表示语文，“type=2” 表示数学, “type=3” 表示英语，此时 type 字段即可使用 tinyint 这种存储空间比较小的类型

smallint
smallint 为小整数类型，存储空间2个字节(16位),有符号范围 -32768 ~ 32767，无符号范围 0 ~ 65535，当遇到最大值不超过 65535 的整数类型字段时，可使用无符号 smallint 类型。

mediumint
mediumint 为中整数类型，存储空间3个字节（24位），有符号范围 -8388608 ~ 8388607，无符号范围 0 ~ 16777215，当遇到最大值不超过 16777215 的整数类型字段时，可使用无符号 mediumint 类型。

int
int 为整数类型，存储空间 4 个字节 ( 32位 )，有符号范围 -2147483648 ~ 2147483647，无符号范围 0 ~ 49294967295，当遇到最大值不超过 49294967295 的整数类型字段时，可使用无符号 int 类型，通常自增主键 id 使用 int 类型。

bigint
bigint 为大整数类型，存储空间8个字节(64位)，有符号范围 -9223372036854775808 ~ 9223372036854775807，无符号范围 0 ~ 18446744073709551615，当遇到最大值不超过 18446744073709551615 的整数类型字段时，可使用无符号 bigint 类型，通常自增主键 id 使用 int 无法满足时，可以使用 bigint 类型。

二. 浮点类型

类型	大小	SIGNED(有符号)	UNSIGNED(无符号)	说明
float	4字节	-3.402823466E+38 ~ -1.175494351E-38，0，1.175494351E-38 ~ 3.402823466351E+38	0，1.175494351E-38 ~ 3.402 823 466 E+38	单精度浮点型
double	8字节	-1.7976931348623157E+308 ~ -2.2250738585072014E-308，0，2.2250738585072014E-308 ~ 1.7976931348623157E+308	0，2.2250738585072014E-308 ~ 1.797693134862315 7E+308	双精度浮点型
decimal	每4个字节存储9个数字，小数点占1字节	DECIMAL(M,D)，取决于M和D,M是整数部分，D是小数部分	DECIMAL(M,D)，取决于M和D，M是整数部分,D是小数部分	精确类型,常用来表示金额

float
float 为单精度浮点类型，存储空间 4 个字节，支使用标准的浮点运算进行近似计算，若想知道浮点运算是怎么计算的，则需要研究操作系统的浮点数方式，通常对小数精度要求不那么高的字段可使用 float 类型。

double
double 为双精度浮点类型，存储空间 8个字节，相比float 有更高精度和更大的范围，通常对小数精度要求不那么高，但比 float 要求更高的字段可使用 double 类型。

decimal
decimal 类型用于存储精确的小数，若使用 float 类型来取代一些需要精确小数点类型的字段时，大的数据量会导致数据错误，比如金额,若使用 float 类型，可能会丢失精度，此时对于金额这样对精度要求很高的字段来说，可以选择使用 decimal 类型

三. 日期和时间类型

类型	大小	格式	范围
date	3字节	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
time	3-6字节	HH::MM:SS[.微秒]	-838:59:59 ~ 838:59:59
year	1字节	YYYY	1901 ~ 2155
datetime	5-8字节	YYYY-MM-DD HH:MM:SS[.微秒值]	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 UTC
timestamp	4-7字节	YYYY-MM-DD HH:MM:SS[.微秒值]	1970-01-01 00:00：00 ~ 2038-01-19 03:14:07 UTC

date
date 类型为日期类型，存储空间 3个字节，格式为 “YYYY-MM-DD”，例如 “2020-03-16”,日期的范围 “1000-01-01 ~ 9999-12-31”，通常只想用来存储如 “2020-02-02” 这种格式的日期字段时，可以选择使用 date 类型。

time
time 类型为时间类型，存储空间 3 ~ 6个字节，格式为 “HH::MM:SS[.微秒]” ，例如 “04:31:22.33”，范围 “-838:59:59 ~ 838:59:59”，通常只想用来存储如 “04:31:22.33” 这种格式的时间字段时，可以选择使用 time 类型。

year
year 类型为日期类型，存储空间 1 个字节 ，例如 “2020”，范围 “1901 ~ 2155”，通常只想用来存储如 “2020” 这种格式的日期字段时，可以选择使用 year 类型。

datetime
datetime 类型为精确时间的日期类型，存储空间为 5 ~ 8 个字节，格式为 “YYYY-MM-DD HH:MM:SS[.微秒值]”，例如 “2020-02-02 02:02:02.02”，范围 “1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 UTC”，通常只想用来存储如 “2020-02-02 02:02:02.02” 这种格式的日期字段时，可以选择使用 datetime 类型。

timestamp
timestamp 类型保存了从1970年01月01日午夜以来的秒数，它和 UNIX 时间戳相同，存储空间 4 ~ 7 个字节，范围 “1970-01-01 00:00：00 ~ 2038-01-19 03:14:07 UTC”，timestamp 显示的值依赖于时区，和 datetime 相比，前者提供的值与时区有关系，后者则保留文本表示的日期和时间。

四. 字符串类型

类型	范围	说明
char	1~255个字符	固定长度
varchar	字段存储所占字节数不能超过65535字节	可变长度
tinytext	最大长度255字节	可变长度
text	最大长度65535个字节	可变长度
mediumtext	最大长度16777215个字节	可变长度
longtext	最大长度4294967295个字节	可变长度

char
char 类型为定长字符串类型，适合存储很短的字符串，或者所有值都接近同一个长度，例如存储密码 md5 值，因为它是一个定长的值，对于经常变更的数据，char 比 varchar 更好，因为定长的 char 不容易产生碎片。

varchar
varchar 类型用于存储可变长度字符串，是最常见的字符串数据类型，对于短长度的字符串，它比定长的更为节省空间，varchar 节省空间对性能有帮助，varchar 类型的字符串有如下特点 :

字符串列的最大长度比平均长度大；
列的更新少，碎片问题较小；
使用了如 “UTF-8” 字符集，每个字符都使用不同的字节数来存储。

text
text 数据类型家族包括 : tinytext、text、mediumtext、longtext。 MySQL 把每个 text 值当做一个独立的对象处理。若 text 家族的值太大时，InnoDB 存储引擎会额外使用存储区域来进行存储，通常存储文章这样比较长的内容字段时，可选择 text 家族类型。

五. 枚举值类型

类型	范围	说明
enum	几何数最大65535	枚举值类型,只能插入列表中指定的值

enum
enum 类型为枚举值类型，有时可以使用枚举值替代字符串，枚举列可以把一些不重复的字符串存储为一个预定义的集合。 MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到 1 ~ 2 个字节，例如 ENUM （‘黄色’，‘黑色’，‘蓝色’，‘紫色’），枚举值类型字段的需求也可以使用 tinyint 类型来替代。

六. 二进制类型

类型	范围	说明
tinyblob	最大长度255字节	可变长度
blob	最大长度65535个字节	可变长度
mediumblob	最大长度16777215个字节	可变长度
longblob	最大长度4294967295个字节	可变长度

blob
blob 数据类型家族包括 ：tinybob、blob、mediumblob、longblob。和 text 家族一样，MySQL 把每个 text 值当做一个独立的对象处理。若 blob 家族的值太大时，InnoDB 存储引擎会额外使用存储区域来进行存储，blob 类型存储的是二进制数据,没有排序规则或字符集。

七. 小结

1. MySQL 数值类型大小和取值范围，实际应用中要根据字段在业务中实际表示的含义来选择数值字段数据类型，例如年龄字段 age 选择无符号 TINYINT 类型，金额数值对精度要求比较高，所以选择 DECIMAL 类型比较合适，时间戳类型使用 INT，对于数据量比较大的表的自增主键 id 可选择 BIGINT，具体字段类型的选择，还需要根据实际情况来综合分析，合理选择即可，字段的默认值尽量避免 NULL。
2. MySQL 日期时间类型和字符串类型，需要注意的是时间类型 TIME、DATETIME、TIMESTAMP 存储大小具体取决于微秒值，例如 TIME 类型，当微秒不存在的时候如 “12:12:12” 字节数为 3 字节（微秒 0 字节）"12:12:12.72"表示微秒 2 位，总共4字节（微秒1字节），“12:12:12.6173” 表示微秒 4 位，总共 5 字节（微秒2字节），“12:12:12.627127” 表示微秒 6 位，总共 6 字节，微秒字节位数对表如下：

微秒位数	大小	举例
0	0字节	12:12:12
1	1字节	12:12:12.3 或 12:12:12.32
3	2字节	12:12:12.326 或 12:12:12.3653
5	3字节	12:12:12.35812 或 12:12:12.352161

3. char和varchar的区别
   char 类型是一种固定长度的类型，varchar 则是一种可变长度的类型，它们的区别是：char(M) 类型的数据列里，每个值都占用 M 个字节，如果某个长度小于 M，MySQL 就会在它的右边用空格字符补足