(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 TypeStorage Required
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INTINTEGER 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 个字节
TINYBLOBTINYTEXT L+ 1 个字节,其中 L< 2 8
BLOB,TEXT L+ 2 个字节,其中 L< 2 16
MEDIUMBLOBMEDIUMTEXT L+ 3 个字节,其中 L< 2 24
LONGBLOBLONGTEXT 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
MEDIUMBLOBMEDIUMTEXT 4000
LONGBLOBLONGTEXT 13948
JSON 8100

 

posted @ 2019-03-08 17:16  郭大侠1  阅读(262)  评论(0编辑  收藏  举报