原则

MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于作出更好的选择。

更小的通常更好

更小的数据类型通常更快，因为占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。

简单就好

简单数据类型的操作通常需要更少的 CPU 周期、索引性能更好。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使得字符比整型更复杂。

有几个例子：

  • 使用日期与时间类型，而不是字符串来存储日期和时间，以便排序和格式转换。

  • 使用整型，而不是字符串来存储 IP 地址。MySQL 提供了两个函数来处理 IP 地址：

    1
    2
    3
    4
    5
    6
    -- 使用 INT unsigned 类型存储（存储范围：0 ~ 2^32-1）
    SELECT inet_aton('0.0.0.0');  --结果：0
    SELECT inet_aton('192.168.1.1');  --结果：3232235777
    SELECT inet_aton('255.255.255.255');  --结果：4294967295

    SELECT inet_ntoa('3232235777');  --结果：192.168.1.1

  • 使用定长二进制类型（如 binary），而不是字符串来存储散列值：

    • MD5 128 bit / 4 = 32 length
    • SHA-1 160 bit / 4 = 40 length
    • SHA-224 224 bit / 4 = 56 length
    • SHA-256 256 bit / 4 = 64 length
    • SHA-384 384 bit / 4 = 96 length
    • SHA-512 512 bit / 4 = 128 length
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    -- 示例表
    CREATE TABLE `t_hash` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
      `hash` varbinary(40) NOT NULL COMMENT '散列值',
      PRIMARY KEY (`id`),
      KEY `idx_hash` (`hash`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    -- 测试数据
    INSERT INTO t_hash(hash) values(md5('0.0.0.0'));
    INSERT INTO t_hash(hash) values(sha1('0.0.0.0'));
    INSERT INTO t_hash(hash) values('你好吗');

    -- 测试结果
    -- length() 函数返回值的单位为字节。
    -- 由于二进制类型无字符集，因此中文乱码
    select hash, length(hash), char_length(hash) from t_hash order by id;
    +------------------------------------------+--------------+-------------------+
    | hash                                     | length(hash) | char_length(hash) |
    +------------------------------------------+--------------+-------------------+
    | f1f17934834ae2613699701054ef9684         |           32 |                32 |
    | e562f69ec36e625116376f376d991e41613e9bf3 |           40 |                40 |
    | 浣犲ソ鍚?                                 |            9 |                 9 |
    +------------------------------------------+--------------+-------------------+

避免使用 NULL

NULL 列使得 MySQL 索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间（例如当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节），在 MySQL 里也需要特殊处理。如果计划在列上建索引，应该尽量避免。

常用数据类型

数字类型

比特类型

BIT[(M)] 比特类型，M 为 1~64 位比特。

b'value' 符号可用于指定比特值。value 是一组使用 0 和 1 编写的二进制值。例如 b'111' 和 b'10000000' 分别代表 7 和 128 。详见《Bit-Value Literals》。

如果赋值给小于 M 位长的 BIT(M) 类型列，则该值左侧用零填充。例如，为 BIT(6) 列赋值 b'101' 实际上等于赋值 b'000101'。

BIT(1) 常用来表示布尔类型，b'0' 表示 false，b'1' 表示 true。

1 byte = 8 bit。

整数类型（精确值）

MySQL 可以使用以下几种整数类型，存储的值的范围从“-2^(N-1)”到“2^(N-1)-1”，其中 N 是存储空间的位数（1 byte = 8 bit）：

类型 字节长度 取值范围（有符号） 取值范围（无符号） 备注
TINYINT 1 byte -2^7 ~2^7-1 0~2^8-1 同义词 BOOL、BOOLEAN ，0 为 false，!0 为 true
SMALLINT 2 bytes -2^15 ~2^15-1 0~2^16-1
MEDIUMINT 3 bytes -2^23 ~2^23-1 0~2^24-1
INT 4 bytes -2^31 ~2^31-1 0~2^32-1 同义词 INTEGER
BIGINT 8 bytes -2^63 ~2^63-1 0~2^64-1 SERIAL 等于 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名 ，适用于创建主键

INT[(M)] [UNSIGNED] [ZEROFILL] [AUTO_INCREMENT] 整数类型

  • M 最大显示宽度，例如 INT(11) ：

    这个属性对于大多数应用都是没有意义的：它不会限制值的合法范围，只是规定了 MySQL 的一些交互工具用来显示字符的个数，最大值为 255，一般配合 ZEROFILL 使用。对于存储和计算来说，INT(1) 和 INT(20) 是相同的，并不会影响该类型的占用字节数。

  • ZEROFILL 填充零：

    顾名思义就是用 “0” 填充的意思，也就是在数字位数不够（< M）的空间用字符 “0” 填满。只在一些交互工具中有效，例如 MyCli。

  • UNSIGNED 无符号：

    整数类型有可选的 UNSIGNED 属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如 tinyint UNSIGNED 可以存储的范围是 0255，而 tinyint 的存储范围是 -128127。

  • AUTO_INCREMENT 自动递增：

    在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。 一个表中最多只能有一个 AUTO_INCREMENT 列 。对于任何想要使用 AUTO_INCREMENT 的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。

定点类型（精确值）

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] 定点类型（fixed-point number），用于存储高精度数值，如货币数据。

