【译】MYSQL-参考手册-11.2数值类型-优快云博客

本文详细介绍了MySQL5.7中的数值类型，包括整数类型、定点数类型、浮点数类型和BIT类型，解释了每种类型的存储方式、取值范围、属性及处理数值范围超出和计算溢出的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

本文是本人在阅读MYSQL5.7参考手册11.2节时翻译的译文，如有差错，欢迎大家指正

11.2 数值类型

11.2.1 整数类型 (存储精确值) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
11.2.2 定点数类型 (存储精确值) - DECIMAL, NUMERIC
11.2.3 浮点数类型 (存储近似值) - FLOAT, DOUBLE
11.2.4 BIT类型 - 存储位值
11.2.5 数值类型的属性
11.2.6 数值范围超出和计算溢出的处理

11.2.1 整数类型(精确存储)- INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

Mysql 支持SQL标准的整数类型：INTEGER(INT)和SMALLINT。作为扩展，MySQL也支持TINYINT, MEDIUMINT,和BIGINT，下面的表格展示了Mysql不同整数类型的取值范围和占用存储空间

11.2.2 定点数类型 (精确存储) - DECIMAL, NUMERIC

DECIMAL和NUMERIC用于存储一个精确的值.如果要确保存储值的精确性,比如金融数据，那么你应该使用这些类型。在MySQL中,NUMERIC与DECIMAL等效,所以下面关于DECIMAL的描述同样适用于NUMERIC。

MySQL以二进制存储DECIMAL。详情请阅读12.22节“Precision Math”.

声明一个DECIMAL列时,通常可以指定精度(precision)和刻度(scale);

例如:salary DECIMAL(5,2)

在这个例子中，5表示精度(precision)，2表示刻度(scale)，精度表示能存储的总位数，刻度表示小数点之后能存储的有效位
SQL标准规定DECIMAL(5,2)必须能存储任何总位数为5位且精度为小数点后两位的数值，所以salary的能存储的范围是[-999.99,999.99]
在SQL标准规范中,表达式DECIMAL(M)与DECIMAL(M,0)等价，表达式中允许指定M的值，MySQL支持这两种形式的语法，M的默认值是10。
如果刻度为0，那么值中将不包含小数点和小数部分。
DECIMAL能表示的最大位数为65，但是实际的存储范围受到定义的精度和刻度限制。当为某个DECIMAL列赋值，该值的小数点后位数超过指定的刻度所允许的位数时，该值将转换为该刻度。(精确的行为是特定于操作系统的，但通常效果是截断到允许的位数。)

11.2.3 浮点数类型 (近似存储) - FLOAT, DOUBLE

FLOAT和DOUBLE表示近似的数据值类型。MySQL使用4个字节存储FLOAT型数据，8个字节存储DOUBLE类型数据
对于FLOAT, SQL标准允许在圆括号中的关键字FLOAT后面以位为单位指定精度(并不是指数的范围);也就是说FLOAT(p)。MySQL同样提供这个可选值，但是这个精度值仅仅用于确定存储空间。[0,23]表示单精度的FLOAT,[24-53]表示双精度DOUBLE。
MySQL允许非SQL标准的表达式:FLOAT(M,D)、REAL(M,D)、DOUBLE PRECISION(M,D)。这里的M是指能存储的总位数，D表示小数点后能存储的位数。比如说一个列被定义为FLOAT(7,4)，类似-999.9999的值将可以被存储，如果你存入值999.00009实际存储的会是近似值999.0001。
由于浮点数存储的是一个近似值而非精确值，试图将它们作为准确值来比较会导致一些问题，它们还受制于平台和具体实现。更多的信息请参见B.6.4.8节Problems with Floating-Point Values
为了更好的可移植性，需要存储近似值的数值数据应该使用FLOAT或者DOUBLE PRECISION并且不指定精度和位数

11.2.4 BIT类型 - BIT

BIT类型类型用来存储位信息，BIT（M）允许存储M位数据，M的范围是[1-64]

可以使用b'value'表达式来表达字面值，value是一个0和1组成的二进制串，例如b'111'和b'10000000'代表7和128。详情参考 9.1.5, Bit-Value Literals.
如果你要存储一个位数小于M的值到BIT(M),那么这个值左边会补0，举个例子：如果存储'101'到BIT(6)的列，实际上等同于存储'000101'。

11.2.5 数值类型的属性

MySQL允许在整数类型的基本关键字后面的括号中指定整数数据类型的显示宽度。例如INT（4）指定一个可显示4位宽度的INT类型。当数值小于4位时，应用程序可以用空格左填充整数值。(也就是说，这个宽度出现在结果集返回的元数据中。是否使用它取决于应用程序) 这个宽度值不会限制列数据存储值的范围,比他宽度更宽的数据也能够正确的显示，例如，一个列被声明为SMALLINT(3)，值的范围仍然是[-32768,32767],比3位数更大的整数仍然可以正常的显示
当我们与可选的(非SQL标准)ZEROFILL属性一起使用时，将用0取代默认的填充空格符，也就是说，当一个列为INT(4) ZEROFILL,数值5将会表示为0005

注意：

对于涉及到表达式或者UNION查询时，ZEROFILL属性将会被忽略
如果你存储了一个大于显示宽度的整数值，当进行一些复杂的jion操作时你可能会遇到一些问题，这种情况下，MySQL假定数据值符合列显示宽度

