I have a column, its datatype is LONGTEXT. While TEXT datatype is enough for 95% of the values. I need LONGTEXT just for 5% of values.
我有一个列，它的数据类型是LONGTEXT。而TEXT数据类型足以满足95％的值。我只需5％的价值就需要LONGTEXT。
Now I want to know, storing a small value in the LONGTEXT will waste a lot of space? Is that optimize?
现在我想知道，在LONGTEXT中存储一个小值会浪费很多空间吗？这是优化吗？
1 个解决方案
#1
2
Lets look at MySQL documentation.
让我们看看MySQL文档。
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
TEXT [（M）] [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 65,535 (2^16 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.
一个TEXT列，最大长度为65,535（2 ^ 16 - 1）个字符。如果值包含多字节字符，则有效最大长度会减少。每个TEXT值使用2字节长度前缀存储，该前缀指示值中的字节数。
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.
TEXT列，最大长度为4,294,967,295或4GB（2 ^ 32 - 1）个字符。如果值包含多字节字符，则有效最大长度会减少。 LONGTEXT列的有效最大长度还取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个LONGTEXT值使用4字节长度前缀存储，该前缀指示值中的字节数。
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 16,777,215 (2^24 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
一个TEXT列，最大长度为16,777,215（2 ^ 24 - 1）个字符。如果值包含多字节字符，则有效最大长度会减少。每个MEDIUMTEXT值使用3字节长度前缀存储，该前缀指示值中的字节数。
So the difference is 2 bytes for prefix.
所以前缀的区别是2个字节。
#1
2
Lets look at MySQL documentation.
让我们看看MySQL文档。
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
TEXT [（M）] [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 65,535 (2^16 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.
一个TEXT列，最大长度为65,535（2 ^ 16 - 1）个字符。如果值包含多字节字符，则有效最大长度会减少。每个TEXT值使用2字节长度前缀存储，该前缀指示值中的字节数。
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.
TEXT列，最大长度为4,294,967,295或4GB（2 ^ 32 - 1）个字符。如果值包含多字节字符，则有效最大长度会减少。 LONGTEXT列的有效最大长度还取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个LONGTEXT值使用4字节长度前缀存储，该前缀指示值中的字节数。
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 16,777,215 (2^24 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
一个TEXT列，最大长度为16,777,215（2 ^ 24 - 1）个字符。如果值包含多字节字符，则有效最大长度会减少。每个MEDIUMTEXT值使用3字节长度前缀存储，该前缀指示值中的字节数。
So the difference is 2 bytes for prefix.
所以前缀的区别是2个字节。