MySQL数据类型 详细讲解
MySQL数据类型
MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。
- 数值数据类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE、定点小树类型DECIMAL。
- 日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
- 字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。 1.1 整数类型 数值型数据类型主要用来存储数字，MYSQL提供了多种数值数据类型，不同的数据库有不同的取值范围，可以存储的值范围越大，其所存储的控件也会越大。MySQL主要提供整数类型有：TINYTINT、SMALLINT、MEDIUMINT、INT、BIGINT。整数类型属性的字段可以添加AUTO_INCREMENT自增约束条件。下表列出了MySQL中的数值类型。
类型名称 | 说明 | 存储需求 |
|---|---|---|
TINYINT | 很小的整数 | 1个字节 |
SMALLINT | 小的整数 | 2个字节 |
MEDIUMINT | 中等大小的整数 | 3个字节 |
INT | 普通大小的整数 | 4个字节 |
BIGINT | 大整数 | 8个字节 |
从表可知，不同类型整数存储所需的字节数是不同的，占用字节数最小的是TINYINT类型，占用字节最大的是BIGINT类型，相应的占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据的取值范围，例如TINYINT需要1个字节(8bits)来存储，那么TINYINT无符号数的最大值为2^8-1，即255；TINYINT有符号数的最大值为2^7-1，即127。其它类型如下表所示：
数据类型 | 有符号 | 无符号 |
|---|---|---|
TINYINT | -128~127 | 0~255 |
SMALLINT | -32768~32767 | 0~65535 |
MEDIUMINT | -8388608~8388607 | 0~16777215 |
INT | -2147483648~2147483647 | 0~4294967295 |
BIGINT | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
1.2 浮点数类型和定点数类型
MySQL中使用浮点数和定点数来表示小数。浮点类型有两种：单精度（FLOAT）和双精度（DOUBLE）。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M，N）来表示，其中M称为精度，表示总共的位数；N称为标度，是表示小数的位数。下表列出了小数类型存储的需求。
类型名称 | 说明 | 存储需求 |
|---|---|---|
FLOAT | 单精度浮点数 | 4个字节 |
DOUBLE | 双精度浮点数 | 8个字节 |
DECIMAL(M,D),DEC | 压缩的”严格”定点数 | M+2个字节 |
DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则取值范围将随M的变大而变大。
1.3 日期/时间类型
MySQL中有多种表示日期的数据类型，主要有：DATETIME、DATE、TIMESTAMP、TIME和YEAR。下表列出了MySQL日期/时间类型：
类型名称 | 日期格式 | 日期范围 | 存储需求 |
|---|---|---|---|
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 UTC ~ 2038-01-19 03:14:07 UTC | 4字节 |
1.4 字符串类型
字符串类型用来存储字符串数据，除了可以存储字符串之外，还可以存储其它数据，比如图片和声音二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找。MySQL中字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。下表列出了字符串数据类型。
类型名称 | 说明 | 存储需求 |
|---|---|---|
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^24 |
ENUM | 枚举类型,只能有一个枚举字符串值 | 1或2个字节,取决于枚举值数目(最大值65535) |
SET | 一个设置,字符串对象可以有0个或多个SET成员 | 1,2,3,4或8个字节，取决于集合成员的数量（最多64个成员） |
1.5 二进制类型
MySQL支持两类字符型数据：文本字符串和二进制字符串。MySQL中存储二进制字符串数据类型有：BIT、BINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。下表列出了二进制数据类型：
类型名称 | 说明 | 存储需求 |
|---|---|---|
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 |
2. 如何选择数据类型
MySQL提供了大量的数据类型，为了优化存储，提高数据库性能，在任何情况下均应该使用精确的类型。即在所有可以表示该列值的类型中，该类型使用的存储最少。
2.1 整数和浮点数
如果不需要小数部分，则使用整数来保存数据；如果需要小数部分，则使用浮点数来表示。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如，如果列的值范围为1-99999，若使用整数，则MEDIUMINT UNSIGNED是最好的类型；若需存储小数，则使用FLOAT类型。 浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此，如要求存储精度较高时，应选择DOUBLE。
2.2 浮点数和定点数
浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。但是由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。DECIMAL在MySQL中是以字符串存储的。在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。另外两个浮点数进行减法和比较运算时也容易出现问题，因此在进行计算的时候，一定要小心。如果进行数值比较，建议使用DECIMAL类型。
2.3 日期/时间类型
MySQL对于不同种类的日期和时间有多种数据类型，比如YEAR和TIME。如果只需记录年份，则使用YEAR即可，如果只记录时间，则使用TIME类型。 如果同时需要记录日期和时间则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP这个列取值时范围小于DATETIME的取值范围，因此存储范围较大的日期最好使用DATETIME。 TIMESTAMP也有一个DATETIME不具备的属性。默认情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此需要插入记录同时插入当前时间时，使用TIMESTAMP是方便的，另外TIMESTAMP在空间上比DATETIME更有效。
2.4 CHAR与VARCHAR之间的特点与选择
CHAR与VARCHAR之间的区别： CHAR是固定长度字符，VARCHAR是可变长度字符；CHAR会自动删除插入数据的尾部空格，VARCHAR不删除尾部空格。 CHAR是固定长度，所以它的处理速度比VARCHAR速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之可以使用VARCHAR类型来实现。
存储引擎对CHAR和VARCHAR的影响：
对于MyISAM存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用存储空间换查询时间。 对于InnoDB存储引擎：使用可变长的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际存的长度存储，比较节省空间，所以对磁盘I/O和数据存储总量比较好。
2.5 ENUM和SET
ENUM只能取单值，它的数据列表示一个枚举集合。它的合法值列表最多有65535个成员。因此，在需要从多个值中选取一个时，可以使用ENUM。比如：性别字段适合定义成ENUM类型，每次只能从’男’ 或 ‘女’中取一个值。 SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。在需要取多个值的时候，适合使用SET类型，比如：要存储一个兴趣爱好，最好使用SET类型。 ENUM和SET的值是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。
2.6 BLOB和TEXT
BLOB是二进制字符串，TEXT是非二进制字符串，二者均可存放大容量信息。BLOB主要存储图片、音频信息等，而TEXT只能存储纯文本文件，应分清二者存储的关系。
腾讯云开发者