M 是总位数（精度，precision），D 是小数点后的位数（标度，scale）。小数点和（对于负数） - 符号不计入 M。如果 D 为 0，则值不包含小数点或小数部分。如果指定 UNSIGNED，则不允许负值。DECIMAL 的所有基本运算 (+, -, *, /) 都以 65 位数的最大精度完成。

类型 M 精度范围（总位数） D 标度范围（小数位数） 备注
DECIMAL 0~65，默认 10 0~30，默认 0 同义词 DEC、NUMERIC、FIXED

例如 ：

1
salary DECIMAL(5,2)

可以存储在 salary 列中的值范围从 -999.99 ~ 999.99。

DECIMAL 以二进制格式存储值，每 4 个字节存 9 个数字。例如，DECIMAL(18,9) 小数点两边各存储 9 个数字，一共使用 9 个字节：小数点前的数字使用 4 个字节，小数点后的数字使用 4 个字节，小数点本身占 1 个字节。详见《Precision Math》。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL —— 例如存储财务数据。但在数据量比较大的时候，可以考虑使用 BIGINT 代替 DECIMAL ，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储的财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在 BIGINT 里，这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。

浮点类型（近似值）

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 单精度浮点类型（floating-point number），M 是总位数，D 是小数点后面的位数。如果 M 和 D 省略，值将存储到硬件允许的限制。单精度浮点数精确到约 7 位小数。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 双精度浮点类型（floating-point number），M 是总位数，D 是小数点后面的位数。如果 M 和 D 省略，值将存储到硬件允许的限制。双精度浮点数精确到小数点后 15 位。

类型 字节长度 取值范围
FLOAT 4 bytes -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38
DOUBLE 8 bytes -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and2.2250738585072014E-308 to 1.7976931348623157E+308

因为浮点值是近似值而不是作为精确值存储的，比值时可能会导致问题。详见《Problems with Floating-Point Values》。

字符串类型

CHAR 和 VARCHAR 类型

下列表格中，M 表示字符长度，L 表示实际字节存储长度。这两种类型很相似，但它们被存储和检索的方式不同。它们的最大字节长度 L 和尾部空格是否保留也不同。

类型 最大字节长度 尾部空格是否保留 描述 适用情况
VARCHAR(M) 65,535 bytes (2^16-1) 否 用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。其有效最大长度取决于行大小限制（默认 65,535 字节，在所有列中共享） 和使用的字符集（例如， UTF-8 字符集每个字符最多可能需要三个字节，因此最多可以声明为 21,844 个字符）。参加《表列数量和行数限制》。 字符的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像 UTF-8 这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR(M) 255 bytes (2^8-1) 是 用于存储定长字符串。长度不足时会填充尾部空格到指定的长度。 存储很短的字符串，或者所有值都接近同一个长度。例如，存储密码的 MD5 值；经常变更的数据，定长的 CHAR 类型不容易产生碎片。

不同于 CHAR 类型，VARCHAR 类型需要使用 1 或 2 个额外字节记录字符串的长度：如果列的最大长度小于或等于 255 字节，则只使用 1 个字节表示，否则使用 2 个字节：

L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes

下表通过存储各种字符串值到 CHAR(4) 和 VARCHAR(4) 列说明了 CHAR 和 VARCHAR 之间的差别（假设该列使用单字节字符集，例如 latin1）：

值 CHAR(4) 需要存储 VARCHAR(4) 需要存储
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

BLOB 和 TEXT 类型

TEXT 表示二进制字符串（字节字符串），没有排序规则或字符集。BLOB 表示非二进制字符串（字符串），有排序规则、字符集。与其它类型不同，MySQL 把每个 BLOB 和 TEXT 值当做一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当 BLOB 和 TEXT 值太大时，InnoDB 会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要 1~4 个字节存储一个指针，然后在外部存储区域存储实际的值。它们的最大字节长度如下：

