MySQL: Syntax: space requirements for data types

Note:

When different MySQL storage engines implement different data types, the storage methods, space requirements, and processing methods are not necessarily the same. Therefore, this issue must be placed on the specific storage engine implementation to make sense!

In addition, even if the same storage engine has different versions, the storage implementation may change!

The following discussion is limited to the MyISAM storage engine of MySQL.

 

Storage Requirements for numeric types inMyISAM

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 [precision],Real	8 bytes
Decimal (M,D),Numeric (M,D)	Varies; see following discussion. Approximate value: (number of valid numbers m/9) * 4 bytes
Bit (M)	Approximately (M+ 7)/8 bytes. It is about (capacity M + 7)/8 characters, usually 1 byte

ValuesDecimal(AndNumeric) 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.

Leftover digits	Number of bytes
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

 

 

Storage Requirements for Date and Time types inMyISAM

Data Type	Storage required
Date	3 bytes
Time	3 bytes
Datetime	8 bytes
Timestamp	4 bytes
Year	1 byte

The storage requirements shown in the table arise from the way that MySQL represents temporal values:

• Date: A three-byte integer packedDd+Mm× 32 +Yyyy× 16 × 32

• Time: A three-byte integer packedDd× 24 × 3600 +HH× 3600 +Mm× 60 +SS

• Datetime: Eight Bytes:

  • A four-byte integer packedYyyy× 10000 +Mm× 100 +Dd

  • A four-byte integer packedHH× 10000 +Mm× 100 +SS

• Timestamp: A Four-byte integer representing seconds UTC since the epoch ('2017-01-01 00:00:00'UTC)

• Year: A one-byte integer

 

 

Storage Requirements for string types inMyISAM

In the following table,MRepresents the declared Column Length in characters for nonbinary string types and bytes for binary string types.LRepresents the actual length in bytes of a given string value.

Data Type	Storage required
Char (M)	M×WBytes, 0<=M<=255, whereWIs the number of bytes required for the maximum-length character in the character set
Binary (M)	MBytes, 0<=M<=255
Varchar (M),Varbinary (M)	L+ 1 bytes, whereL<28,L+ 2 bytes, whereL<216
Tinyblob,Tinytext	L+ 1 bytes, whereL<28
Blob,Text	L+ 2 bytes, whereL<216
Mediumblob,Mediumtext	L+ 3 bytes, whereL<224
Longblob,Longtext	L+ 4 bytes, whereL<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 isL(The Byte Length of the string). For example, storage forMediumtextValue requiresLBytes to store the value plus three bytes to store the length of the value.

To calculate the number of bytes used to store a participantChar,Varchar, OrTextColumn value, you must take into account the character set used for that column and whether the value contains multi-byte characters. In particle, when usingUtf8Unicode Character Set, you must keep in mind that not allUtf8Characters use the same number of bytes and can require up to three bytes per character. For a breakdown of the storage used for different categoriesUtf8Characters, see section 9.1.10, "Unicode support ".

Varchar,Varbinary, AndBlobAndTextTypes 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,Varchar (255)Column can hold a string with a maximum length of 255 characters. Assuming that the column usesLatin1Character 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',LIs 4 and the storage requirement is five bytes. If the same column is instead declared to useUcs2Double-byte character set, the storage requirement is 10 bytes: the length'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 ).

Note

The specified tive maximum numberBytesThat can be stored inVarcharOrVarbinaryColumn is subject to the maximum row size of 65,535 bytes, which is shared among all columns. ForVarcharColumn that stores multi-byte characters, the specified tive maximum numberCharactersIs less. For example,Utf8Characters can require up to three bytes per character, soVarcharColumn that usesUtf8Character set can be declared to be a maximum of 21,844 characters.