MySQL data type storage requirements and how to choose the correct type

1. Column type storage requirements

The storage requirements of each column type supported by MySQL are listed according to category.

The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and TEXT column accounts for only 5 to 9 bytes of it.

If the MyISAM table includes variable length column types, the record format is also variable length. When creating a table, MySQL can change a column from a variable-length type to a fixed-length type or vice versa, under certain conditions.

Numeric type storage requirements

Column type Storage requirements

TINYINT 1 byte

SMALLINT 2 bytes

MEDIUMINT 3 bytes

INT, INTEGER 4 bytes

BIGINT 8 bytes

FLOAT(p) If 0

FLOAT 4 bytes

DOUBLE [PRECISION], item REAL 8 bytes

DECIMAL(M,D), NUMERIC(M,D) variable length; see discussion below

BIT(M) approximately (M+7)/8 bytes

DECIMAL(and NUMERIC) storage requirements are version-specific:

Uses binary format to compress 9 decimal (10-based) numbers into 4 bytes to represent DECIMAL column values. The storage of the integer and fractional parts of each value is determined separately. Each multiple of 9 digits requires 4 bytes, and the "remaining" bits require a portion of the 4 bytes. The following table gives the storage requirements for excess bits:

Remaining Bytes

Number of Bits

0 0

1 1 1

2 1

3 2

4 2

5 3

6 3

7 4

8 4

9 4

Storage requirements for date and time types

Column type Storage requirements

DATE 3 bytes

DATE TIME 8 bytes

TIMESTAMP 4 bytes

TIME 3 bytes

YEAR 1 byte

Storage requirements for string type

Column type Storage requirements

CHAR(M) M bytes, 0

VARCHAR (M) L+1 bytes, where L

BINARY(M) M bytes, 0

VARBINARY(M) L+1 bytes, where L

TINYBLOB, TINYTEXT L+1 bytes, where L

BLOB, TEXT L+2 bytes, where L

MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L

LONGBLOB, LONGTEXT L+4 bytes, where L

ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumeration values ​​(up to 65,535 values)

SET('value1','value2',.. .) 1, 2, 3, 4 or 8 bytes, depending on the number of set members (up to 64 members)

VARCHAR, BLOB and TEXT classes are variable-length types. The storage requirements of each type depend on the actual length of the column value (denoted by L in the previous table), not the maximum possible size of the type. For example, a VARCHAR(10) column can hold a string of maximum length 10. The actual storage requirement is the length of the string (L), plus a byte recording the length of the string. For the string 'abcd', L is 4 and storage requires 5 bytes.

For CHAR, VARCHAR and TEXT types, the values ​​L and M in the previous table should be interpreted as the number of characters, and the length of these types in the column definition represents the number of characters. For example, to store a TINYTEXT value requires L characters + 1 byte.

To calculate the number of bytes used to store the value of a specific CHAR, VARCHAR or TEXT column, you need to consider the character set used by the column. In the specific case, when working with Unicode, you must remember that all Unicode characters use the same number of bytes.

Note: The effective maximum length of a VARCHAR column is 65,532 characters.

NDBCLUSTER engine only supports fixed-width columns. This means that VARCHAR columns in tables in MySQL Cluster behave like type CHAR (except that each record still has an extra byte of space). For example, in a Cluster table, each record in a column declared as VARCHAR(100) will occupy 101 bytes when stored, regardless of the length of the string in the actual stored record.

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

In the NDB Cluster storage engine, the implementation of TEXT and BLOB columns is different, where each record in the TEXT column consists of two separate parts. One is fixed size (256 bytes) and is actually saved in the original table. The other includes any data beyond 256 bytes, held in an implicit table. Records in the second table are always 2,000 bytes long. This means that if size

The size of an ENUM object is determined by the number of different enumeration values. The enumeration uses one byte and can have 255 possible values. When the enumeration value is between 256 and 65,535, two bytes are used.

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 to 1, 2, 3, 4, or 8 bytes. A SET can have up to 64 members.

2. Choose the right data type

To optimize storage, the most precise type should be used in any case. For example, if the column values ​​range from 1 to 99999, MEDIUMINT UNSIGNED is a good type if you use integers. This type uses the least storage of all the types that can represent the column value.

Perform all basic calculations (+, -, *, /) on the DECIMAL column with a precision of 65 decimal digits (based on 10).

Use double precision operations to perform calculations on DECIMAL values. If accuracy is not too important or if speed is the highest priority, the DOUBLE type is sufficient. To achieve high precision, conversion to fixed-point types stored in BIGINT can be performed. This allows all calculations to be done with 64-bit integers, converting the results back to floating point values ​​as needed.

3. Use column types from other database engines

In order to use SQL execution code written by other vendors, MySQL maps column types as shown in the following table. Table definitions can be easily imported into MySQL from other database engines through these mappings:

Other seller types MySQL types

BOOL, TINYINT

BOOLEAN TINYINT

CHAR VARYING(M) VARCHAR(M)

DEC DECIMAL

FIXED DECIMAL

FLOAT4 FLOAT

FLOAT8 DOUBLE

INT1 TINYINT

INT2 SMALLINT

INT3 MEDIUMINT

INT4 INT

INT8 BIGINT

LONG VARBINARY MEDIUMBLOB

LONG VARCHAR MEDIUMTEXT

LONG MEDIUMTEXT

MIDDLEINT MEDIUMINT

NUMERIC

DECIMAL

The column type is mapped when the table is created, and then the original type definition is discarded. If you create a table using another vendor's type and then execute the DESCRIBE tbl_name statement, MySQL uses the equivalent MySQL type to report the structure of the table. For example:

mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG, d NUMERIC);Query OK, 0 rows affected (0.08 sec);
mysql> DESCRIBE t;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | tinyint(1)    | YES  |     | NULL    |       |
| b     | double        | YES  |     | NULL    |       |
| c     | mediumtext    | YES  |     | NULL    |       |
| d     | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)