原则

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' 分别代表 7128 。详见《Bit-Value Literals》。

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

BIT(1) 常用来表示布尔类型,b'0' 表示 falseb'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 同义词 BOOLBOOLEAN ,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 可以存储的范围是 0~255,而 tinyint 的存储范围是 -128~127。

  • 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 同义词 DECNUMERICFIXED

例如 :

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 是小数点后面的位数。如果 MD 省略,值将存储到硬件允许的限制。单精度浮点数精确到约 7 位小数。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 双精度浮点类型(floating-point number),M 是总位数,D 是小数点后面的位数。如果 MD 省略,值将存储到硬件允许的限制。双精度浮点数精确到小数点后 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) 列说明了 CHARVARCHAR 之间的差别(假设该列使用单字节字符集,例如 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 把每个 BLOBTEXT 值当做一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当 BLOBTEXT 值太大时,InnoDB 会使用专门的“外部”存储区域来进行存储,此时每个值在行内需要 1~4 个字节存储一个指针,然后在外部存储区域存储实际的值。它们的最大字节长度如下:

类型 指针长度 实际值的最大字节长度
TINYTEXTTINYBLOB 1 byte 256 bytes (2^8)
TEXTBLOB 2 bytes 65,536 bytes (2^16),64 KB
MEDIUMTEXTMEDIUMBLOB 3 bytes 16,777,216 bytes (2^24),16 MB
LONGTEXTLONGBLOB 4 bytes 4,294,967,296 bytes (2^32),4 GB

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

日期与时间类型

  • 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 0000-00-00 00:00:00 1970-01-01 00:00:01 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

有关于时间值的内部表示的详细信息,参考 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

默认值

  • 默认值必须是常量,而不能是一个函数或表达式。举个栗子,这意味着你不能将日期列的默认值设置为诸如 NOW()CURRENT_DATE 之类的函数的值。唯一例外是你可以指定 CURRENT_TIMESTAMPTIMESTAMPDATETIME 列的默认值。
  • 隐式默认值定义如下:
    • 数字类型
      • 对于使用 AUTO_INCREMENT 属性声明的整数类型或浮点类型,默认值为下一个序列值。
      • 否则默认值为 0
    • 字符串类型
      • ENUM 的默认值为第一个枚举值。
      • BLOBTEXT 列无法指定默认值。
      • 其它类型的默认值为空字符串。
    • 日期与时间类型
      • 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时间数据类型功能获得改进