Mysql的所有数据类型和它们的区别

一、数值类型

1. 普通整数数值类型

以下数据类型只能用以保存整数

整数数值类型
类型	存储大小（字节）	有符号的取值范围（允许存在负数）	无符号的取值范围
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	- 327678 ~ 32767	0 ~ 65535
MEDIUMINT	3	- 8388608 ~ 8388607	0 ~ 1677215
INT	4	- 2147483648 ~  2147483647	0 ~ 4294967295
BINTINT	5	- 9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

2. 严格数值数据类型

        保存必须为确切精度的数值，对于需要运用到计算中的数据正确性比近视数值数据类型更高。适合用来存储金额、科学数据等。

严格数值数据类型
类型	说明
INTEGER	整数数据类型，类比INT类型
SMAIIINT	取值范围比较小的整数数据类型
DECIMAL	浮点数数据类型，使用规则是DECIMAL(M,D)。M是精度，D是标度；精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。 M默认为10，D默认为0；M范围为1 ~ 65 (旧版mysql范围是 1 ~ 254)，D范围默为0~30，且不能超过M；可以存储正数、负数和0
NUMERIC	严格数值的浮点数，使用规则同DECIMAL一样。

3. 近似数值数据类型

        一下数据类型都可以用来存储浮点数，既有小数点的数。但因以下数据类型是近似数值数据类型，存储时是采用二进制表示，因此存在精度问题。所以再存储需要用来做计算和大小比较的数据时推荐使用 DECIMAL。

近似数值数据类型
类型	说明	有符号的取值范围
FLOAT	浮点数数据类型；4字节单精度;使用方法 FLOAT(M,D)；占用的存储空间比DOUBLE小	-3.402823466E+38到3.402823466E+38
DOUBLE	浮点数数据类型；8字节双精度;使用方法 DOUBLE(M,D)； 精度比FLOAT更精确	-1.7976931348623157E+308到1.7976931348623157E+308
REAL	浮点数数据类型

二、时间和日期类型

 1. ‘伪日期比null更省空间’

允许将'0000-00-00'保存为“伪日期”(如果不使用NO_ZERO_DATE SQL模式)。这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小)

2. 开启ALLOW_INVALID_DATES SQL模式,允许保存不合法的日期

        若想保存不合法的日期，例如2023-11-31 ，那么MySQL将给会出警告或错误。但业务需要执意要保存这种错误格式，那么我们可以开启ALLOW_INVALID_DATES SQL模式。

在这种模式下，MySQL只验证月范围为从0到12，日范围为从0到31。这些范围可以包括零，因为MySQL允许在 DATE 或 DATETIME 列保存日/月和日是零的日期。这在应用程序需要保存一个你不知道确切日期的生日时非常有用。在这种情况下，只需要将日期保存为'1999-00-00'或'1999-01-00'。

        但需要注意的一点是，如果保存此类日期，DATE_SUB()或DATE_ADD等需要完整日期的函数不会得到正确的结果。

3. 不想在日期中出现0，使用NO_ZERO_IN_DATE SQL模式

4. DATETIME、DATE、TIMESTAMP和YEAR类型，MySQL使用以下规则解释含模糊年值的日期：

·         00-69范围的年值转换为2000-2069。（本世纪）

·         70-99范围的年值转换为1970-1999。（上世纪）

5. Mysql的时间函数

                Mysql时间函数大全-CSDN

时间和日期类型
类型	数据格式	数值范围	示例说明
DATE	YYYY-MM-DD		2023-11-30
DATETIME	YYYY-MM-DD  HH:MM:SS		2023-11-30 14:34:40，允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符，例如2023.11.30 14:34:40同前面的格式是一样的日期
TIMESTAMP	YYYY-MM-DD  HH:MM:SS	1970 ~ 2037；	可以设置为自动初始化当前时间戳或自动更新         以UTC格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	示例值： 14:34:40 可为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。
YEAR	YYYY	四位数字或字符串：1901 ~ 2155 两位字符串：00~99 两位整数：1 ~ 99	2023

三、字符串（String）类型

1. 字符集

        ASCII属性为CHAR列分配latin1字符集。UNICODE属性分配ucs2字符集。

2. BINARY的填充

        （1）设计表money为binary

（2） 插入数据

insert into number value (10,'a');

（3）查询比对,第一条sql是只查询a,第二天sql是加上拼接的0字节进行的查询

3. 严格模式的开启对字符串存储的影响

        如果存入的数据超过规定的长度，则会被截取。当开启严格模式的时候，SQL将会产生错误，然后值会被拒绝。

        当严格模式未开启的时候，则只会产生一条警告，然后数据被截取后存入。

