DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) storage-requirements

Info Catalog (mysql.info) string-types (mysql.info) data-types (mysql.info) choosing-types
 
 11.5 Data Type Storage Requirements
 ===================================
 
 The storage requirements for each of the data types supported by MySQL
 are listed here 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.
 
 *Storage Requirements for Numeric Types*
 
 *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)',               Varies; see following discussion
 `NUMERIC(M,D)'                
 `BIT(M)'                      approximately (M+7)/8 bytes
 
 The storage requirements for `DECIMAL' (and `NUMERIC') are
 version-specific:
 
 As of MySQL 5.0.3, values for `DECIMAL' 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
 9                  4
 
 Before MySQL 5.0.3, `DECIMAL' columns are represented as strings and
 storage requirements are: M+2 bytes if D > 0, `M+1' bytes if D = 0 (D+2,
 if `M < D')
 
 *Storage Requirements for Date and Time Types*
 
 *Data Type*                   *Storage Required*
 `DATE'                        3 bytes
 `DATETIME'                    8 bytes
 `TIMESTAMP'                   4 bytes
 `TIME'                        3 bytes
 `YEAR'                        1 byte
 
 *Storage Requirements for String Types*
 
 *Data Type*                   *Storage Required*
 `CHAR(M)'                     `M' bytes, 0 `<= M <=' 255
 `VARCHAR(M)'                  _Prior to MySQL 5.0.3_: L + 1 bytes, where
                               `L <= M' and 0 `<= M <=' 255. _MySQL 5.0.3
                               and later_: L + 1 bytes, where `L <= M'
                               and 0 `<= M <=' 255 _or_ L + 2 bytes, where
                               `L <= M' and 256 `<= M <=' 65535 (see note
                               below).
 `BINARY(M)'                   `M' bytes, 0 `<= M <=' 255
 `VARBINARY(M)'                _Prior to MySQL 5.0.3_: L + 1 bytes, where
                               `L <= M' and 0 `<= M <=' 255. _MySQL 5.0.3
                               and later_: L + 1 bytes, where `L <= M'
                               and 0 `<= M <=' 255 _or_ L + 2 bytes, where
                               `L <= M' and 256 `<= M <=' 65535 (see note
                               below).
 `TINYBLOB', `TINYTEXT'        L+1 byte, 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)
 
 For the `CHAR', `VARCHAR', and `TEXT' types, the values L and M in the
 preceding table should be interpreted as number of characters, and
 lengths for these types in column specifications indicate the number of
 characters. For example, to store a `TINYTEXT' value requires L
 characters plus one byte.
 
 `VARCHAR', `VARBINARY', and the `BLOB' and `TEXT' types 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
 
 For example, a `VARCHAR(10)' column can hold a string with a maximum
 length of 10. Assuming that the column uses the `latin1' character 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'', L is 4 and the storage requirement is five bytes.
 If the same column was instead declared as `VARCHAR(500)', the string
 `'abcd'' requires 4 + 2 = 6 bytes. Two bytes rather than one are
 required for the prefix because the length of the column is greater
 than 255 characters.
 
 To calculate the number of _bytes_ used to store a particular `CHAR',
 `VARCHAR', or `TEXT' column value, you must take into account the
 character set used for that column. In particular, when using the `utf8'
 Unicode character set, you must keep in mind that not all `utf8'
 characters use the same number of bytes.  For a breakdown of the
 storage used for different categories of `utf8' characters, see 
 charset-unicode.
 
 * In MySQL 5.0.3 and later, the _effective_ maximum length for a
 `VARCHAR' or `VARBINARY' column is 65,532.
 
 As of MySQL 5.0.3, the `NDBCLUSTER' engine supports only fixed-width
 columns. This means that a `VARCHAR' column from a table in a MySQL
 Cluster will behave as follows:
 
    * If the size of the column is fewer than 256 characters, the column
      requires one byte extra storage per row.
 
    * If the size of the column is 256 characters or more, the column
      requires two bytes extra storage per row.
 
 Note that the number of bytes required per character varies according
 to the character set used. For example, if a `VARCHAR(100)' column in a
 Cluster table uses the `utf-8' character set, then each character
 requires 3 bytes storage. This means that each record in such a column
 takes up 100 × 3 + 1 = 301 bytes for storage, regardless of the length
 of the string actually stored in any given record. For a
 `VARCHAR(1000)' column in a table using the `NDBCLUSTER' storage engine
 with the `utf-8' character set, each record will use 1000 × 3 + 2 =
 3002 bytes storage; that is, the column is 1,000 characters wide, each
 character requires 3 bytes storage, and each record has a 2-byte
 overhead because 1,000 > 256.
 
 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  blob.
 
 `TEXT' and `BLOB' columns are implemented differently in the NDB
 Cluster storage engine, wherein each row in a `TEXT' column is made up
 of two separate parts. One of these is of fixed size (256 bytes), and is
 actually stored in the original table. The other consists of any data
 in excess of 256 bytes, which stored in a hidden table. The rows in
 this second table are always 2,000 bytes long. This means that the size
 of a `TEXT' column is 256 if SIZE <= 256 (where SIZE represents the
 size of the row); otherwise, the size is 256 + SIZE + (2000 - (SIZE -
 256) % 2000).
 
 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 having between 256
 and 65,535 possible values.  See  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  set.
 
Info Catalog (mysql.info) string-types (mysql.info) data-types (mysql.info) choosing-types
automatically generated byinfo2html