（1.1）mysql 选择合适的数据类型

【1】mysql 选择合适的数据类型

1、char与varchar

【1.1】char 在内容未满定义长度时，做空格填充，且字符串末尾空格会被截断；超出定义长度也会被截断。  如：char(4)    'abcde' => 'abcd'

【1.2】varchar 在内容未满定义长度时，内容是多少就占多少，以\0 为系统自动加上的默认结束符。额外多占一个字节。

 

2、浮点与定点（float/decimal）

【2.1】浮点：当插入数值超过浮点定义时设置的精度时，会四舍五入截断。

【2.2】定点：当插入数值超过浮点定义时设置的精度时，默认sqlmode下也会会四舍五入截断（但有警告）。在传统模式（traditional）下的话，会报错。

浮点在插入数据时，可能会丢失精度；做算数运算的时候也会有误差。

　　

 

3.日期的选择

【3.1】date　　【3.2】time　　【3.3】datetime　　【3.4】timestamp

　　如果只需要记录年，则用year类型即可；

　　选择原则：

　　（1）用最小的字段类型来记录数据且能保证扩展与可用性；

　　（2）如果要记录年月日时分秒，最好使用datetime类型而不是timestamp，因为timestamp记录的时间范围短很多。

　　（3）如果记录的日期需要让不同时区的用户使用，最好使用timestamp，因为日期类型中只有它能够和实际时区想对应。

 

 

【2】数据类型与长度大小

参考官网：https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

数字类型存储

 

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 <= p <= 53
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

 

DECIMAL( 和 ) 列的 值NUMERIC使用二进制格式表示，该格式将九个十进制（以 10 为基数）数字打包成四个字节。每个值的整数和小数部分的存储分别确定。每个九位数字的倍数需要四个字节，而“剩余” 数字需要四个字节的一部分。下表给出了多余数字所需的存储空间。

剩余数字	字节数
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

日期和时间类型存储要求

对于TIME、 DATETIME和 TIMESTAMP列，在 MySQL 5.6.4 之前创建的表所需的存储空间与从 5.6.4 开始创建的表不同。这是由于 5.6.4 中的更改允许这些类型具有小数部分，这需要 0 到 3 个字节。

数据类型	MySQL 5.6.4 之前需要存储	MySQL 5.6.4 所需的存储空间
YEAR	1 个字节	1 个字节
DATE	3 个字节	3 个字节
TIME	3 个字节	3 字节 + 小数秒存储
DATETIME	8 个字节	5 字节 + 小数秒存储
TIMESTAMP	4字节	4 字节 + 小数秒存储

从 MySQL 5.6.4 开始，存储 YEAR和 DATE保持不变。但是， TIME、 DATETIME和 TIMESTAMP的表示方式不同。DATETIME打包的效率更高，非小数部分需要 5 个而不是 8 个字节，并且所有三个部分都有一个小数部分，需要 0 到 3 个字节，具体取决于存储值的小数秒精度。

小数秒精度	需要存储
0	0 字节
1, 2	1 个字节
3、4	2 个字节
5、6	3 个字节

例如，TIME(0)、 TIME(2)、 TIME(4)和 TIME(6)分别使用 3、4、5 和 6 个字节。TIME并且 TIME(0)是等效的并且需要相同的存储空间。

字符串类型存储要求

在下表中，M表示非二进制字符串类型的声明列长度和二进制字符串类型的字节。 L表示给定字符串值的实际长度（以字节为单位）。

数据类型	需要存储
CHAR(M)	紧凑的 InnoDB 行格式系列优化了可变长度字符集的存储。请参阅 COMPACT 行格式存储特性。否则，M× w字节，255，其中 是字符集中最大长度字符所需的字节数。<= M <=w
BINARY(M)	M字节，0 255<= M <=
VARCHAR(M), VARBINARY(M)	L如果列值需要 0 - 255 个字节，则L+ 1 个字节，如果值可能需要超过 255 个字节，则 + 2 个字节
TINYBLOB, TINYTEXT	L+ 1 个字节，其中 L< 2 8
BLOB,TEXT	L+ 2 个字节，其中 L< 2 16
MEDIUMBLOB, MEDIUMTEXT	L+ 3 个字节，其中 L< 2 24
LONGBLOB, LONGTEXT	L+ 4 个字节，其中 L< 2 32
ENUM('value1','value2',...)	1 或 2 个字节，取决于枚举值的数量（最多 65,535 个值）
SET('value1','value2',...)	1、2、3、4 或 8 个字节，取决于集合成员的数量（最多 64 个成员）

可变长度字符串类型使用长度前缀加数据存储。长度前缀根据数据类型需要一到四个字节，前缀的值是 L（字符串的字节长度）。例如，一个 MEDIUMTEXT值的存储需要 L字节来存储值加上三个字节来存储值的长度。

Bolb 等大文本类型存储

TEXT、 BLOB和 JSON列在NDB存储引擎中的实现方式不同，其中列中的每一行由两个独立的部分组成。其中之一是固定大小（ 和 为 256 字节， 为 TEXT4000BLOB字节JSON），实际上存储在原始表中。另一个包含超过 256 字节的任何数据，这些数据存储在隐藏的 blob 部分表中。第二个表中的行大小由列的确切类型决定，如下表所示：

类型	斑点零件尺寸
BLOB,TEXT	2000
MEDIUMBLOB, MEDIUMTEXT	4000
LONGBLOB, LONGTEXT	13948
JSON	8100