MySQL 类型文本的最大长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6766781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Maximum length for MySQL type text
提问by CyberJunkie
I'm creating a form for sending private messages and want to set the maxlengthvalue of a textarea appropriate to the max length of a textfield in my MySQL database table. How many characters can a type text field store?
我正在创建一个用于发送私人消息的表单，并希望将maxlengthtextarea的值设置为适合text我的 MySQL 数据库表中字段的最大长度。类型文本字段可以存储多少个字符？
If a lot, would I be able to specify length in the database text type field as I would with varchar?
如果很多，我是否可以像使用 varchar 一样在数据库文本类型字段中指定长度？
回答by fyr
See for maximum numbers: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
请参阅最大数量：http: //dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
TINYBLOB, TINYTEXT L + 1 bytes, where L < 2^8 (255 Bytes)
BLOB, TEXT L + 2 bytes, where L < 2^16 (64 Kilobytes)
MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 2^24 (16 Megabytes)
LONGBLOB, LONGTEXT L + 4 bytes, where L < 2^32 (4 Gigabytes)
L is the number of bytes in your text field. So the maximum number of chars for text is 216-1 (using single-byte characters). Means 65 535 chars(using single-byte characters).
L 是文本字段中的字节数。所以文本的最大字符数是 2 16-1（使用单字节字符）。表示 65 535 个字符（使用单字节字符）。
UTF-8/MultiByte encoding: using MultiByte encoding each character might consume more than 1 byte of space. For UTF-8 space consumption is between 1 to 4 bytes per char.
UTF-8/MultiByte 编码：使用 MultiByte 编码每个字符可能会占用超过 1 个字节的空间。对于 UTF-8，每个字符的空间消耗在 1 到 4 个字节之间。
回答by Cristian Oana
TINYTEXT: 256 bytes
TEXT: 65,535 bytes
MEDIUMTEXT: 16,777,215 bytes
LONGTEXT: 4,294,967,295 bytes
TINYTEXT：256 字节
TEXT：65,535 字节
MEDIUMTEXT：16,777,215 字节
LONGTEXT：4,294,967,295 字节
回答by evilReiko
Type | Approx. Length | Exact Max. Length Allowed
-----------------------------------------------------------
TINYTEXT | 256 Bytes | 255 characters
TEXT | 64 Kilobytes | 65,535 characters
MEDIUMTEXT | 16 Megabytes | 16,777,215 characters
LONGTEXT | 4 Gigabytes | 4,294,967,295 characters
Note:If using multibyte characters (like Arabic, where each Arabic character takes 2 bytes), the column "Exact Max. Length Allowed" for TINYTEXTcan hold be up to 127 Arabic characters (Note: space, dash, underscore, and other such characters, are 1-byte characters).
注意：如果使用多字节字符（如阿拉伯语，每个阿拉伯字符占 2 个字节），“Exact Max. Length Allowed”列最多TINYTEXT可容纳 127 个阿拉伯字符（注意：空格、破折号、下划线和其他此类字符, 是 1 个字节的字符）。
Basically, it's like:
基本上，它是这样的：
"Exact Max. Length Allowed" = "Approx. Length" in bytes - 1
"Exact Max. Length Allowed" = "Approx. Length" in bytes - 1
回答by Blindy
Acording to http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html, the limit is L + 2 bytes, where L < 2^16, or 64k.
根据http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html，限制为L + 2 bytes, where L < 2^16，或 64k。
You shouldn't need to concern yourself with limiting it, it's automatically broken down into chunks that get added as the string grows, so it won't always blindly use 64k.
您不需要担心限制它，它会自动分解为随着字符串增长而添加的块，因此它不会总是盲目地使用 64k。
回答by SCC
How many characters can a type text field store?
类型文本字段可以存储多少个字符？
According to DocumentationYou can use maximum of 21,844 characters if the charset is UTF8
根据文档，如果字符集是 UTF8，您最多可以使用 21,844 个字符
If a lot, would I be able to specify length in the db text type field as I would with varchar?
如果很多，我是否可以像使用 varchar 一样在 db 文本类型字段中指定长度？
You dont need to specify the length. If you need more character use data types MEDIUMTEXT or LONGTEXT. With VARCHAR, specifieng length is not for Storage requirement, it is only for how the data is retrieved from data base.
您不需要指定长度。如果您需要更多字符，请使用数据类型 MEDIUMTEXT 或 LONGTEXT。对于 VARCHAR，指定长度不是针对存储要求，而是针对如何从数据库中检索数据。
回答by Sachith
TINYTEXT 256 bytes TEXT 65,535 bytes ~64kb MEDIUMTEXT 16,777,215 bytes ~16MB LONGTEXT 4,294,967,295 bytes ~4GB
TINYTEXT 256 bytes TEXT 65,535 bytes ~64kb MEDIUMTEXT 16,777,215 bytes ~16MB LONGTEXT 4,294,967,295 bytes ~4GB
TINYTEXTis a string data type that can store up to to 255characters.
TINYTEXT是一种字符串数据类型，最多可以存储255字符。
TEXTis a string data type that can store up to 65,535characters. TEXTis commonly used for brief articles.
TEXT是一种字符串数据类型，最多可以存储65,535字符。TEXT常用于简短的文章。
LONGTEXTis a string data type with a maximum length of 4,294,967,295characters. Use LONGTEXTif you need to store large text, such as a chapter of a novel.
LONGTEXT是具有最大4,294,967,295字符长度的字符串数据类型。使用LONGTEXT，如果你需要存储大量的文本，如小说的一章。
回答by sohel shaikh
TEXTis a string data type that can store up to 65,535 characters.
But still if you want to store more data then change its data type to LONGTEXT
TEXT是一种字符串数据类型，最多可存储 65,535 个字符。但是如果你想存储更多的数据，那么将它的数据类型更改为LONGTEXT
ALTER TABLE name_tabelCHANGE text_fieldLONGTEXT CHARACTER SET utf8COLLATE utf8_general_ciNOT NULL;
ALTER TABLE name_tabelCHANGE text_fieldLONGTEXT CHARACTER SET utf8COLLATE utf8_general_ciNOT NULL;
回答by Rohit.007
For the MySql version 8.0.
对于 MySql 版本 8.0。
Numeric Type Storage Requirements
数字类型存储要求
Data Type Storage Required
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT, INTEGER 4 bytes
BIGINT 8 bytes
FLOAT(p) 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT 4 bytes
DOUBLE, REAL 8 bytes
DECIMAL(M,D), NUMERIC(M,D) Varies; see following discussion
BIT(M) approximately (M+7)/8 bytes
Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table.
DECIMAL（和 NUMERIC）列的值使用二进制格式表示，该格式将九个十进制（基数为 10）数字打包成四个字节。每个值的整数部分和小数部分的存储是单独确定的。九位数字的每个倍数需要四个字节，“剩余”数字需要四个字节的一小部分。下表给出了多余数字所需的存储空间。
Date and Time Type Storage Requirements For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from tables created from 5.6.4 on. This is due to a change in 5.6.4 that permits these types to have a fractional part, which requires from 0 to 3 bytes.
日期和时间类型存储要求 对于 TIME、DATETIME 和 TIMESTAMP 列，MySQL 5.6.4 之前创建的表所需的存储与 5.6.4 之后创建的表不同。这是由于 5.6.4 中的更改允许这些类型具有小数部分，这需要 0 到 3 个字节。
Data Type Storage Required Before MySQL 5.6.4 Storage Required as of MySQL 5.6.4
YEAR 1 byte 1 byte
DATE 3 bytes 3 bytes
TIME 3 bytes 3 bytes + fractional seconds storage
DATETIME 8 bytes 5 bytes + fractional seconds storage
TIMESTAMP 4 bytes 4 bytes + fractional seconds storage
As of MySQL 5.6.4, storage for YEAR and DATE remains unchanged. However, TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.
从 MySQL 5.6.4 开始，YEAR 和 DATE 的存储保持不变。但是，TIME、DATETIME 和 TIMESTAMP 的表示方式不同。DATETIME 的打包效率更高，非小数部分需要 5 个而不是 8 个字节，并且所有三个部分都有一个需要 0 到 3 个字节的小数部分，具体取决于存储值的小数秒精度。
Fractional Seconds Precision Storage Required
0 0 bytes
1, 2 1 byte
3, 4 2 bytes
5, 6 3 bytes
For example, TIME(0), TIME(2), TIME(4), and TIME(6) use 3, 4, 5, and 6 bytes, respectively. TIME and TIME(0) are equivalent and require the same storage.
例如，TIME(0)、TIME(2)、TIME(4) 和 TIME(6) 分别使用 3、4、5 和 6 个字节。TIME 和 TIME(0) 是等价的，需要相同的存储。
For details about internal representation of temporal values, see MySQL Internals: Important Algorithms and Structures.
有关时间值的内部表示的详细信息，请参阅 MySQL 内部：重要算法和结构。
String Type Storage Requirements 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.
字符串类型存储要求 在下表中，M 表示非二进制字符串类型的声明列长度和二进制字符串类型的字节数。L 表示给定字符串值的实际长度（以字节为单位）。
Data Type Storage Required
CHAR(M) The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Characteristics. Otherwise, M × w bytes, <= 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)
