MySQL Reference Manual - 4.0.20

[ < ]

[ > ]

[ << ]

[ Up ]

[ >> ]

[Top]

[Contents]

[Index]

[ ? ]

12.5 Column Type Storage Requirements

The storage requirements for each of the column types supported by MySQL are listed by category.

The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and TEXT column accounts for only five to nine bytes toward this size.

If a MyISAM or ISAM table includes any variable-length column types, the record format will also be variable length. When a table is created, MySQL may, under certain conditions, change a column from a variable-length type to a fixed-length type or vice versa. See section Silent Column Specification Changes.

Storage Requirements for Numeric Types

Column 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]`, item `REAL`	8 bytes
`DECIMAL(M,D)`, `NUMERIC(M,D)`	`M+2` bytes if D > 0, `M+1` bytes if D = 0 (`D`+2, if `M < D`)

Storage Requirements for Date and Time Types

Column Type	Storage Required
`DATE`	3 bytes
`DATETIME`	8 bytes
`TIMESTAMP`	4 bytes
`TIME`	3 bytes
`YEAR`	1 byte

Storage Requirements for String Types

Column Type	Storage Required
`CHAR(M)`	`M` bytes, 0 `<= M <=` 255
`VARCHAR(M)`	`L`+1 bytes, where `L <= M` and 0 `<= M <=` 255
`TINYBLOB`, `TINYTEXT`	`L`+1 bytes, where `L` < 2^8
`BLOB`, `TEXT`	`L`+2 bytes, where `L` < 2^16
`MEDIUMBLOB`, `MEDIUMTEXT`	`L`+3 bytes, where `L` < 2^24
`LONGBLOB`, `LONGTEXT`	`L`+4 bytes, where `L` < 2^32
`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)

VARCHAR and the BLOB and TEXT types are variable-length types. For each, the storage requirements depend on the actual length of column values (represented by L in the preceding table), rather than on the type's maximum possible size. For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

The BLOB and TEXT types require 1, 2, 3, or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. See section BLOB.

The size of an ENUM object is determined by the number of different enumeration values. One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations with up to 65,535 values. See section ENUM.

The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum of 64 members. See section SET.