字符串类型
	共同点	类型	长度范围	说明
CHAR 和 ARCHAR （两者在存储和检索中都不进行大小写转换）	1. 索引前缀长度可选	CHAR	0 ~ 255 （字符）	长度将在设置时固定 当保存数值不足设置的长度时候，会在右侧填充空格达到指定的长度，检索的时候尾部的空格会被删除，所以如果存入的数据末尾存在空格，被检索的时候就会被删除。
		VARCHAR	0 ~ 65535 （字符）	可变长度字符串，不会进行填充。
BINARY 和 VARBINARY	1.类比VARCHAR,包含二进制字符串,包含字节字符串。 2. 没有字符集，并且排序和比较基于列值字节的数值值。	BINARY	0 ~ 255 （字节）	长度将在设置时固定； 当保存数值不足设置的长度时候，会在右侧填充0x00(零字节)达到指定的长度； 大小上： 0x00 < 空格；
		VARBINARY	0 ~ 65535 （字节）	可变长度字节串，不会进行填充。
BLOB 和 TEXT （两者在存储和检索中都不存在大小写转换）	1. 当保存或检索时不删除尾部空格 2. 不能有默认值； 3. 必须指定索引前缀的长度	BLOB	单位：（字节） TINYBLOB：0~255 BLOB：0~65,535  MEDIUMBLOB：0~16,777,215  LONGBLOB：0~4,294,967,295	【字节字符串】 1. 是一个二进制大对象，可以容纳可变数量的数据 2. 有4种BLOB类型： TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。 3. BLOB列没有字符集，排序和比较基于列值字节的数值值 4. 会占用大量存储空间
		TEXT	单位：（字节） TINYTEXT：0~255 TEXT：0~65,535 MEDIUMTEXT：0~16,777,215 LONGTEXT：0~4,294,967,295	【字符字符串】 1. 有4种TEXT类型： TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT 对应4种BLOB类型，有相同的最大长度和存储需求。 2. 有一个字符集，排序和比较根据字符集的校对规则。
		ENUM	1 ~ 65,535(元素)	1. ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值 2.允许是空字符串‘’或null； 3.如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素； 4.每个枚举值有一个索引,允许的值列中的值从1开始编号,空字符串错误值的索引值是0; 5. 如果将一个数字保存到ENUM列，数字被视为索引，并且保存的值是该索引对应的枚举成员；
		SET	1-64（成员）	1.SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值； 2.当创建表时，SET成员值的尾部空格将自动被删除 3.为列分配值时需注意大小写 4.SET值按数字顺序排序。NULL值排在非NULL SET值的前面

四、多维上的点、线、面、几何类型

点、线、面类型
类型	格式		说明
POINT	POINT(x y)； x为经度，y为维度。两者之间用空格相隔开	点	存储二维平面地理坐标的单个点
MULTIPOINT	MULTIPOINT(x1 y1, x2 y2, x3 y3) 可以存储多个点，点与点用逗号分隔		存储二维平面地理坐标的多个个点
LINESTRING	LINESTRING(x1 y1,x2 y2,x3 y3) x为经度，y为维度。点与点用逗号分隔，经纬度之间用空格分隔	线	存储一组连续的直线段
MULTILINSTRING	MULTILINESTRING((x1 y1, x2 y2), (x3 y3, x4 y4)) 可以存储多个直线，直线与直线用集合形式下的逗号分隔		存储一多个连续的线段
POLYGON	POLYGON（(x1 y1,x2 y2,x3 y3,x4 y4)，(x5 y5,x6 y6,x7 y7,x8 y8)） x为经度，y为维度。点于点用逗号分隔，经纬度之间用空格分隔	面	存储一个面
MULTIPOLYGON	POLYGON（((x1 y1,x2 y2,x3 y3,x4 y4))，((x5 y5,x6 y6,x7 y7,x8 y8))）		存储多个面
GEOMETRY	GEOMETRY(POINT(x1 y1)) 里面可以跟子类中的任意一个	几何体	存储一个几何体； 它是所有空间集合类型的基类，其他类型如POINT、LINESTRING、POLYGON都是它的子类。
GEOMETRYCOLLECTION	GEOMETRYCOLLECTION(POINT(x1 y1), POINT(x2 y2), LINESTRING(x3 y3,x4 y4))		存储多个几何体

五、JSON类型

JSON类型
类型	说明
JSON	主要用于存储json对象和json数组两种格式的数据，当JSON类型的数据插入时，会自动校验数据格式，若不是JSON则会报错。 字段可以无线拓展 会自动处理稀疏字段，避免了null值造成的冗余存储 支持索引 此功能是mysql 5.7之后才支持的功能。