mysql - MySQL 中数据类型的动态大小

Question

我想在MySQL表的某列中保存一个字符串。有时它是 2 个字符，有时甚至更大。 50000 个字符。使用 varchar(50000) 是个好主意吗？如果我只在列中保存 2 个字符，它会使用全部 4998 个字节还是仅使用 2 个字节？

Answer 1

如 Data Type Storage Requirements 下所述:

Storage Requirements for String Types

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

╔═════════════════════════════╦═════════════════════════════════════════════════╗║          Data Type          ║                Storage Required                 ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ CHAR(M)                     ║ M × w bytes, 0 <= M <= 255, where w is the      ║║                             ║ number of bytes required for the maximum-length ║║                             ║ character in the character set                  ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ BINARY(M)                   ║ M bytes, 0 <= M <= 255                          ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ VARCHAR(M), VARBINARY(M)    ║ L + 1 bytes if column values require 0 – 255    ║║                             ║ bytes, L + 2 bytes if values may require more   ║║                             ║ than 255 bytes                                  ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ TINYBLOB, TINYTEXT          ║ L + 1 bytes, where L < 28                         ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ BLOB, TEXT                  ║ L + 2 bytes, where L < 216                        ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ MEDIUMBLOB, MEDIUMTEXT      ║ L + 3 bytes, where L < 224                        ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ LONGBLOB, LONGTEXT          ║ L + 4 bytes, where L < 232                        ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ ENUM('value1','value2',...) ║ 1 or 2 bytes, depending on the number of        ║║                             ║ enumeration values (65,535 values maximum)      ║╠═════════════════════════════╬═════════════════════════════════════════════════╣║ SET('value1','value2',...)  ║ 1, 2, 3, 4 or 8 bytes, depending on the number  ║║                             ║ of set members (64 members maximum)             ║╚═════════════════════════════╩═════════════════════════════════════════════════╝

Variable-length string types are stored using a length prefix plus data. The length prefix requires from one to four bytes depending on the data type, and the value of the prefix is L (the byte length of the string). For example, storage for a MEDIUMTEXT value requires L bytes to store the value plus three bytes to store the length of the value.

To calculate the number of bytes used to store a particular CHAR, VARCHAR, or TEXT column value, you must take into account the character set used for that column and whether the value contains multi-byte characters. In particular, when using the utf8 (or utf8mb4) Unicode character set, you must keep in mind that not all characters use the same number of bytes and can require up to three (four) bytes per character. For a breakdown of the storage used for different categories of utf8 or utf8mb4 characters, see Section 10.1.10, “Unicode Support”.

VARCHAR, VARBINARY, and the BLOB and TEXT types are variable-length types. For each, the storage requirements depend on these factors:

• The actual length of the column value

• The column's maximum possible length

• The character set used for the column, because some character sets contain multi-byte characters

For example, a VARCHAR(255) column can hold a string with a maximum length of 255 characters. Assuming that the column uses the latin1 character set (one byte per character), the actual storage required is the length of the string (L), plus one byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes).

因此，回答您的问题:

If I only save 2 characters in the column, does it use all 4998 bytes or just 2 bytes?

存储 2 个字符字符串的 VARCHAR(50000) 列需要 L+2 个字节，其中L是在列的字符集中编码该2个字符的字符串所需的字节数:它肯定不会 使用“全部 4998 字节”。