MySQL数据类型详解及如何选择

仅图片来自：https://www.cnblogs.com/jennyyin/p/7895010.html

数值类型

数值类型大致可分为两类：整数、浮点数（小数）

MySQL 允许我们指定数值字段中的值是否有正负之分(UNSIGNED)或者用零填补(ZEROFILL)。

MySQL中有一个可选的宽度指示器，如INT（6），6即是宽度指示器。该宽度指示器并不会影响int列存储字段的大小,也就是说,超过6位它不会自动截取,依然会存储,只有超过它本身的存储范围才会截取;此处宽度指示器的作用在于该字段是否有zerofill,如果有就把未满足6位的部分用0来填充，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。

decimal,numeric,double,float

decimal,numeric属于标准数据类型，指定精度和刻度；decimal的存储大小不确定，numeric的存储大小默认8个字节。
double,float属于非标准数据类型，在DB中保存的是近似值，而Decimal则以字符串的形式保存数值。
float，double类型是可以存浮点数（即小数类型），但是float有个坏处，当你给定的数据是整数的时候，那么它就以整数给你处理。这样我们在存取货币值的时候自然遇到问题，我的default值为：0.00而实际存储是0，同样我存取货币为12.00，实际存储是12。

float(M,S) M为小数点前后的数字总长度，S为小数点后长度
double同float，只是精度不同。
Decimal和Numric经常用来存储货币值（该值的准确精度是极其重要的值）

DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。，因此该例中能存储的值范围是-9999999.99到9999999.99。

字符串类型

MySQL 提供了8个基本的字符串类型（CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET），可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。
需要注意的是，一个英文字符占用一个字节，一个汉字（GBK编码，每个字符最多占2字节；UTF-8编码，每个字符最多占3字节）。

CHAR和VARCHAR类型

CHAR类型将字符串存储为定长字符串，并且必须在圆括号内用一个大小修饰符来定义（宽度指示器）。这个大小修饰符的范围从0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

VARCHAR类型将字符串存储为变成字符串，也必须用一个大小修饰符来定义（宽度指示器），至于指示器的范围下面专门说明。

CHAR和VARCHGAR的区别

两者的区别在于MySQL数据库处理这个指示器的方式不同，CHAR把指示器的大小当做该字段能存储的字符串值字节的大小，不足的就用空格补齐。而VARCHAR把指示器的大小当做该字段能存储的字符串值字节的最大长度（由于长度不是固定的，因此需要增加额外字节来存储字符串本身的长度），对于长于指示器的字符会被截断，短于指示器长度的字符不会被空格填充。
也就是说**，CHAR(M)定义的列的长度为固定的**，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~X（X的值下面会计算）之间，VARCHAR值保存时不进行填充。

两者的选择

因为VARCHAR类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR类型可以大大地节约磁盘空间、提高存储效率。但如果确切知道字符串长度,比如就在50~55之间,那就用CHAR因为CHAR类型由于本身定长的特性使其性能要高于VARCHAR。
比如手机号推荐用char(11),char(11)在查询上更有效率，而且手机号是一个活跃字段参与逻辑会很多。

VARCHAR类型能存多长的字符（即指示器的最大值）

首先要明确两点，一是mysql的记录行长度是有限制的，不是无限长的，这个长度是64K，即65535个字节。而是一个英文字符占一个字节，utf8编码的一个汉字字符占3个字节，gbk编码的一个汉字字符占2个字节，latin1编码一个汉字字符占1个字节。

下面仅以utf8编码来计算VARCHAR类型能存多长的字符

65535 / 3 = 21845，也就是说指示器的值可以设置为21845，但是实际这样会报错，这是因为mysql对于变长类型的字段会有1－2个字节用来保存字符长度：当字符数小于等于255时，mysql只用1个字节来记录，因为2的8次方减1只能存到255；当字符数多于255时，就得用2个字节来存长度了。

65535 - 2 = 65533；65533 / 3 = 21844 余 1；

实际计算并不是-2，而是还有其他字段所占字节数（下面计算式的括号部分），-2只是字符串长度计算字节数，因此正确的计算式如下：

65535 - (4 + 4 + 2 + 1 + 5) - 2 = 65535 - 16 - 2 = 65517；
65517 / 3 = 21839；

因此，VARCHAR类型能存储最大字符数为21839，注意，这里不是说一定可以把varchar类型的长度设为21839，别忘了一个记录行最大为64K，因此还需好考虑记录行的总大小。不过，我们也不会把该字段的长度设为这么大，有这么大的一个字段对于整个表操作来说是一个严重的瓶颈，阿里巴巴开发手册中提到长度大于50000的字段，建议新建表。

还有需要注意的是，MySQL4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节），5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个。

时间类型

数据类型的选择

• 合理选择类型，可以大大减少磁盘空间及磁盘I/0读写开销，减少内存占用，减少CPU的占用率
• 选择相对简单的数据类型，数字类型相对字符串类型要简单的多，尤其是在比较运算时，所以我们应该选择最简单的数据类型。比如有的人就喜欢用时间戳来存储日期，因为日期是一个参与比较的重要字段。
• 列属性尽量为NOT NULL，MYSQL对NULL字段索引优化不佳，增加更多的计算难度，同时在保存与处理NULL类形时，也会做更多的工作，所以从效率上来说，不建议用过多的NUL。所以数字类型的默认值组好设为0，字符类型的默认值最好设为空字符串。

对记录行大小限制为65535字节的说明

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row length for the default innodb_page_size of 16KB is about 8000 bytes. However, for an InnoDB page size of 64KB, the maximum row length is approximately 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.

除了可变长度列（varbinary、varchar、blob和text），对于4KB、8KB、16KB和32KB的页面大小，最大行长度略小于页面的一半。例如，默认innodb页面大小16kb的最大行长度约为8000字节。但是，对于64KB的InnoDB页面大小，最大行长度约为16000字节。longblob和longext列必须小于4GB，并且包括blob和文本列在内的总行长度必须小于4GB。

If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page。

如果一行的长度小于半页，则所有行都存储在该页的本地位置。如果超过半页，则选择可变长度列作为外部页外存储，直到行大小小于半页。

Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns:

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs

虽然innodb内部支持大于65535字节的行大小，但mysql本身对所有列的组合大小都施加了65535的行大小限制。