所有的整数类型都拥有一个可选的(非标准的)UNSIGNED属性,unsigned类型可用来存储非负数或者存储一个更大的数值。举例来说：如果一个INT类型的列声明为UNSIGNED,那么它能表示的范围大小相同，但是表示范围的值由[-2147483648,2147483647]变成了[0 ,4294967295]
浮点数和定点数也可以和整数一样被声明为UNSIGNED,这个属性可以防止负数存储到列中，与整数不同的是它们的取值上限还是和原来一样
如果你为一个列指定ZEROFILL属性，那么MySQL会自动为其添加UNSIGNED属性
整数和浮点数类型都具有AUTO_INCREMENT属性，当你插入一个NULL值到具有AUTO_INCREMENT属性的列时，这个列的值将被设置为AUTO_INCREMENT序列中的下一个值，也就是value+1，value取这个表中该列的最大值(AUTO_INCREMENT序列由1开始.)

注意:

向这个列中插入0等同于插入NULL，除非开启SQL mode的NO_AUTO_VALUE_ON_ZERO属性
当插入一个NULL值生成AUTO_INCREMENT时需要为列赋予NOT NULL属性，如果这个列被声明为允许为NULL，那么将会插入NULL。为这个列插入一个值时这个值同时也会刷新AUTO_INCREMENT序列，确保下一个序列值的与插入的值顺序一致

11.2.6数值范围超出和计算溢出的处理

当MySQL存储一个超出范围的值到数值类型的列时，处理结果取决于当时SQL mode的设置:

如果启用了严格的SQL mode(strict SQL mode),MySQL拒绝插入一个超范围的值，这与SQL标准是吻合的。
如果没有启用严格的SQL mode,MySQL将使用适当的端点值(上限值或者下限值)插入到数值列:
1、当存储一个超范围的值到整数列时，MySQL存储列数据类型相应的端点值。
2、当浮点或定点数据类型列被赋值超过指定(或默认)精度和比例的范围时，MySQL存储对应的端点值

假设我们定义一个表t1:

CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);
复制代码

在启用严格的SQL mode时,会发生一个错误

mysql> SET sql_mode = 'TRADITIONAL';
mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
复制代码

在未启用严格的SQL mode时，会发生一个警告

mysql> SET sql_mode = '';
mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 1 |
| Warning | 1264 | Out of range value for column 'i2' at row 1 |
+---------+------+---------------------------------------------+
mysql> SELECT * FROM t1;
+------+------+
| i1   | i2   |
+------+------+
|  127 |  255 |
+------+------+
复制代码

如果未启用严格的SQL mode，列的转换时发生ALTER TABLE, LOAD DATA, UPDATE, 和多行的插入的警告，在严格的SQL mode下，这些操作会失败，值不会被插入或改变，这还取决于这个表是否是事务表或一些其他因素。详情请见5.1.10章 Server SQL Modes。

这段水平有限，翻译的很牵强，官方原文:
When strict SQL mode is not enabled, column-assignment conversions that occur due to clipping are reported as warnings for ALTER TABLE, LOAD DATA, UPDATE, and multiple-row INSERT statements. In strict mode, these statements fail, and some or all the values are not inserted or changed, depending on whether the table is a transactional table and other factors. For details, see Section 5.1.10, “Server SQL Modes”.

表达式求值期间发生溢出将导致错误，例如signed BIGINT的最大值是9223372036854775807，所以下面的表达式会产生错误

mysql> SELECT 9223372036854775807 + 1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'
复制代码

为了使得这个表达式正确，将值强转成unsigned

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
|                       9223372036854775808 |
+-------------------------------------------+
复制代码

是否溢出取决于操作数的范围，所以另外一个解决方法是使用定点数,因为DECIMAL的取值范围是大于整数的

mysql> SELECT 9223372036854775807.0 + 1;
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
|     9223372036854775808.0 |
+---------------------------+
复制代码

两个整数相减，而其中一个是无符号数时，结果会返回一个无符号数，但是如果结果是负数，那么会发生一个错误

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
复制代码

如果SQL mode启用NO_UNSIGNED_SUBTRACTION，那么结果将是一个负数

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
复制代码

如果使用此类操作的结果更新无符号整数列，则将结果设置为列类型的最大值，如果启用no_unsigned_subtract，则将结果裁剪为0。如果启用了严格的SQL mode，则会发生错误，并且列保持不变

总结

MySQL支持所有SQL标准数值类型,这些类型包括精确的数值类型（INTEGER, SMALLINT, DECIMAL,和NUMERIC），也包含近似值数据类型（FLOAT, REAL, and DOUBLE PRECISION）。对于MYSQL来说，INT和INTERGER类型是等效的，DEX和FIXED与DECIMAL类型是等效的。PRECISION和DOUBLE类型也是等效的。除非SQL_MODE启用REAL_AS_FLOAT，否则REAL和DOUBLE PRECISION同样是等效的。

BIT类型存储位数据，仅支持MyISAM, MEMORY, InnoDB,和NDB引擎的表。

有关MySQL如何处理向列存储超范围值和表达式计算期间溢出细节请阅读11.2.6节“Out-of-Range and Overflow Handling”。

关于不同数值类型需要占用的存储空间请阅读11.8节, “Data Type Storage Requirements”。

计算结果的类型取决于计算的参数类型和对他们进行的操作,具体信息见12.6.1节 “Arithmetic Operators”。