MySQL 字段类型占用空间

最新推荐文章于 2024-07-25 15:55:44 发布

原创最新推荐文章于 2024-07-25 15:55:44 发布 · 629 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #database

mysql 专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了 MySQL 中的数据类型，包括 NumberType、DateandTimeType 和 StringType。NumberType 包括 TINYINT 到 BIGINT 不同大小的整数以及 FLOAT 和 DOUBLE。DateandTimeType 涵盖了 YEAR 至 TIMESTAMP，考虑了不同精度的存储需求。StringType 中，CHAR、VARCHAR、BINARY 及其变种各有不同的存储规则。此外，还提到了 ENUM 和 SET 的存储方式。这些数据类型的选择对于数据库设计和性能优化至关重要。

Number Type

Data Type	Storage Required
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT, INTEGER	4 bytes
BIGINT	8 bytes
FLOAT§	4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT	4 bytes
DOUBLE [PRECISION], REAL	8 bytes

Date and Time Type

Data Type	MySQL 5.6.4之前	MySQL >=5.6.4
YEAR	1 byte	1 byte
DATE	3 bytes	3 bytes
TIME	3 bytes	3 bytes + fractional seconds storage
DATETIME	8 bytes	5 bytes + fractional seconds storage
TIMESTAMP	4 bytes	4 bytes + fractional seconds storage

Fractional Seconds Precision	Storage Required
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

String Type

Data Type	Storage Required
CHAR(M)	The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Storage Characteristics. Otherwise, M × w bytes, <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set.
BINARY(M)	M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)	L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXT	L + 1 bytes, 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)