MySQL数据类型 - 数字类型 - 详细介绍

林员外聊编程 2020-06-24

761

1. 整数类型（精确值）-INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT

MySQL支持SQL标准整数类型 INTEGER（或INT）和SMALLINT。作为标准的扩展，MySQL还支持整数类型TINYINT、MEDIUMINT和BIGINT。下表显示了每个整数类型所需的存储空间和范围。

类型	存储空间 (字节)	有符号最小值	无符号最小值	有符号最大值	无符号最大值
`TINYINT`	1	`-128`	`0`	`127`	`255`
`SMALLINT`	2	`-32768`	`0`	`32767`	`65535`
`MEDIUMINT`	3	`-8388608`	`0`	`8388607`	`16777215`
`INT`	4	`-2147483648`	`0`	`2147483647`	`4294967295`
`BIGINT`	8	`-2` `⁶³`	`0`	`2` `⁶³` `-1`	`2` `⁶⁴` `-1`

2.定点类型（精确值）- DECIMAL, NUMERIC

DECIMAL和NUMERIC类型存储精确的数字数据值。当保持精确精度非常重要时，例如对于货币数据，使用这些类型。在MySQL中，NUMERIC被实现为DECIMAL，因此下面关于DECIMAL的说明同样适用于NUMERIC。

MySQL以二进制格式存储DECIMAL值。

在DECIMAL列声明中，可以（通常是）指定精度和小数位数。例如：

在本例中，5是精度，2是小数位。精度表示值存储的有效位数，小数位数表示可以存储在小数点后的位数。

标准SQL要求DECIMAL(5,2)能够存储总计5位且带2位小数的任何值，因此可以存储在salary列中的值的范围是-999.99到999.99。

在标准SQL中，语法DECIMAL(M)等同于DECIMAL(M,0)。类似地，语法DECIMAL相当于DECIMAL(M,0)。MySQL支持这两种形式的DECIMAL语法的变体。M的默认值是10。

如果小数位数为0，则DECIMAL
值不包含小数点和小数部分。

DECIMAL的最大位数是65，但是给定DECIMAL列的实际范围可能会被给定列的精度或小数位数限制。如果为此类列分配的小数点后的位数超过允许的值，则该值将转换为符合小数位数要求。（精确的行为是特定于操作系统的，但通常效果是截断到允许的位数。）

3.浮点类型（近似值）- FLOAT, DOUBLE

FLOAT和DOUBLE类型表示近似的数值。MySQL使用4个字节存储单精度值，使用8个字节存储双精度值。

对于FLOAT，SQL标准允许在FLOAT关键字后面的括号中指定精度（而不是指数的范围）；即FLOAT(p)。MySQL也支持这个可选的精度规范，但是FLOAT(p)中的精度值仅用于确定存储空间大小。从0到23的精度将存储为4字节的单精度FLOAT列。从24到53的精度将存储为8字节的双精度DOUBLE列。

MySQL允许使用非标准语法：FLOAT(M, D)或REAL(M,D)或DOUBLE PRECISION(M, D)。在这里，(M, D)表示值最多可以存储M个数字，其中在小数点后可以有D个数字。例如，定义为FLOAT(7,4)的列在显示时看起来像-999.9999。MySQL在存储值时执行舍入，因此如果将999.00009插入FLOAT(7,4)列中，则近似结果为999.0001。

从MySQL 8.0.17开始，不推荐使用非标准的FLOAT(M,D)和DOUBLE(M,D)语法，未来的MySQL版本中将不再支持它。

由于浮点值是近似值，而不是作为精确值存储，因此在比较中尝试将其视为精确值可能会导致问题。它们还受平台或实现依赖性的影响。

为确保最大程度的可移植性，需要存储近似数字数据值的代码应该使用FLOAT或 DOUBLE PRECISION，不指定精度和位数。

4.位值类型 - BIT

BIT数据类型用于存储位值。BIT(M)类型允许存储M位的值。M的范围从1到64。

要指定位值，可以使用b'value'这种形式表示。value是0和1组成的二进制值。例如，b'111'和b'10000000'分别表示7和128。

