1. 开始

为了能更好地理解与使用MySQL，根据MySQL文档花点时间整理了一下MySQL的数据类型。MySQL中一共有五大类数据类型，如下图：

这篇文章只对其中的数字类型、字符串类型和时间类型进行总结。

在这些数据类型的使用中，有几个需要提前知道的地方：

1. 在整数中，M代表最大的展示宽度；而在浮点数和定点数中，M代表可以存储的总位数（也就是精度,precision）；对于字符串类型，M代表所能存储的最长字符数。不同类型的M也是不一样的；
2. D适用于浮点数和定点数，表示小数点后面的位数。D最大值是30，但是不能大于M-2；
3. fsp适用于时间类型，表示小于秒的精度。这个值在0和6之间，也就是说MySQL的时间可以精确到微秒级；
4. 类型定义中的[和]表示可选项。

不管什么数据类型，计算机的存储都是一样的，不同在于对于底层存储数据的解释不同。

接下来，详细看看各种数据类型是怎么存储的，以及MySQL是怎么解释的。

2. Numeric类型

数字类型还可以细分成如下的几种类型：整数、实数和位数据类型。

数字类型支持有符号和无符号属性，分别是UNSIGNED和SIGNED。其中SIGNED可以不写在类型定义中，默认就是有符号的。

2.1 Integer类型

整数类型根据所占空间大小可以分为五种：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。其中INT也是INTEGER。

按照有无符号又可以分为两种：SIGNED和UNSIGNED，其中默认是有符号的，只需要在指定无符号时使用UNSIGNED。

整数类型基本情况如下表：

有符号与无符号类型使用相同的存储空间，也具有相同的性能，可以根据需要来选择。不过如果选择了无符号类型，当插入的数据是负数时，会保存为0，需要注意。

对于整数的M，比如INT(11)，对于大多数应用这是没有意义的，因为这不会限制值的合法范围，只是规定了MySQL的一些交互工具（比如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(11)是相同的。

还有一个类型，BOOL(也可以是BOOLEAN)，这个类型和TINYINT(1)一样，0为false，非零为true。但是在MySQL中，TRUE就是1FALSE就是0：

SELECT IF(2 = TRUE, 'true', 'false');

结果是false。

2.2 实数类型

MySQL中除了整数还可以储存实数。对于实数，MySQL既支持精确类型（定点），也支持不精确类型（浮点）。

对于精确实数类型，MySQL中有DECIMAL，也可以叫做DEC和NUMERIC。

DECIMAL类型定义如下：

salary DECIMAL(5,2)

其中5就是精度2就是小数点后面的位数。精度也就是数字的总位数，这样salary的范围就是-999.99到999.99。

对于不精确类型，MySQL有FLOAT和DOUBLE类型，其中FLOAT使用4个字节（也有可能使用8个字节），而DOUBLE使用8个字节进行存储。

对于FLOAT，还可以这样来指定类型：FLOAT(p)，可以用来指定存储空间大小，具体的大小和p有关。

浮点类型通常在相同精度时比DECIMAL所需空间少，所以应该尽量在需要精确计算时使用DECIMAL。

下面列出了不同类型所占的空间：

对于DECIMAL情况有些复杂，MySQL将数字打包成二进制字符串来存储，每9个数字使用4个字节，并且对于整数部分和小数部分分开计算。计算规则如下：

1. 每9个数字使用4个字节；
2. 不够9个数字的通过下面的表格确定；
3. 将整数部分和小数部分所占空间相加即是整体空间。

不够9个数字的部分所占空间：

比如，对于123.123，整数部分3个数字不够9个，查表使用2个字节；同理小数部分也使用2个字节，一共4个字节。

对于12345.1，整数部分使用3个字节，小数部分使用1个字节，一共4个字节。

2.3 位数据类型

BIT类型可以存储比特位，BIT(M)可以存储M个比特位，其中M可以取1到64之间的任意值。。

对于BIT类型，不同的存储引擎存储方式不同。MyISAM会把所有的比特位打包存储，所以19个单独的BIT列只需要19位来存储，所以一共3个字节就可以了；但是对于InnoDB来说，会为每个BIT列使用一个足够小的最小整数类型来存储，所以不能节省空间。