类型 指针长度 实际值的最大字节长度
TINYTEXT、TINYBLOB 1 byte 256 bytes (2^8)
TEXT、BLOB 2 bytes 65,536 bytes (2^16)，64 KB
MEDIUMTEXT、MEDIUMBLOB 3 bytes 16,777,216 bytes (2^24)，16 MB
LONGTEXT、LONGBLOB 4 bytes 4,294,967,296 bytes (2^32)，4 GB

MySQL 不能将 BLOB 和 TEXT 列全部长度的字符串进行索引，也不能使用这些索引消除排序，因此可以使用“前缀索引”解决这个问题。

日期与时间类型

  • MySQL 有多种表示日期的数据类型，比如，当只记录年信息的时候，可以使用 YEAR 类型，而没有必要使用 DATE 类型。
  • 每一个类型都有合法的取值范围，当指定确实不合法的值时系统将 “零” 值插入到数据库中。
类型 字节长度 默认值（0 值） 取值范围 备注
YEAR 1 byte 0000 1901 ~ 2155
DATE 3 bytes 0000-00-00 1000-01-01 ~ 9999-12-31
TIME 3 bytes 00:00:00 -838:59:59 ~ 838:59:59
DATETIME 5 bytes 0000-00-00 00:00:00 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 DATETIME 把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关。默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2018-01-16 22:37:08”。这是 ANSI 标准定义的日期和时间显示方法。
TIMESTAMP 4 bytes 1970-01-01 00:00:00 UTC 1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC TIMESTAMP 显示的值依赖于时区。

存储精度

需要注意的是，MySQL 升级到 5.6 之后对日期与时间类型做过调整，可以精确到微秒并指定其精度（最多 6 位），参考 Changes in MySQL 5.6：

incompatible_change_of_date_and_time_type

参考 Date and Time Type Storage Requirements 下表列明了日期与时间类型在 MySQL 5.6.4 前后的变化：

date_and_time_type_storage_requirements

通过分析精确到小数部分的秒（Fractional Seconds Precision）所支持的最大十进制数值，并将其转换为二进制表示，可知为什么精度越高所需的存储空间越多：

Fractional Seconds Precision Maximum Decimal Representation Maximum Binary Representation Storage Required
0 0 0 (0 bit) 0 byte
1, 2 99 0110 0011 (8 bits) 1 byte
3, 4 9,999 0010 0111 0000 1111 (16 bits) 2 bytes
5, 6 999,999 0000 1111 0100 0010 0011 1111 (24 bits) 3 bytes

有关于时间值的内部表示的详细信息，参考 MySQL Internals: Important Algorithms and Structures - Date and Time Data Type Representation

DATETIME 类型非小数部分的编码如下：

1
2
3
4
5
6
7
8
 1 bit  sign           (1= non-negative, 0= negative)
17 bits year*13+month  (year 0-9999, month 0-12)
 5 bits day            (0-31)
 5 bits hour           (0-23)
 6 bits minute         (0-59)
 6 bits second         (0-59)
---------------------------
40 bits = 5 bytes

TIMESTAMP 类型的如下：

时间 时间戳 二进制字面量
1970-01-01T00:00:00Z 0 00000000 00000000 00000000 00000000
2038-01-19T03:14:07Z 2^31-1, 2147483647 01111111 11111111 11111111 11111111

默认值

  • 默认值必须是常量，而不能是一个函数或表达式。举个栗子，这意味着你不能将日期列的默认值设置为诸如 NOW() 或 CURRENT_DATE 之类的函数的值。唯一例外是你可以指定 CURRENT_TIMESTAMP 为 TIMESTAMP 和 DATETIME 列的默认值。
  • 隐式默认值定义如下：
    • 数字类型
      • 对于使用 AUTO_INCREMENT 属性声明的整数类型或浮点类型，默认值为下一个序列值。
      • 否则默认值为 0 。
    • 字符串类型
      • ENUM 的默认值为第一个枚举值。
      • BLOB 、TEXT 列无法指定默认值。
      • 其它类型的默认值为空字符串。
    • 日期与时间类型
      • TIMESTAMP
        • 如果系统变量 explicit_defaults_for_timestamp 开启，其默认值为 0 值。
        • 否则表中第一列 TIMESTAMP 的默认值为当前时间。
      • 其它类型的默认值为相应的 0 值。

参考

https://dev.mysql.com/doc/refman/5.7/en/data-types.html

https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

《MySQL数据类型：UNSIGNED注意事项》

《MySQL 5.6时间数据类型功能获得改进》