MySQL Basic Tutorial 4 - Storage requirements of data types and how to choose the correct type

1. Column type storage requirements

The storage requirements for 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 <= p <= 24 is 4 bytes, if 25 <= p <= 53 is 8 bytes
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

Storage requirements for DECIMAL (and NUMERIC) are version-specific:

Compresses 9 decimal (based on 10) numbers into 4 bytes using binary format to represent the DECIMAL column value. 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:

##112132425363748494

Remaining	bytes
Number of digits
0	0

Storage requirements for date and time types

DATE3 bytesDATETIME8 bytesTIMESTAMP4 bytesTIME3 bytesYEAR1 byte

Column types	Storage requirements

Storage requirements for string type

##CHAR(MM##VARCHAR(M)+1 byte, where BINARY(M) bytes, 0 <= M)LLLLLLLLLvalue1','value2value1','value2

Column type

Storage requirements

)

bytes, 0 <= M <= 255

L

L <= M and 0 <= M <= 65535 (see notes below)

M

M <= 255##VARBINARY(

L +1 byte, where

<= M and 0 <= M <= 255TINYBLOB, TINYTEXT

+1 byte, of which

< 28BLOB, TEXT

+2 bytes, of which

< 216 MEDIUMBLOB, MEDIUMTEXT

+3 bytes, of which

< 224LONGBLOB, LONGTEXT

+4 bytes, of which

< 232ENUM('

',...)1 or 2 bytes, depending Based on the number of enumerated values ​​(up to 65,535 values)

SET('

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

VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示)，而不是该类型的最大可能的大小。例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串(L)的长度，加上一个记录字符串长度的字节。对于字符串'abcd'，L是4，存储需要5个字节。

对于CHAR、VARCHAR和TEXT类型，前面的表中的值L和M应解释为字符数目，并且列定义中的这些类型的长度表示字符数目。例如，要想保存一个TINYTEXT值需要L字符+ 1个字节。

要想计算用于保存具体CHAR、VARCHAR或者TEXT列值的字节数，需要考虑该列使用的字符集。在具体情况中，当使用Unicode时，必须记住所有Unicode字符使用相同的字节数。

注释：VARCHAR列的有效最大长度为65,532字符。

NDBCLUSTER引擎只支持固定宽度的列。这说明MySQL簇中的表中的VARCHAR列的行为如同类型CHAR(不同的是每个记录仍然有一个额外字节空间)。例如，在Cluster表中，声明为VARCHAR(100)的列中的每个记录存储时将占用101个字节，无论实际存储的记录中的字符串的长度为多少。

BLOB和TEXT类需要 1、2、3或者4个字节来记录列值的长度，取决于该类的最大可能的长度。

在NDB Cluster存储引擎中，TEXT和BLOB列的实施是不同的，其中TEXT列中的每个记录由两个单独部分组成。一个是固定大小(256字节)，并且实际上保存在原表中。另一个包括超出256字节的任何数据，保存在隐含的表中。第2个表中的记录总是2,000字节长。这说明如果size<= 256，TEXT列的大小为256(其中size表示记录的大小)；否则，大小是256 +size+(2000–(size–256)%2000)。

ENUM对象的大小由不同的枚举值的数目确定。枚举用一个字节，可以有255个可能的值。当枚举的值位于256和65,535之间时，用两个字节。

SET对象的大小由不同的set成员的数量确定。如果set大小是N，对象占(N+7)/8个字节，四舍五入到1、2、3、4或者8个字节。SET最多可以有64个成员。

2. 选择正确的数据类型

为了优化存储，在任何情况下均应使用最精确的类型。例如，如果列的值的范围为从1到99999，若使用整数，则MEDIUMINT UNSIGNED是好的类型。在所有可以表示该列值的类型中，该类型使用的存储最少。

用精度为65位十进制数(基于10)对DECIMAL 列进行所有基本计算(+、-、*、/)。

使用双精度操作对DECIMAL值进行计算。如果准确度不是太重要或如果速度为最高优先级，DOUBLE类型即足够了。为了达到高精度，可以转换到保存在BIGINT中的定点类型。这样可以用64位整数进行所有计算，根据需要将结果转换回浮点值。

3. 使用来自其他数据库引擎的列类型

为了使用由其它卖方编写的SQL执行代码，MySQL按照下表所示对列类型进行映射。通过这些映射，可以很容易地从其它数据库引擎将表定义导入到MySQL中：

其它卖方类型	MySQL类型
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

在创建表时对列类型进行映射，然后原来的类型定义被丢弃。如果你使用其它卖方的类型创建一个表，然后执行DESCRIBE tbl_name语句，MySQL使用等效的MySQL类型来报告表的结构。例如：

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)

 以上就是MySQL基础教程4 —— 数据类型之存储需求及如何选择正确的类型的内容，更多相关内容请关注PHP中文网（www.php.cn）！