还有一点需要注意的是，MySQL是使用字符串来存储的，而不是ASCII码的”0”或”1”。但是在数字上下文的场景使用时，结果是把位字符串转换成了数字。比如，如果存储一个b'00111001'（二进制值是57）到BIT(8)中并检索使用，得到的内容是字符码为57的字符串，也就是得到了字符”9”，但是在数字上下文中，得到的是57：

CREATE TABLE bittest(a bit(8));
INSERT INTO bittest VALUES (b'00111001');
SELECT a, a+0 FROM bittest;

结果：

+------+------+
| a    | a+0  |
+------+------+
| 9    |   57 |
+------+------+

一个建议就是，尽可能不使用BIT类型。

3. String类型

3.1 VARCHAR和CHAR类型

VARCHAR和CHAR是两种主要的类型，虽然相似，但是在好多方面各不相同。而且，两种类型在磁盘和内存中的存储方式也和存储引擎有关，这里的描述主要是InnoDB和MyISAM。

区别一：VARCHAR是变长的，CHAR是定长的。

VARDCHAR根据存储的内容使用不同的空间，最长长度定义时指定；而CHAR使用定义时指定的空间。如果存储的值超过了指定的空间，两种类型都会截断超过的部分。

区别二：两种类型所能存储的最长字符串不同。

VARCHAR可以最多存储65535个字符；而CHAR最多能存储255个字符。最长字符都可以在定义时指定。

区别三：填充方式

由于CHAR是定长的，如果存储的值不够指定长度，会在末尾填充空格；而VARCHAR不会填充空格。

区别四：空间占用

CHAR是定长的，所以指定的长度就是真正需要的空间，不过还和使用的字符集有关。如果使用latin1字符集，那么CHAR(4)就使用4个字节存储。

VARCHAR是变长的，所以需要额外记录字符串的长度。对于字符数小于等于255，额外需要一个字节存储长度；字符数超过255，需要两个字节。

区别五：末尾空格

在区别三种提到了CHAR会在后面使用空格填充，但是如果数据本身后面就有空格的话，两个类型处理方式就不同了。CHAR当然会保存空格，但是在检索时会把空格去掉；而VARCHAR不会：存入什么，得到什么。

CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
INSERT INTO vc VALUES ('ab  ', 'ab  ');
SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;

结果：

+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+

下面的表格演示了两者的区别：

VARCHAR可以节省空间，不过由于是变长的，在更新时可能会比原来的长，这就需要额外的操作。如果一个行占用的空间增长，并且在页中没有更多的空间的话，不同的存储引擎处理方式不同。MyISAM会将行拆分成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度，比如MD5值。对于经常变更的数据，CHAR也比VARCHAR好，因为不会产品碎片。

3.2 VARBINARY和BINARY类型

VARBINARY和BINARY两种类型和VARCHAR和CHAR类似，不同在于，前两者储存的是二进制字符串（二进制字符码），而后两者存储的是常规字符串（字符）。

这样有一个问题就是，虽然前两者的长度限制和后两者的一样，但是前两者计算的是字节数，而后两者计算的字符数，这在有些单个字符使用多个字节的字符集中会有所不同。

还有一点需要注意，BINARY会在数据后面填充\0而不是空格，在检索的时候也不会删除后面的\0。

二进制字符的优势在于比较。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值比较。这会比字符比较更快。

3.3 BLOB和TEXT类型

BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

其实它们也有各自的类型族：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB和TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

当BLOG和TEXT过大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1-4个字节来存储一个指针，指向外部存储区域的实际值。

MySQL对于BLOB和TEXT列进行排序与其它类型不同：只对最前面的max_sort_length字节而不是整个字符串进行排序。

MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

3.4 ENUM类型

有时候可以使用枚举类型代替常用的字符串类型。枚举类型可以把一些不重复的字符串存储成一个预定义的集合。枚举类型使用一个或两个字节来编码枚举值，最多可以用65535个不同的枚举值。

创建一个枚举值：

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

这也是使用枚举类型的一个缺点，当需要增加枚举值时，只能使用ALTER TABLE：

ALTER TABLE shirts MODIFY COLUMN size ENUM('x-small', 'small', 'medium', 'large', 'x-large', 'xx-large');

存储枚举值时使用的是数字而不是对应的字符串：

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT * FROM shirts;

结果：

