Mysql 数据类型
起因
Mysql 底层 其实就是把数据存储到了表里边,而表又是由行和列组成的,我们拿学生表举例子
| 学号 | 姓名 | 性别 | 年龄 | 班级 |
|---|---|---|---|---|
| 1 | 张三 | 男 | 18 | 1 班 |
| 2 | 李四 | 女 | 19 | 1 班 |
| 3 | 王五 | 男 | 20 | 2 班 |
表里的一行就代表一个学生的基本信息，这一行中的某一列就代表这个学生基本信息中的一项属性，也就是说学号是学生的一项属性、姓名也是学生的一项属性，其他的列也都是这个学生的属性。但是这些属性都有一定格式，比如说学号必须是整数格式的，入学时间必须是日期格式的，其他的属性都是字符串格式的，不同格式的数据是不能随便乱填的，你把一个日期格式的数据填在了性别里，岂不是闹出了笑话。所以设计 MySQL 的大叔们针对属性的不同格式定义了不同的数据类型，我们接下来就要详细唠叨 MySQL 中具体有哪些数据类型。
数值类型
整型
很显然，使用的字节数越多，意味着能表示的数值范围就越大，但是也就越耗费存储空间。根据表示一个数占用字节数的不同，MySQL 把整数划分成如下所示的类型：
| 类型 | 字节 | 无符号取值范围 | 有符号取值范围 | 含义 |
|---|---|---|---|---|
| TINYINT | 1 | 0 ~ 2⁸-1 | 2⁷ ~ 2⁷-1 | 非常小的整数 |
| SMALLINT | 2 | 0 ~ 2¹⁶-1 | -2¹⁵ ~ 2¹⁵-1 | 小的整数 |
| MEDIUMINT | 3 | 0 ~ 2²⁴-1 | -2²³ ~ 2²³-1 | 中等大小的整数 |
| INT（别名：INTEGER） | 4 | 0 ~ 2³²-1 | -2³¹ ~ 2³¹-1 | 标准的整数 |
| BIGINT | 8 | 0 ~ 2⁶⁴-1 | -2⁶³ ~ 2⁶³-1 | 大整数 |
浮点数类型
很显然，我们表示一个浮点数使用的字节数越多，表示尾数和指数的范围就越大，也就是说可以表示的小数范围就越大，设计 MySQL 的大叔根据表示一个小数需要的不同字节数定义了如下的两种浮点数类型：
| 类型 | 字节 | 绝对值最小非 0 值 | 绝对值最大非 0 值 | 含义 |
|---|---|---|---|---|
| FLOAT | 4 | ±1.175494351E-38 | ±3.402823466E+38 | 单精度浮点数 |
| DOUBLE | 8 | ±2.2250738585072014E-308 | ±1.7976931348623157E+308 | 双精度浮点数 |
- 设置最大整数位数和小数位数
在定义浮点数类型时，还可以在 FLOAT 或者 DOUBLE 后边跟上两个参数，就像这样：
FLOAT(M, D)
DOUBLE(M, D)对于我们用户而言，使用的都是十进制小数。如果我们事先知道表中的某个列要存储的小数在一定范围内，我们可以使用 FLOAT(M, D)或者 DOUBLE(M, D)来限制可以存储到本列中的小数范围。其中：
(1) M表示该小数最多需要的十进制有效数字个数。
注意是有效数字个数，比方说对于小数-2.3 来说有效数字个数就是 2，对于小数 0.9 来说有效数字个数就是 1。
(2) D表示该小数的小数点后的十进制数字个数。
这个好理解，小数点后有几个十进制数字，D 的值就是什么。
举个例子看一下，设置了 M 和 D 的单精度浮点数的取值范围的变化：
| 类型 | 取值范围 |
|---|---|
| FLOAT(4, 1) | -999.9~999.9 |
| FLOAT(5, 1) | -9999.9~9999.9 |
| FLOAT(6, 1) | -99999.9~99999.9 |
| FLOAT(4, 0) | -9999~9999 |
| FLOAT(4, 1) | -999.9~999.9 |
| FLOAT(4, 2) | -99.99~99.99 |
可以看到，在 D 相同的情况下，M 越大，该类型的取值范围越大；在 M 相同的情况下，D 越大，该类型的取值范围越小。当然，M 和 D 的取值也不是无限大的，M 的取值范围是 1~255，D 的取值范围是 0~30，而且 D 的值必须不大于 M。M 和 D 都是可选的，如果我们省略了它们，那它们的值按照机器支持的最大值来存储。
定点数类型
正因为用浮点数表示小数可能会有不精确的情况，在一些情况下我们必须保证小数是精确的，所以设计 MySQL 的大叔们提出一种称之为定点数的数据类型，它也是存储小数的一种方式：
| 类型 | 字节 | 取值范围 |
|---|---|---|
| DECIMAL(M, D) | 取决于 M 和 D | 取决于 M 和 D |
此处的 M 和 D 的含义与浮点数中的含义一样。
- 默认
对于定点数类型 DECIMAL(M, D)来说，M 和 D 都是可选的，默认的 M 的值是 10，默认的 D 的值是 0，也就是说下列等式是成立的：
DECIMAL = DECIMAL(10) = DECIMAL(10, 0)
DECIMAL(n) = DECIMAL(n, 0)另外 M 的范围是 1~65，D 的范围是 0~30，且 D 的值不能超过 M。
无符号数值类型的表示
对于数值类型，包括整数、浮点数和定点数，有些情况下我们只需要用到无符号数（就是非负数）。MySQL 给我们提供了一个表示无符号数值类型的方式，就是在原数值类型后加一个单词 UNSIGNED：
数值类型 UNSIGNED大家可以把它当成一种新类型对待，比如 INT UNSIGNED 就表示无符号整数，FLOAT UNSIGNED 表示无符号浮点数，DECIMAL UNSIGNED 表示无符号定点数。
日期和时间类型
我们有很多场景需要表示时间或日期，比如学生基本信息中的入学时间就需要用日期的格式保存。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' | 时间戳 |
- YEAR
YEAR 类型也可以写成 YEAR(4)，它单纯表示一个年份值，取值范围为 1901 ～ 2155，仅仅占用 1 个字节大小而已。因为可以存储的年份值有限，如果我们想存储更大范围的年份值，可以不使用 MySQL 自带的 YEAR 类型，换成 SMALLINT（2 字节）或者字符串类型啥的都可以。
- DATE、TIME 和 DATETIME
顾名思义，DATE 表示日期，格式是 YYYY-MM-DD；TIME 表示时间，格式是 hh:mm:ss[.uuuuuu]或者 hhh:mm:ss[.uuuuuu]（有时候要存储的小时值是三位数），DATETIME 表示日期+时间，格式是 YYYY-MM-DD hh:mm:ss[.uuuuuu]。其中的 YYYY、MM、DD、hh、mm、ss、uuuuuu 分别表示年、月、日、时、分、秒、小数秒。
需要注意的是，DATETIME 中的时间部分表示的是一天内的时间(00:00:00 ~ 23:59:59)，而 TIME 表示的是一段时间，而且可以表示负值。
- TIMESTAMP
1970-01-01 00:00:00 注定是一个特殊的时刻，我们把某个时刻距离 1970-01-01 00:00:00 的秒数称为时间戳。比方说当前时间是 2018-01-24 11:39:21，距离 1970-01-01 00:00:00 的秒数为 1516765161，那么 2018-01-24 11:39:21 这个时刻的时间戳就是 1516765161。不过在 MySQL5.6.4 之后，时间戳的值也可以加入小数秒。
用时间戳存储时间的好处就是，它展示的值可以随着时区的变化而变化。比方说我们把 2018-01-24 11:39:21 这个时刻存储到一个 TIMESTAMP 的列中，那么在中国你看到的时间就是 2018-01-24 11:39:21，如果你去了日本，他们哪里的使用的是东京时间，比北京时间早一个小时，所以他们那显示的就是 2018-01-24 12:39:21。而如果你用 DATETIME 存储 2018-01-24 11:39:21 的话，那不同时区看到的时间值都是一样的。
字符串类型
现在我们可以看一下 MySQL 中提供的各种字符串类型（注：其中M 代表该数据类型最多能存储的字符数量，L 代表我们实际向该类型的属性中存储的字符串在特定字符集下所占的字节数，W 代表在该特定字符集下，编码一个字符最多需要的字节数）：
| 类型 | 最大长度 | 存储空间 | 含义 |
|---|---|---|---|
| CHAR(M) | M 个字符 | M×W 个字节 | 固定长度的字符串 |
| VARCHAR(M) | M 个字符 | L+1 或 L+2 个字节 | 可变长度的字符串 |
| TINYTEXT | 2⁸-1 个字节 | L+1 个字节 | 非常小型的字符串 |
| TEXT | 2¹⁶-1 个字节 | L+2 个字节 | 小型的字符串 |
| MEDIUMTEXT | 2²⁴-1 个字节 | L+3 个字节 | 中等大小的字符串 |
| LONGTEXT | 2³²-1 个字节 | L+4 个字节 | 大型的字符串 |
- CHAR(M)类型
CHAR(M)中的 M 代表该类型最多可以存储的字符数量，注意，是字符数量，不是字节数量。其中 M 的取值范围是 0~255。如果省略掉 M 的值，那它的默认值就是 1，也就是说 CHAR 和 CHAR(1)是一个意思。CHAR(0)是一种特别的类型，它只能存储空字符串''或者 NULL 值（我们后边会详细介绍啥是个 NULL）。再回头看一眼我们的学生基本信息表，如果你觉得学生的姓名不会超过 5 个字符，你就可以指定这个姓名列的类型为 CHAR(5)。
- VARCHAR(M)类型
如果你表中的某个列需要存储字符串类型的数据，而且这些字符串长短不一，那么使用 CHAR(M)可能会浪费很多存储空间，VARCHAR(M)正是为了解决这个问题而生的。
VARCHAR(M)中的 M 也是代表该类型最多可以存储的字符数量，理论上的取值范围是 1~65535。但是 MySQL 中还有一个规定，表中某一行包含的所有列中存储的数据大小总共不得超过 65535 个字节（注意是字节），也就是说 VARCHAR(M)类型实际能够容纳的字符数量是小于 65535 的。
- 各种 TEXT 类型
虽然 VARCHAR(M)已经可以存储很长的字符串了，可是有时候还是不够咋办？对于很长的字符串，设计 MySQL 的大叔们给我们提供了 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT 四种可以存储大型的字符串的类型。它们也都是变长类型，也就是说这些类型占用的存储空间由实际内容和内容占用的字节长度两部分构成。
因为 TINYTEXT 最多可以存储 2⁸-1 个字节，所以内容占用的字节长度用 1 个字节就可以表示
TEXT 最多可以存储 2¹⁶-1 个字节，所以内容占用的字节长度用 2 个字节就可以表示。
MEDIUMTEXT 最多可以存储 2²⁴-1 个字节，所以内容占用的字节长度用 3 个字节就可以表示。
LONGTEXT 最多可以存储 2³²-1 个字节，所以内容占用的字节长度用 4 个字节就可以表示。
ENUM 类型和 SET 类型
视角回到我们的学生信息表，性别一列也需要填写字符串，但是比较特殊的一点是，这一列只能填男或者女，填别的字符串就尴尬了！针对这种情况，我们提出了一个叫 ENUM 的类型，也称为枚举类型，它的格式如下：
ENUM('str1', 'str2', 'str3' ⋯)它表示在给定的字符串列表里选择一个。比如我们的性别一列可以定义成 ENUM('男', '女')类型。这个的意思就是性别一列只能在'男'或者'女'这两个字符串之间选择一个，相当于一个单选框～
有的时候某一列的值可以在给定的字符串列表中挑选多个，假设学生的基本信息加了一列兴趣属性，这个属性的值可以从给定的兴趣列表中挑选多个，那我们可以使用 SET 类型，它的格式如下：
SET('str1', 'str2', 'str3' ⋯)它表示可以在给定的字符串列表里选择多个。我们的兴趣一列就可以定义成 SET('打球', '画画', '扯犊子', '玩游戏')类型。这个的意思就是兴趣一列可以在给定的这几个字符串中选择一个或多个，相当于一个多选框～效果就像这样：
| 学号 | 姓名 | ... | 兴趣 |
|---|---|---|---|
| 20180101 | 杜子腾 | ··· | '打球', '画画' |
| 20180102 | 杜琦燕 | ··· | '扯犊子' |
| 20180103 | 范统 | ··· | '扯犊子', '玩游戏' |
| 20180104 | 史珍香 | ··· | '画画', '扯犊子', '玩游戏' |
综上所述，ENUM 和 SET 类型都是一种特殊的字符串类型，在从字符串列表中单选或多选元素的时候会用得到它们。
二进制类型(用的少,一般都是地址)
BIT 类型
有时候我们有存储单个或者多个比特位的需求，此时就可以用到下边这种类型：
|类型|字节数|含义| |:--😐:--😐:--😐:--😐 |BIT(M)|近似为(M+7)/8|存储 M 个比特位的值|
其中 M 的取值范围为 1~64，而且 M 可以省略，它的默认值为 1，也就是说 BIT(1)和 BIT 的意思是一样的。
MySQL 是以字节为单位存储数据的，一个字节拥有 8 个比特位。如果我们想存储的比特位个数不足整数个字节，那么 MySQL 会偷偷的填充满，比方说：
BIT(1)类型仅仅需要存储 1 个比特位的数据，但是 MySQL 会为其申请(1+7)/8 = 1 个字节。
BIT(5)类型仅仅需要存储 5 个比特位的数据，但是 MySQL 会为其申请(5+7)/8 = 1 个字节。
BIT(9)类型仅仅需要存储 9 个比特位的数据，但是 MySQL 会为其申请(9+7)/8 = 2 个字节。
BINARY(M)与 VARBINARY(M)
BINARY(M)和 VARBINARY(M)对应于我们前边提到的 CHAR(M)和 VARCHAR(M)，都是前者是固定长度的类型，后者是可变长度的类型，只不过 BINARY(M)和 VARBINARY(M)是用来存放字节的，其中的 M 代表该类型最多能存放的字节数量，而 CHAR(M)和 VARCHAR(M)是用来存储字符的，其中的 M 代表该类型最多能存放的字符数量。
其他二进制
TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 是针对数据量很大的二进制数据提出的，比如图片、音频、压缩文件啥的。它们很像 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，不过各种 BLOB 类型是用来存储字节的，而各种 TEXT 类型是用来存储字符的而已。
INFO
对于比较大的二进制数据，比方说图片、音频、压缩文件什么的，通常情况下都不直接存储到数据库管理系统中，而是将它们保存到文件系统中，然后在数据库中之存放一个文件路径即可。