Mysql基础篇（3）—— MySQL数据库类型

整数类型

类型名	占用字节	有符号范围	无符号范围(UNSIGNED)
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

浮点类型

类型名	占用字节	有符号范围	无符号范围(UNSIGNED)
FLOAT	4	（-3.4E+38，-1.17E-38），0，（1.17E-38，3.4E+38）	0，（1.17E-38，3.4E+38）
DOUBLE	8	（-1.7E+308，-2.2E-308），0，（2.2E-308，1.7E+308）	0，（2.2E-308，1.7E+308）

ps：

• mysql存储浮点数的格式为：符号(S)、尾数(M)和阶码(E)。有无符号，正数的范围是一样的

• 尾数是二进制存储的，除了以5结尾的小数，其他的都会有精度上的缺失。所以浮点数不要用“=”来判断两个数是否相等

• 不建议定义精度M和标度D

• 整数部分超出范围会直接报错，小数部分超出范围会四舍五入

定点数类型

DECIMAL

使用DECIMAL(M,D)方式方式表示高精度小数。0<=M<=65，0<=D<=30，D<M。DECIMAL的存储空间为M+2。

底层是以字符串的形式存储的，这就决定了它是精确的。

不指定MD，默认是DECIMAL(10, 0)。

定点数和浮点数

定点数精确但是通常情况占用空间相对较大(M大于2/6的时候就大于float/double的占用大小了)。

相反，浮点数通常情况下相对于定点数来说占用空间小，范围大但是不精确。

位类型

类型名	长度	长度范围	占用字节
BIT(M)	M,默认1	1<=M<=64	约为(M + 7) / 8 个字节

###　日期与时间类型

类型名	介绍	占用字节	格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

ps:

• TIMESTAMP存储数据的时候会转换成距离1970-01-01 00:00:01 UTC的毫秒值，查询的时候会根据查询时候所在的时区将毫秒值转换成时间，因此不同时区查看看一个数据会得到不同的结果。
• TIMESTAMP类型的日期比较相对于DATATIME来说较快。

文本字符串类型

类型	字符长度	长度范围（存储字节范围）	占用存储空间
CHAR(M)	M	0<=M<=255	M字符所占用的字节
VARCHAR(M)	M	0<=M<=65535	M字符所占用的字节+1个字节
TINYTEXT	L	0<=L<=255	L字符所占用的字节+2个字节
TEXT	L	0<=L<=65535	L字符所占用的字节+2个字节
MEDIUMTEXT	L	0<=L<=16777215	L字符所占用的字节+3个字节
LONGTEXT	L	0<=L<=4294967295	L字符所占用的字节+4个字节
ENUM	L	1<=L<=65535	1或2个字节
SET	L	0<=L<=64	1,2,3,4或8个字节

CHAR和VARCHAR

类型	特点	空间上	时间上	试用场景
CHAR(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	节省存储空间	效率低	非CHAR的情况

具体存储引擎的情况：

• MyISAM：最好用CHAR，这样使得整个表静态化，以空间换时间
• MEMORY：底层都是用固定长度去存储数据，用啥都一样
• InnoDB：建议使用varchar类型，因为InnoDB内部行存储的格式并没有区分固定长度和可变长度（所有数据行都是用指向数据列值得头指针），而且主要影响性能因素是数据行使用的存储总量，所以一般情况除了简短并且固定长度的使用char，其他都使用varchar。

二级制字符串

类型	特点	值的长度	占用空间
BINARY(M)	固定长度	M （0 <= M <= 255）	M个字节
VARBINARY(M)	可变长度	M （0 <= M <= 65535）	M+1个字节

和char、varchar很像

二进制大对象

类型	长度	长度范围	占用空间
TINYBLOB	L	0 <= L <= 255	L + 1 个字节
BLOB	L	0 <= L <= 65535（相当于64KB）	L + 2 个字节
MEDIUMBLOB	L	0 <= L <= 16777215 （相当于16MB）	L + 3 个字节
LONGBLOB	L	0 <= L <= 4294967295（相当于4GB）	L + 4 个字节

TEXT和BLOB的使用注意事项

• BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的" 空洞 "，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理

• 把BLOB或TEXT列 分离到单独的表 中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的碎片 ，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

JSON类型

MYSQL5.7已经支持，MYSQL8.0加了自动验证的json文档和优化的存储结构。

空间类型

MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如一个十字路口等等。MySQL中使用 Geometry（几何） 来表示所有地理特征。Geometry指一个点或点的集合，代表世界上任何具有位置的事物。

MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括单值类型：GEOMETRY、POINT、LINESTRING、POLYGON以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 。