MySQL系统学习 - 基本数据类型

数字类型

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
FLOAT	4 bytes
DOUBLE [PRECISION], REAL	8 bytes
DECIMAL(M,D),NUMERIC(M,D)	Varies; see following discussion
BIT(M)	approximately (M+7)/8 bytes

字符类型

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

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)

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
‘’	’ ’	4 bytes	‘’	1 byte
‘ab’	'ab ’	4 bytes	‘ab’	3 bytes
‘abcd’	‘abcd’	4 bytes	‘abcd’	5 bytes
‘abcdefgh’	‘abcd’	4 bytes	‘abcd’	5 bytes

时间类型

Data Type	Storage Required Before MySQL 5.6.4	Storage Required as of 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

---

参考引用:

• MySQL 数据类型官方文档
  https://dev.mysql.com/doc/refman/8.0/en/data-types.html
  https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-innodb