+-------------+--------+
| name        | size   |
+-------------+--------+
| dress shirt | large  |
| t-shirt     | medium |
| polo shirt  | small  |
+-------------+--------+

不过在数字上下文中，可以看到真实存储的值：

SELECT size+0 FROM shirts;

结果：

+--------+
| size+0 |
+--------+
|      4 |
|      3 |
|      2 |
+--------+

因此：千万不要使用数字作为枚举值。

还有一点，枚举字段是根据存储的数字来排序的而不是定义的字符串：

SELECT size FROM shirts ORDER BY size;

结果：

+--------+
| size   |
+--------+
| small  |
| medium |
| large  |
+--------+

3.5 SET类型

一位可以存储一个只有true/false值，如果有很多true/false值的话，可以使用SET类型，它在MySQL中打包存储，可以有效节省空间，最多可以存储64个true/false值，只需要8个字节。

和ENUM一样，一个主要的缺点是添加新的定义时需要使用ALTER TABLE。而且，也不能在SET上使用索引。

作为一个替代方案，可以使用一个整数来包装一系列的位，然后再应用中操作这个整数。不过是使用这个方法还是使用SET依据每个人的偏好。

4. Date and Time类型

MySQL中的时间和日期类型有如下几种：DATE、TIME、DATETIME、TIMESTAMP和YEAR。每种类型都有自己对应的零值和取值范围：

4.1 DATE、DATETIME和TIMESTAMP类型

这三种类型都含有年月日的信息，但是三种类型又有一些不同。

DATE类型只有日期而没有时间；DATETIME和TIMESTAMP除了日期还有具体的时间。

DATETIME和TIMESTAMP的一个区别是两者的范围不同。DATETIME将日期存储为YYYYMMDDHHMMSS格式的整数中，使用8个字节；而TIMESTAMP使用4个字节存储从1970年1月1日以来的秒数，所以范围小了很多。

另外一个区别就是TIMESTAMP依赖时区而DATETIME不包含时区。MySQL服务器、操作系统以及客户端连接都可以设置时区。

TIMESTAMP也有一些DATETIME没有的属性，比如可以设置插入和更新时TIMESTAMP列的行为。

在MySQL 5.6之后DATETIME和TIMESTAMP增加了时间的精确度，在原来秒的基础上精确度增加到了微秒级别，不过需要在定义中指定精确度，比如DATETIME(6)，括号中的数字就是时间的精确度，取值范围是0-6。

如果指定了额外的精确度的话，MySQL需要额外的空间来存储，所需的空间和精度有关：

4.2 TIME类型

TIME类型的存储范围并不是感觉上的'00:00:00'到'23:59:59'，而是'-838：59：59'到'838:59:59'，这样就可以表示是两个事件之间的时间差了。

MySQL中TIME的格式是hh:mm:ss，使用3个字节来存储。

在对TIME列进行赋值时，需要注意的是，11:12的结果是'11:12:00'，而不是00:11:12；'1112'的结果是'00:11:12'；'12'的结果是'00:00:12'。不过建议不要这么做。

同样，TIME类型在MySQL 5.6 版本之后添加了微秒级的精度，只需要在定义的时候指定精度即可，所需的额外空间就是上面的表格。

4.3 YEAR类型

YEAR类型是使用1个字节来存储的数据类型，用来表示年份。在MySQL中，有两种：YEAR(4)和YEAR(2)。

YEAR(4)可以表示的范围是'1901'到'2155'；

YEAR(2)可以的取值范围是'0'到'99'，可以表示的范围是'1970'到'2069'。

由于范围小，尽量不要使用YEAR(2)。

5. 选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的类型对于性能来说也很重要。下面是选择数据类型时的一些原则。

1. 一般情况下，应该尽量使用可以正确存储数据的最小数据类型；
2. 使用简单的数据类型。比如，使用MySQL的内建类型来存储时间而不是字符串，使用整型次数IP地址而不是字符串；
3. 通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值；
4. 尽量使用相同的数据类型来存储相似或相关的值，尤其是需要在关联条件中使用的列；
5. 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存；
6. 避免使用MySQL已经遗弃的特性，比如指定浮点数的精度，或者整数的显示宽度；
7. 小心使用ENUM和SET，不要滥用；
8. 谨慎使用BIT，对于大部分应用，最好避免使用这个类型。