mysql - InnoDB 是如何存储字符列的？

Question

此问题仅解决“短”CHAR 和VARCHAR 列如何存储在 InnoDB 表中。

• CHAR(10) 列是否正好占用 10 个字节？
• 尾随空格会怎样？
• 对于每个字符需要超过 1 个字节的字符集怎么办？
• VARCHAR(10) 与 CHAR(10) 有何不同？
• EXPLAIN 表示所有索引的 varchar 都包含一个 2 字节长度的字段。真的是2个字节吗？或者它可能是 1 个字节？ (cf key_len).
• 不同的 ROW_FORMAT 怎么样？

此问题未涵盖(以免过于宽泛):

• TEXT 呢。
• 关于 255、191、页外存储等。
• 在以 char/varchar 开头的索引中会发生什么。 (思考:删除公共(public)前缀。)
• 当涉及到 MEMORY 临时表时，char/varchar 会发生什么。此外，8.0 版中发生了哪些变化。
• ROW_FORMAT 对较长的字符串列有重大影响，主要是决定何时使用页外存储。

Answer 1

来自 MySQL Documentation :

CHAR 和VARCHAR 值的区别在于它们的存储方式，CHAR (10) 无论如何都需要 10 个字节的存储空间您使用的许多字符，因为数据右填充了空格，VARCHAR (10) 只占用 1 个字节(在 1 字节字符集中)+ 长度前缀(当长度为 255 或更小时为 1，2否则...我不知道为什么 EXPLAIN 的 key_len 添加 2 个字节)

我不明白你所说的尾随空格是什么意思，虽然我可以想象你指的是多余的尾随空格，使用 VARCHAR 这些被截断并带有警告，同时在 CHAR 列，这些空格被静默截断，这有一定道理，因为 CHAR 存储时尾随空格。

关于此 link 中的字符集您可以看到 CHAR 或 VARCHAR 的字符数是相同的，尽管您的存储需要每个字符 1 到 4 个字节，here是支持的字符集列表，here每个字符的字节数。

我读到的 InnoDB 的不同行格式

冗余行格式特征:

Internally, InnoDB stores fixed-length character columns such as CHAR(10) in a fixed-length format. InnoDB does not truncate trailing spaces from VARCHAR columns.

InnoDB encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

COMPACT 行格式特征:

• Internally, for nonvariable-length character sets, InnoDB stores fixed-length character columns such as CHAR(10) in a fixed-length format.

  InnoDB does not truncate trailing spaces from VARCHAR columns.

• Internally, for variable-length character sets such as utf8mb3 and utf8mb4, InnoDB attempts to store CHAR(N) in N bytes by trimming trailing spaces. If the byte length of a CHAR(N) column value exceeds N bytes, InnoDB trims trailing spaces to a minimum of the column value byte length. The maximum length of a CHAR(N) column is the maximum character byte length × N.

  InnoDB reserves a minimum of N bytes for CHAR(N). Reserving the minimum space N in many cases enables column updates to be done in place without causing fragmentation of the index page. By comparison, for ROW_FORMAT=REDUNDANT, CHAR(N) columns occupy the maximum character byte length × N.

  InnoDB encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

  ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED handle CHAR storage in the same way as ROW_FORMAT=COMPACT.

...

DYNAMIC and COMPRESSED row formats are variations of the COMPACT row format and therefore handle CHAR storage in the same way as the COMPACT row format