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

2023-12-13 04:09:42

一、数值类型

1. 普通整数数值类型

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

整数数值类型
类型存储大小（字节）有符号的取值范围（允许存在负数）无符号的取值范围
TINYINT1-128 ~ 1270 ~ 255
SMALLINT2- 327678 ~ 327670 ~ 65535
MEDIUMINT3- 8388608 ~ 83886070 ~ 1677215
INT4- 2147483648 ~??21474836470 ~ 4294967295
BINTINT5- 9223372036854775808 ~?92233720368547758070 ~?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

? ? ? ? 时间和日期类型
类型数据格式数值范围示例说明
DATEYYYY-MM-DD?2023-11-30
DATETIMEYYYY-MM-DD? HH:MM:SS2023-11-30 14:34:40，允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符，例如2023.11.30 14:34:40同前面的格式是一样的日期
TIMESTAMP

YYYY-MM-DD? HH:MM:SS???????

?
????

1970 ~ 2037；

????可以设置为自动初始化当前时间戳或自动更新

????????以UTC格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区

TIMEHH:MM:SS-838:59:59 ~ 838:59:59

示例值： 14:34:40

可为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。

YEARYYYY

四位数字或字符串：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. 索引前缀长度可选CHAR0 ~ 255 （字符）

长度将在设置时固定

当保存数值不足设置的长度时候，会在右侧填充空格达到指定的长度，检索的时候尾部的空格会被删除，所以如果存入的数据末尾存在空格，被检索的时候就会被删除。

VARCHAR0 ~ 65535 （字符）

可变长度字符串，不会进行填充。

BINARY 和 VARBINARY

1.类比VARCHAR,包含二进制字符串,包含字节字符串。

2.?没有字符集，并且排序和比较基于列值字节的数值值。

BINARY0 ~ 255 （字节）

长度将在设置时固定；

当保存数值不足设置的长度时候，会在右侧填充0x00(零字节)达到指定的长度；

大小上： 0x00 < 空格；

VARBINARY0 ~ 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. 有一个字符集，排序和比较根据字符集的校对规则。

ENUM1 ~ 65,535(元素)

1. ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值

2.允许是空字符串‘’或null；

3.如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素；

4.每个枚举值有一个索引,允许的值列中的值从1开始编号,空字符串错误值的索引值是0;

5.?如果将一个数字保存到ENUM列，数字被视为索引，并且保存的值是该索引对应的枚举成员；

SET1-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为维度。点于点用逗号分隔，经纬度之间用空格分隔

面存储一个面
MULTIPOLYGONPOLYGON（((x1 y1,x2 y2,x3 y3,x4 y4))，((x5?y5,x6?y6,x7?y7,x8?y8))）存储多个面
GEOMETRY

GEOMETRY(POINT(x1 y1))

里面可以跟子类中的任意一个

几何体

存储一个几何体；

它是所有空间集合类型的基类，其他类型如POINT、LINESTRING、POLYGON都是它的子类。

GEOMETRYCOLLECTIONGEOMETRYCOLLECTION(POINT(x1 y1), POINT(x2 y2), LINESTRING(x3 y3,x4 y4))存储多个几何体

五、JSON类型

JSON类型
类型说明
JSON

主要用于存储json对象和json数组两种格式的数据，当JSON类型的数据插入时，会自动校验数据格式，若不是JSON则会报错。

字段可以无线拓展

会自动处理稀疏字段，避免了null值造成的冗余存储

支持索引

此功能是mysql 5.7之后才支持的功能。

文章来源:https://blog.csdn.net/linyunping/article/details/134706433
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。