mysql的存储格式_MySQL · 存储引擎 · MySQL的字段数据存储格式

概述

MySQL支持多种存储引擎，而InnoDB是MySQL事务型数据库的首选引擎，也是MySQL从5.6版本以来的默认存储引擎。

InnoDB的存储格式已经有太多介绍性的文章，讲述了Tablespaces, Segments, Exents, Pages, Records等概念。其中很少有人对行存Record的不同数据字段进行介绍。本文讨论分析一下常见的字段数据在MySQL和InnoDB种不同的存储格式，并给出方法，大家可以自行学习其他没有涉及的字段。这里讨论的MySQL和InnoDB都是MySQL 5.7或MySQL 8.0, 过早的版本不在本文讨论范围。

字段数据格式学习方法

因为MySQL可以对接不同独立的存储引擎，MySQL和其对应的存储引擎对数据的存储方式就可能不同。因此MySQL必然会在计算层和存储层有不同的存储格式，也会有对应的数据转化方法。

对于InnoDB而言，有两个方法对于数据格式的转化最为关键。

MySQL数据 -> InnoDB数据  (row0mysql.cc)

/** Stores a non-SQL-NULL field given in the MySQL format in the InnoDB format. */

row_mysql_store_col_in_innobase_format()

InnoDB数据 -> MySQL数据  (row0sel.cc)

/** Convert a field from Innobase format to MySQL format. */

row_sel_store_mysql_field

大家可以使用GDB设置断点在以上两个函数，就可以清楚的认识到不同字段数据的存储格式了。

常见字段数据格式

Numeric Data Types

INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

Table:  Required Storage and Range for Integer Types Supported by MySQL

Type

Storage (Bytes)

Minimum Value Signed

Minimum Value Unsigned

Maximum Value Signed

Maximum Value Unsigned

TINYINT

1

-128

0

127

255

SMALLINT

2

-32768

0

32767

65535

MEDIUMINT

3

-8388608

0

8388607

16777215

INT

4

-2147483648

0

2147483647

4294967295

BIGINT

8

-2

0

2-1

2-1

MySQL使用little-endian格式存储integer数据，InnoDB使用big-endian格式，并且符号为是取反处理。InnoDB这样设计存储Integer的好处是在数据比较的时候，可以直接一个一个byte去比较 - memcmp。

举例：BIGINT value 1000

InnoDB format

1000 stored as bigint (8 bytes) in Hex as:

0x800x00 0x00 0x00 0x000x00 0x03 0xe8

-1000 stored as bigint (8 bytes) in Hex as:

0x7f 0xff 0xff 0xff 0xff 0xff 0xfc 0x18

MySQL format:

1000 stored in Hex as:

0xe8    0x03    0x00    0x00    0x00    0x00    0x00    0x00

DECIMAL

这里MySQL和InnoDB存储格式一致，不需要做特别转换。Decimal需要声明precision和scale，例如decimal(30,15)。精度表示值存储的有效位数，小数位数表示小数点后可以存储的位数。

举例：decimal(30,15) value

1000.01 stored (14 bytes) in Hex as:

0x80    0x00    0x00    0x00    0x00    0x03    0xe8    0x00

0x98    0x96    0x80    0x00    0x00    0x00

FLOAT, DOUBLE

FLOAT/DOUBLE类型表示近似数字数据值。这里MySQL和InnoDB存储格式一致，不需要做特别转换。MySQL将四个字节用于单精度值，并将八个字节用于双精度值。单精度FLOAT列存储精度范围是从0到23, 双精度FLOAT列存储精度范围是从24到53。

举例：double

1000.01 stored (8 bytes) in Hex as:

0xae    0x47    0xe1    0x7a    0x14    0x40    0x8f    0x40

Date and Time Data Types

时间相关的存储格式：

Type

Storage as of MySQL 5.6.4

YEAR

1 byte, little endian

DATE

3 bytes, little endian

TIME

3 bytes + fractional-seconds storage, big endian

TIMESTAMP

4 bytes + fractional-seconds storage, big endian

DATETIME

5 bytes + fractional-seconds storage, big endian

TIME encoding for non-fractional part:

1 bit sign (1= non-negative, 0= negative)

1 bit unused (reserved for future extensions)

10 bits hour (0-838)

6 bits minute (0-59)

6 bits second (0-59)

---------------------

24 bits = 3 bytes

DATETIME encoding for non-fractional part:

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

举例：

Datetime value: ‘1970-1-1 00:00:00’

innodb data: 0x99    0x02    0xc2    0x00    0x00

Datetime value: ‘2019-12-19 03:14:07’

innodb data: 0x99    0xa4    0xe6    0x33    0x87

查看落盘数据格式

还有其他常用字段大家可以通过前文的方法，自行学习下。在了解了不同字段的存储格式后，我们也可以从InnoDB落盘数据上得到验证。

hexdump -C -v table.ibd > table.txt

找到对应table的数据文件，用hexdump把table数据以hex方式打印到一个文本文件内，然后就可以用编辑器打开浏览。这里可以结合上文中得到的不同字段hex的表示，在文本文件中搜寻。