GNU Info

(mysql.info)Storage requirements

---

Prev: Other-vendor column types Up: Column types

---

Enter node , (file) or (file)node

Column Type Storage Requirements
--------------------------------

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

Storage requirements for numeric types
......................................

*Column type*                        *Storage required*
`TINYINT'                            1 byte
`SMALLINT'                           2 bytes
`MEDIUMINT'                          3 bytes
`INT'                                4 bytes
`INTEGER'                            4 bytes
`BIGINT'                             8 bytes
`FLOAT(X)'                           4 if X <= 24 or 8 if 25 <= X <= 53
`FLOAT'                              4 bytes
`DOUBLE'                             8 bytes
`DOUBLE PRECISION'                   8 bytes
`REAL'                               8 bytes
`DECIMAL(M,D)'                       `M+2' bytes if D > 0, `M+1' bytes
                                     if D = 0 (`D'+2, if `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, `1 <= M <= 255'
`VARCHAR(M)'                         `L'+1 bytes, where `L <= M' and `1
                                     <= 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 (65535
                                     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 which 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.  Note: BLOB.

If a table includes any variable-length column types, the record format
will also be variable-length.  Note that 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.  Note: Silent column
changes.

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 65535
values. Note: 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. Note: SET.