mysql数据类型存储需求及如何选择正确的类型

1. 列类型存储需求

根据类别列出了MySQL支持的每个列类型的存储需求。

MyISAM表中行的最大大小为65,534字节。每个BLOB和TEXT列 账户只占其中的5至9个字节。

如果MyISAM表包括变长列类型，记录格式也是可变长度。当创建表时，在某些条件下，MySQL可以将一个列从变长类型改为固定长度的类型或反之亦然。

数值类型存储需求

列类型 存储需求

TINYINT 1个字节

SMALLINT 2个字节

MEDIUMINT 3个字节

INT, INTEGER 4个字节

BIGINT 8个字节

FLOAT(p) 如果0 <= p <= 24为4个字节, 如果25 <= p <= 53为8个字节

FLOAT 4个字节

DOUBLE [PRECISION], item REAL 8个字节

DECIMAL(M,D), NUMERIC(M,D) 变长；参见下面的讨论

BIT(M) 大约(M+7)/8个字节

DECIMAL(和NUMERIC)的存储需求与具体版本有关：

使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。每个值的整数和分数部分的存储分别确定。每个9位数的倍数需要4个字节，并且“剩余的”位需要4个字节的一部分。下表给出了超出位数的存储需求：

剩余的 字节

位数 数目

0 0

1 1

2 1

3 2

4 2

5 3

6 3

7 4

8 4

9 4

日期和时间类型的存储需求

列类型 存储需求

DATE 3个字节

DATETIME 8个字节

TIMESTAMP 4个字节

TIME 3个字节

YEAR 1个字节

字符串类型的存储需求

列类型 存储需求

CHAR(M) M个字节，0 <= M <= 255

VARCHAR(M) L+1个字节，其中L <= M 且0 <= M <= 65535(参见下面的注释)

BINARY(M) M个字节，0 <= M <= 255

VARBINARY(M) L+1个字节，其中L <= M 且0 <= M <= 255

TINYBLOB, TINYTEXT L+1个字节，其中L < 28

BLOB, TEXT L+2个字节，其中L < 216

MEDIUMBLOB, MEDIUMTEXT L+3个字节，其中L < 224

LONGBLOB, LONGTEXT L+4个字节，其中L < 232

ENUM('value1','value2',...) 1或2个字节，取决于枚举值的个数(最多65,535个值)

SET('value1','value2',...) 1、2、3、4或者8个字节，取决于set成员的数目(最多64个成员)

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)