MySQL 之数据类型
前言
很多时候总感觉数据类型很简单，但是真正用的时候确总是很犹豫，不知道用哪个好，仔细了解他们之间的区别能让我们更准确的选择数据类型。
最近看到《MySQL 5.5从零开始学》中详细讲解了基础数据类型和区别，记录一下。
数据类型介绍
MySQL 支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。
- 数值类型：包括整数类型
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE、定点小数类型DECIMAL。 - 日期/时间类型：
YEAR、TIME、DATE、DATETIME和TIMESTAMP。 - 字符串类型：
CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET。
整数类型
MySQL中的整数型数据类型：
| 类型名称 | 存储需求 |
|---|---|
| TINYINT | 1个字节 |
| SMALLINT | 2个字节 |
| MEDIUMINT | 3个字节 |
| INT(INTEGER) | 4个字节 |
| BIGINT | 8个字节 |
我们经常会把数据类型设置成INT(11)，这后面的数字11表示的是该数据类型指定的显示宽度。例如，假设声明一个 INT 类型的字段：
create table t1(year int(4));
我们插入两条数据：
insert into t1(year) values(1999),(19999);
可以插入，而且没有警告，然后查看显示如图：
结果1
注意：显示宽度和数据类型的取值范围是无关的，显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。
浮点数类型和定点数类型
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| FLOAT | 单精度浮点数 | 4个字节 |
| DOUBLE | 霜精度浮点数 | 8个字节 |
| DECIMAL(M,D) | 压缩的“严格”定点数 | M+2个字节 |
DECIMAL 类型不同于 FLOAT 和 DOUBLE ，DECIMAL 实际是以串存放的，由表也可以看到 DECIMAL 的存储空间不是固定的，而由其精度 M 决定，占用 M+2 个字节。例如，假设声明一个 DECIMAL 类型的字段：
create table ta(a float,b decimal(10,5));
我们分边插入数据：
insert into ta(a,b) values(1,12345,123423);
可以插入，但是有一个警告，然后查看显示：
结果2
把小数点后面的第六位截断，我们再插入一条数据：
insert into ta(a,b) values(1,123456,12342);
结果提示超出范围：
结果3
结论：DECIMAL 类型的数据当插入的小数部分有多余的位数，截断后插入数据库；当插入的整数部分的值超过了其表示范围则不会插入到数据库。
定点数与浮点数的比较：
- 在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币，科学数据等）使用 DECIMAL 的类型比较好。
- 浮点 FLOAT、DOUBLE 相对于定点数 DECIMAL 的优势是在一定的情况下，浮点数能表示更大的范围数据，但是浮点数容易产生误差。在数据库迁移中，float(M,D) 是非标准 SQL 定义，数据库迁移可能会出现问题，最好不要这样使用。两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点型是需要注意，并尽量避免做浮点数比较。
日期与时间类型
| 类型名称 | 日期格式 | 日期范围 | 存储需求 |
|---|---|---|---|
| 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-3 | 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个字节 |
在这里提一下** CURRENT_DATE 和 NOW() 的区别**：CURRENT_DATE 返回当前日期值，不包括时间部分，NOW() 函数返回日期和时间值。
提示：TIMESTAMP 和 DATATIME 除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而 TIMESTAMP 值的存储是以 UTC （世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。
字符串类型
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| 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^32 |
| ENUM | 枚举类型，只能存一个枚举字符串值 | 1或2个字节，取决于枚举值的数目（最大值65535） |
| SET | 一个设置，字符串对象可以有零个或多个 SET 成员 | 1、2、3、4或8个字节，取决于集合成员的数量（最多64个成员） |
1、CHAR 和 VARCHAR 类型
- CHAR(M) 为固定长度字符串，VARCHAR(M) 是长度可变的字符串.
- CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度快，但是确定是浪费空间。
- 当检索 CHAR 值时，尾部的空格将被删除掉。VARCHAR 在值保存和检索时尾部的空格仍保留。
- 字符串类型的 M 值是存储的最大字节数，不是显示宽度，如果插入的字符超过了 M 值，则不允许插入。不要与整数类型的 M 值搞混。
2、TEXT 类型
- TEXT 类型保存非二进制字符串，如文章内容、评论等，当保存或查询 TEXT 列的值时，不删除尾部空格。
3、ENUM 类型和 SET 类型
- ENUM 与SET 都是枚举类型，不同的是，ENUM 字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。
二进制类型
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| 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^24 |
| LONGBLOB(M) | 非常大的的BLOB | L+4个字节，L<2^32 |
BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。但由于现在图片和音频越来越多，检索起来也不方便，所以都不放在数据库，一般放在专门的文件存储服务器上。