如果为BIT(M)列指定一个长度小于M位的值，则该值将用零填充在左侧。例如，给BIT(6)列赋值b'101'，实际上与赋值b'000101'相同。

NDB Cluster 存储引擎中，给定NDB表中使用的所有BIT列的位最大合计不得超过4096位。

5. 数字类型属性

MySQL支持扩展，可以选择在类型的基本关键字后面的括号中指定整数数据类型的显示宽度。例如，INT(4)指定一个显示宽度为四位数的INT。应用程序可以使用此可选显示宽度来显示整数值，如果整数值的宽度小于指定宽度，用空格在左侧填充直到满足列指定宽度。（也就是说，此宽度存在于结果集返回的元数据中。是否使用取决于应用程序。）

显示宽度不限制可以存储在列中的值的范围。它也不能阻止大于列显示宽度的值正确显示。例如，列指定为SMALLINT(3)类型，SMALLINT范围为-32768到32767，超出三位数字允许的范围的值将使用三位以上的数字完整显示。

当与可选（非标准）ZEROFILL 属性一起使用时，默认填充值将由空格替换为零。例如，对于声明为INT(4) ZEROFILL的列，5将存储为0005。

从MySQL 8.0.17开始，数字数据类型不推荐使用ZEROFILL属性，整数数据类型也不推荐使用显示宽度属性。在未来的MySQL版本中，将删除对整数数据类型的ZEROFILL 和显示宽度的支持。请考虑使用其他方法来代替这些属性的效果。例如，应用程序可以使用LPAD()函数将结果用0填充到所需宽度，或者将格式化的数字存储在CHAR列中。

所有整数类型都可以有一个可选的（非标准的）UNSIGNED属性。无符号类型可用于控制列中不包含负数，或者使列有更大的数据上限值。例如，如果INT列是UNSIGNED列，则该列范围的容量相同，但其边界值会上移，从-2147483648和2147483647移到0和4294967295。

浮点和定点类型也可以是 UNSIGNED 的。与整数类型一样，此属性防止将负值存储在列中。与整数类型不同的是，列值的上限保持不变。从MySQL 8.0.17开始，对于FLOAT、DOUBLE和DECIMAL类型的列（以及任何同义词），不推荐使用UNSIGNED属性，并且在以后的MySQL版本中将删除对它的支持。请考虑对此类列使用CHECK约束。

如果为数字列指定ZEROFILL属性，MySQL会自动添加UNSIGNED属性。

整数或浮点数据类型可以指定 AUTO_INCREMENT 属性。将NULL插入索引的AUTO_INCREMENT列时，该列将设置为下一个序列值。通常这是value+1，其中value是表中当前列的最大值。（AUTO_INCREMENT序列以1开头。）

将0存储到AUTO_INCREMENT列与存储NULL值具有相同的效果，除非启用了NO_AUTO_VALUE_ON_ZERO SQL模式。

要插入NULL来生成AUTO_INCREMENT值，需要声明列NOT NULL。如果列声明为NULL，则插入NULL将存储NULL值。在自动递增列中插入任何其他值时，该列将设置为该值并重置序列，以便下一个自动生成的值从插入的值开始按顺序跟随。

AUTO_INCREMENT 列不支持插入负值。

CHECK约束不能引用具有AUTO_INCREMENT属性的列，也不能将AUTO_INCREMENT属性添加到CHECK约束中使用的现有列。

从MySQL 8.0.17开始，不推荐对FLOAT和DOUBLE列使用AUTO_INCREMENT属性，并将在以后的MySQL版本中删除这种支持。请考虑从此类列中删除AUTO_INCREMENT属性，或将其转换为整数类型。

6. 超出范围和溢出处理

当MySQL在数值列中存储超出列允许范围之外的数值时，结果取决于当时有效的SQL模式：

●如果启用了严格SQL模式，MySQL会根据SQL标准拒绝超出范围的值并提示错误，插入操作将失败。

●如果没有启用限制模式，MySQL会将该值消减到列数据类型范围允许的相应极值，并存储结果。

将超出范围的值分配给整数列时，MySQL将按照列数据类型范围的相应极值存储。