InnoDB如何存储字符列? [英] How does InnoDB store character columns?

问题描述

此问题仅解决InnoDB表中如何存储'short'CHAR和VARCHAR列.

This Question addresses only how 'short' CHAR and VARCHAR columns are stored in an InnoDB table.

• CHAR(10)列是否正好占据10个字节?
• 尾随空白会发生什么?
• 每个字符需要超过1个字节的字符集怎么办?
• VARCHAR(10)与CHAR(10)有何不同?
• EXPLAIN表示所有索引的varchar都包含一个2字节长的字段.真的是2个字节吗?或者可能是1个字节? (请参见key_len).
• 那ROW_FORMATs又如何呢?

• Does a CHAR(10) column occupy exactly 10 bytes?
• What happens with trailing blanks?
• What about character sets that need more than 1 byte per character?
• How does VARCHAR(10) differ from CHAR(10)?
• EXPLAIN implies that all indexed varchars contain a 2-byte length field. Is it really 2 bytes? Or might it be 1 byte? (cf key_len).
• What about different ROW_FORMATs?

此问题未涵盖(以免过于笼统):

Not covered in this Question (to keep it from being too broad):

• TEXT怎么样?
• 大约255、191，页外存储等
• 以char/varchar开头的索引中发生了什么. (认为​​:删除通用前缀.)
• 在MEMORY临时表中涉及char/varchar时会发生什么.另外，在8.0版中会发生什么变化.
• ROW_FORMAT对较长的字符串列具有重大影响，主要在于决定何时使用页外存储.

• What about TEXT.
• What about 255, 191, off-page storage, etc.
• What happens in an index starting with a char/varchar. (Think: removal of common prefix.)
• What happens with char/varchar when involved in a MEMORY temp table. Also, what changes happen in version 8.0.
• ROW_FORMAT has a significant impact on longer string columns, primarily in deciding when off-page storage is used.

推荐答案

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

The difference between CHAR and VARCHAR values is the way they are stored, CHAR (10) requires 10 bytes of storage no matter how many characters you use because the data is right-padded with spaces, VARCHAR (10) only takes 1 byte (in 1 byte character set) + length prefix (1 when the length is 255 or less, 2 otherwise... I don't know why key_len for EXPLAIN add 2 bytes)

我不明白您对尾随空格的意思，尽管我可以想象您指的是多余的尾随空格，对于VARCHAR，这些空格会被警告截断，而在CHAR列中，这些空格会被截断默默地讲，这在某种意义上是因为CHAR末尾带有空格.

I don't understand what you mean with trailing blanks, although I can imagine you are referring to the excess of trailing spaces, with VARCHAR these are truncated with a warning, meanwhile in CHAR columns these spaces are truncated silently, this has some sense cause CHAR are stored with trailing blanks at the end.

关于此链接中的字符集，您可以看到CHAR或VARCHAR的字符数是相同的，尽管您的存储空间需要每个字符1到4个字节，

Regarding character set in this link you can see that the number of characters for the CHAR or VARCHAR is the same, although, your storage will require from 1 to 4 bytes per character, here is the list of supported character set and here the bytes per character.

我所阅读的 InnoDB

冗余行格式特征:

在内部，InnoDB以固定长度格式存储固定长度的字符列，例如CHAR(10). InnoDB不会截断VARCHAR列中的尾随空格.

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将长度大于或等于768字节的固定长度字段编码为可变长度字段，可以将其存储在页面外.例如，如果字符集的最大字节长度大于3(如utf8mb4一样)，则CHAR(255)列可以超过768个字节.

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行格式特征:

• 在内部，对于非可变长度字符集，InnoDB存储 固定长度的字符列，例如固定长度的CHAR(10) 格式.

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

InnoDB不会截断VARCHAR中的尾随空格 列.

InnoDB does not truncate trailing spaces from VARCHAR columns.

内部，对于可变长度字符集，例如utf8mb3和 utf8mb4，InnoDB尝试通过修整以N字节存储CHAR(N) 尾随空格.如果CHAR(N)列值的字节长度超过 N个字节，InnoDB将尾随空格修剪到该列的最小值 值字节长度. CHAR(N)列的最大长度是 最大字符字节长度×N.

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为CHAR(N)保留最少N个字节.保留 在许多情况下，最小空间N可使列更新在 放置而不会导致索引页的碎片化.通过对比， 对于ROW_FORMAT = REDUNDANT，CHAR(N)列占据最大值 字符字节长×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编码大于或等于768个字节的固定长度字段 长度为可变长度字段，可以在页面外存储. 例如，如果最大长度，则CHAR(255)列可以超过768个字节 字符集的字节长度大于3，与 utf8mb4.

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 =动态并且ROW_FORMAT = COMPRESSED处理CHAR存储在 与ROW_FORMAT = COMPACT相同.

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

...

动态和压缩行格式是COMPACT行格式的变体，因此以与COMPACT行格式相同的方式处理CHAR存储

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

这篇关于InnoDB如何存储字符列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！