数值数据类型
概述
MySQL 支持 SQL 标准中所有数值数据类型，其中包括精确数值数据类型（INTEGER、SMALLINT、DECIMAL 和 NUMERIC），也包括近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION）。关键字 INT 是 INTEGER 的代名词，关键字 DEC 和 FIXED 是 DECIMAL 的代名词。MySQL 将 DOUBLE 视为 DOUBLE PRECISION 的代名词（一种非标准扩展）。除非启用 SQL 的 REAL_AS_FLOAT 模式，否则 MySQL 也把 REAL 视为 DOUBLE PRECISION 的代名词（一种非标准变体）。
BIT 数据类型用于存储 bit 值，MyISAM、MEMORY、InnoDB 和 NDB 数据库表支持该数据类型。
关于 MySQL 如何处理超出范围和溢出的数值的更多信息，请查看 “Out-of-Range and Overflow Handling”。
关于数值数据类型的存储要求的信息，请查看 Section 11.7, “Data Type Storage Requirements”。
关于处理数值函数的说明，请查看 Section 12.5, “Numeric Functions and Operators”。数值运算结果的数据类型是取决于运算值的类型及对它们执行的操作。详情请看 Section 12.5.1, “Arithmetic Operators”。
数值数据类型的句法
对于整型数据类型，M 表示最大的显示宽度，其最大值为 255。显示宽度与类型可存储的值的范围无关，如 Section 11.1.6, “Numeric Type Attributes” 中所述。
对于浮点型和定点型数据类型，M 表示可存储数字的总个数。
从 MySQL 8.0.17 开始，不建议使用整型数据类型的“显示宽度”属性，并将在未来的 MySQL 版本中移除它。
若为一个数值类型的列指定 ZEROFILL，MySQL 将会为该列自动添加 UNSIGNED 属性。
从 MySQL 8.0.17 开始，不建议使用数值数据类型的 ZEROFILL 属性，并将在未来的 MySQL 版本中移除它。可考虑使用替代方法实现同样的效果。例如，在程序中使用 LPAD() 函数将数字 0 填充至所需宽度，或存储格式化后的数值到 CHAR 类型的列中。
数值数据类型即允许 UNSIGNED 属性，也允许 SIGNED 属性。然而，SIGNED 是默认值，因此 SIGNED 并没有什么用。
从 MySQL 8.0.17 开始，不建议数据类型为 FLOAT、DOUBLE 和 DECIMAL（及它们的代名词）的列使用 UNSIGNED 属性，并将在未来的 MySQL 版本中移除它。可考虑对这些列使用简单的 CHECK 进行约束。 （译者注：UNSIGNED 并不影响它们的取值范围，而是表明该列不接受负数）
SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。
在整型列的定义中， SERIAL DEFAULT VALUE 是 NOT NULL AUTO_INCREMENT UNIQUE 的别名。
注意：当两个整型进行减法运算，且其中一个是 UNSIGNED，则结果是无符号的（unsigned），除非启用 SQL 的 NO_UNSIGNED_SUBTRACTION 模式。详情请看 Section 12.10, “Cast Functions and Operators”。
BIT[(M)] bit 值类型。
M表示值含有 bit 的数量，取值范围为 [1, 64]，默认值为 1（M缺省时）。TINYINT[(M)] [UNSIGNED] [ZEROFILL] 微小整型。有符号的取值范围是 [-128, 127]，无符号的取值范围是 [0, 255]。
BOOL, BOOLEAN 是 TINYINT(1) 的代名词。值为 0 时表示 false，非零时表示 true：
然而，TRUE 和 FALSE 值仅仅分别是 1 和 0 的别名，如下所示：
倒数两个语句的执行结果表明，2 既不等于 1 也不等于 0。
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 小整型。有符号的取值范围是 [-32768, 32767]，无符号的取值范围是 [0, 65535]。
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 中整型。有符号的取值范围是 [-8388608, 8388607]，无符号的取值范围是 [0, 16777215]。
INT[(M)] [UNSIGNED] [ZEROFILL] 常规尺寸的整型。有符号的取值范围是 [-2147483648, 2147483647]，无符号的取值范围是 [0, 4294967295]。
INTEGER[(M)] [UNSIGNED] [ZEROFILL] INT 的代名词。
BIGINT[(M)] [UNSIGNED] [ZEROFILL] 大整型。有符号的取值范围是 [-9223372036854775808, 9223372036854775807]，无符号的取值范围是 [0, 18446744073709551615]。
SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。
关于 BIGINT 列，需要注意以下几点：
所有算术运算都是使用带符号（signed）的 BIGINT 或 DOUBLE 值完成。因此，除位函数（bit function）外，不要使用大于 9223372036854775807（63 bit）的无符号大整型（big integer）！如果这样做，会因为将 BITINT 值转换为 DOUBLE 时的四舍五入错误，导致结果中的最后几位可能出错。
在以下情况下，MySQL 可处理 BIGINT：
向 BIGINT 列存储较大的无符号整数。
在 MIN(col_name) 或 MAX(col_name) 中，其中 col_name 是引用 BIGINT 列。
当使用操作符（+、-、* 等）时，两个操作数均为整型。
你始终可以通过使用字符串将精确的整型值存储在 BIGINT 列中。在这种情况下，MySQL 会执行 string-to-number 的转换，期间不涉及双精度表示形式。
当两个操作数均为整数值时，+、-、* 操作符会使用 BIGINT 进行算术运算。这意味着，如果将两个较大的整数相乘（或函数返回的整数），则当结果大于 9223372036854775807 时，会得到意想不到的结果。
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] 经过包装的“精确”定点数。
M表示数字的总个数（the precision），D表示小数点后数字的个数（the scale）。其中，M不包含小数点和负号 -（对于负数）。如果D为 0，则值没有小数点（即小数部分）。M（即数字个数）的最大值为 65，D（即小数部分的数字个数）的最大值是 30。D的默认值为 0（缺省时），M的默认值是 10（缺省时）。UNSIGNED 属性表示不接受负值。从 MySQL 8.0.17 开始，不建议 DECIMAL（及其代名词）数据类型的列使用 UNSIGNED 属性，并将在未来的 MySQL 版本中移除它。可考虑对这些列使用简单的 CHECK 进行约束。
DECIMAL 列的所有基础运算（+、-、*、/）均以 65 位数字的精度完成。
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] 这些类型均是 DECIMAL 的代名词。其中 FIXED 代名词与其他数据库系统兼容。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 单精度浮点数，其取值范围是 [-3.402823466E+38, -1.175494351E-38]、0、[1.175494351E-38, 3.402823466E+38]。这些是基于 IEEE 标准的理论极限。实际范围可能会略微缩小，这具体取决于你的硬件或操作系统。
M表示数字总个数，D表示小数点后的数字个数。若M和D都缺省，则会将值存储为硬件允许的极限。单精度浮点数的精度约为小数点后 7 位。FLOAT(M, D) 是非标准的 MySQL 扩展。从 MySQL 8.0.17 开始，不建议使用该句法，并将在未来的 MySQL 版本中移除它。
UNSIGNED 属性表示不接受负值。从 MySQL 8.0.17 开始，不建议 FLOAT（及其代名词）数据类型的列使用 UNSIGNED 属性，并将在未来的 MySQL 版本中移除它。可考虑对这些列使用简单的 CHECK 进行约束。
使用 FLOAT 可能会带来意想不到的问题，因为在 MySQL 中所有计算均以双精度完成的。详情请看 Section B.4.4.7, “Solving Problems with No Matching Rows”。
FLOAT(p) [UNSIGNED] [ZEROFILL] 浮点数。
p表示以 bit 为单位的精度，但 MySQL 仅用此值来确定对最终数据类型是 FLOAT 还是 DOUBLE。若p值位于 [0, 24]，则数据类型实际为不带M和D的 FLOAT 类型；若p值位于 [25, 53]，则数据类型实际为不带M和D的 DOUBLE 类型。列的最终取值范围与本节前面提到的单精度 FLOAT 和双精度 DOUBLE 数据类型一致。UNSIGNED 属性表示不接受负值。从 MySQL 8.0.17 开始，不建议 FLOAT（及其代名词） 数据类型的列使用 UNSIGNED 属性，并将在未来的 MySQL 版本中移除它。可考虑对这些列使用简单的 CHECK 进行约束。
提供 FLOAT(p) 句法是为了兼容 ODBC。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 正常大小的（双精度）浮点数。其取值范围是 [-1.7976931348623157E+308, -2.2250738585072014E-308]、0、[2.2250738585072014E-308, 1.7976931348623157E+308]。这些是基于 IEEE 标准的理论极限。实际范围可能会略微缩小，这具体取决于你的硬件或操作系统。
M表示总数字个数，D表示小数点后的数字个数。若M和D都缺省，则会将值存储为硬件允许的极限。双精度浮点数的精度约为小数点后 15 位。DOUBLE(M, D) 是非标准的 MySQL 扩展。从 MySQL 8.0.17 开始，不建议使用该句法，并将在未来的 MySQL 版本中移除它。
UNSIGNED 属性表示不接受负值。从 MySQL 8.0.17 开始，不建议 DOUBLE（及其代名词） 数据类型的列使用 UNSIGNED 属性，并将在未来的 MySQL 版本中移除它。可考虑对这些列使用简单的 CHECK 进行约束。
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL] 两者都是 DOUBLE 的代名词。例外：如果启用 SQL 的 REAL_AS_FLOAT 模式，那么 REAL 是 FLOAT 的代名词，而不是 DOUBLE 的代名词。
整型类型（精确值）- INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
MySQL 支持 SQL 标准的整型类型：INTEGER（或 INT）和 SMALLINT。作为标准的扩展，MySQL 也支持这些整型类型：TINYINT、MEDIUMINT 和 BIGINT。以下表格展示了每种整型类型所需的存储空间和取值范围。
Table 11.1 Required Storage and Range for Integer Types Supported by MySQL
Type
Storage (Bytes)
Minimum Value Signed
Maximum Value Signed
Minimum Value Unsigned
Maximum Value Unsigned
TINYINT
1
-128
127
0
255
SMALLINT
2
-32768
32767
0
65535
MEDIUMINT
3
-8388608
8388607
0
16777215
INT
4
-2147483648
2147483647
0
4294967295
BIGINT
8
-2^63
2^63-1
0
2^64-1
定点型类型（精确值）- DECIMAL, NUMERIC
DECIMAL 和 NUMERIC 类型能存储精确的数值数据。这些类型适用于重视精确度的需求，如货币数据。在 MySQL 中，NUMERIC 被实现为 DECIMAL，因此以下有关 DECIMAL 的说明同样适用于 NUMERIC。
MySQL 以二进制格式存储 DECIMAL。详情请看 See Section 12.25, “Precision Math”。
在 DECIMAL 列的声明中，通常都会指定 M（the precision）和 D（the scale）。如：
以上案例，M 是 5，D 是 2。M 表示可存储多少个有效数字，D 表示可存储小数点后多少个数字。
SQL 标准要求 DECIMAL(5, 2) 能存储任何由 5 个数字（其中 2 个是小数）组成的数值。因此，salary 列的存储范围是 [-999.99, 999.99]。
在 SQL 标准中，DECIMAL(M) 等同于 DECIMAL(M, 0)。类似地，DECIMAL 等同于 DECIMAL(M, 0)，其具体表现则取决于 M 值。MySQL 支持以上 DECIMAL 的两种不同形式，且 M 的默认值为 10。
若 D 为 0，则 DECIMAL 值不包含小数点（或小数部分）。
DECIMAL 支持最多 65 个数字，但 DECIMAL 列的实际存储范围受 M 和 D 的约束。当值的小数部分的数字个数大于指定的 D，则会将小数部分的数字个数限制为 D 个。（这种明确的行为是特定于操作系统，但通常的处理方式为裁剪至 D 长度。【译者注：FLOAT 等近似值数据类型一般采取四舍五入】）
浮点型类型（近似值）- FLOAT, DOUBLE
FLOAT 和 DOUBLE 类型表示近似值。MySQL 用 4 字节存储单精度值，用 8 字节存储双精度值。
对于 FLOAT，SQL 标准允许 FLOAT 关键字后的小括号() 内指定一个可选的、以 bit 为单位的精度参数—— FLOAT(p)。MySQL 也支持此可选精度的语法，但该精度参数仅用于确定存储大小。当 p 位于 [0, 23]，则该列是 4 字节的单精度 FLOAT 列；当 p 位于 [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 版本中移除它们。
由于浮点数是近似值，不能作为精确值存储，当将它们视为精确值进行比较时，可能会出现问题。另外，它们还受平台或实现依赖的约束。详情请看 Section B.4.4.8, “Problems with Floating-Point Values”。
为了获得最大可移植性，存储近似值数据时，应使用无 M 和 D 的 FLOAT 或 DOUBLE PRECISION 类型。
Bit 值类型 - BIT
BIT 数据类型用于存储 bit 值。BIT(M) 表示可允许存储 M-bit 大小的值。M 的取值范围是 [1, 64]。
要指定 bit 值，可使用 b'value' 表示法。value 是由 0 和 1 组成的二进制值。如：b'111' 和 b'10000000' 分别表示 7 和 128。详情请看 Section 9.1.5, “Bit-Value Literals”。
如果指定的值的长度小于 BIT(M) 列要求的 M 位，则该值会在左侧填充 0。例如，向 BIT(6) 列指定 b'101' 值，实际相当于指定 b'000101'。
NBD 集群。一个 NDB 表中所有 BIT 列的总大小不能超过 4096 bit。
数值类型的属性
MySQL 支持在整型数据类型后的括号内指定一个可选的显示宽度属性。如：INT(4) 指定 INT 列的显示宽度为 4 个数字。应用程序可使用此可选的显示宽度来显示整型数据，当该数值的宽度小于指定宽度时，会在其左侧填充空格。（也就是说，此宽度存在于返回结果集的元数据中，是否使用该宽度则取决于应用程序。）
显示宽度不会限制值的存储范围，也不会影响比显示宽度长的值的正确显示。例如，数据类型为 SMALLINT(3) 的列，通常其存储范围是 [-32768, 32767]，对于大于显示宽度的值也能显示完整。
当同时使用可选（非标准）的 ZEROFILL 属性时，填充左侧的空格将会被替换为 0。例如，数据类型为 INT(4) ZEROFILL 的列，检索值 5 时会得到 0005。
注意：对于表达式或 UNION 查询中涉及的列，会忽略 ZEROFILL 属性。
如果将大于显示宽度的值存储在具有 ZEROFILL 属性的整型列中，则当 MySQL 为某些复杂的联结（join）生成临时表时，你可能会遇到问题。在这种情况下，MySQL 会假定值已满足列的显示宽度。
从 MySQL 8.0.17 开始，不建议数值数据类型使用 ZEROFILL 属性，对于整型数据类型，则不建议使用显示宽度属性，并将在未来的 MySQL 版本中移除整型数据类型的 ZEROFILL 和显示宽度属性。可考虑使用替代方法实现同样的效果。例如，在程序中使用 LPAD() 函数将数字 0 填充至所需宽度，或存储格式化后的数值到 CHAR 类型的列中。
所有整型数据类型均拥有可选的（非标准）UNSIGNED 属性。一个 unsigned 类型可实现非负数的存储，或者你希望拥有更大的数值范围。例如，如果 INT 列是 UNSIGNED 的，虽然范围的长度不变，但端点是往正数方向移动，即从 [-2147483648, 2147483647] 变到 [0, 4294967295]。
浮点型和定点型也可使用 UNSIGNED 属性，和整型一样，unsigned 属性能实现非负数的存储。但与整型不一样的地方是，它并不能改变取值范围，即保持不变。从 MySQL 8.0.17 开始，不建议 FLOAT、DOUBLE 和 DECIMAL（及其代名词） 数据类型的列使用 UNSIGNED 属性，并将在未来的 MySQL 版本中移除它。可考虑对这些列使用简单的 CHECK 进行约束。
若为数值类型的列指定 ZEROFILL，MySQL 则自动为其添加 UNSIGNED 属性。
整型和浮点型数据类型的列可使用 AUTO_INCREMENT 属性。当你向 AUTO_INCREMENT 索引列插入 NULL 值时，该列会将其设为下一个序列值。通常为 value+1，其中 value 是列目前的最大值。（AUTO_INCREMENT 序列从 1 开始。）
向 AUTO_INCREMENT 列存储 0 时，效果和存储 NULL 一样，除非启用 SQL 的 NO_AUTO_VALUE_ON_ZERO 模式。
插入 NULL 以生成 AUTO_INCREMENT 值的前提是该列已声明为 NOT NULL。如果该列声明为 NULL，那么在插入 NULL 时就会存为 NULL。当向 AUTO_INCREMENT 列插入其他值，那么就会直接存储该值，并重置序列值，以使得下次能自动生成以该插入值为起始值的序列值。
AUTO_INCREMENT 列不支持负值。
CHECK 约束不能引用具有 AUTO_INCREMENT 属性的列，也不能将 AUTO_INCREMENT 属性添加到 已使用 CHECK 约束的列。
从 MySQL 8.0.17 开始，不建议为 FLOAT、DOUBLE 列使用 AUTO_INCREMENT，并将在未来的 MySQL 版本中移除它。可考虑移除这些列的 AUTO_INCREMENT 属性，或将它们转为整型。
超出取值范围和溢出处理
当 MySQL 向数值类型的列存储超出列数据类型允许范围的值时，处理结果取决于当时的 SQL 模式。
如果启用 SQL 严格模式，MySQL 则按照 SQL 标准处理——以报错的方式拒绝超出范围的值，即插入失败。
如果未启用任何限制模式，MySQL 会将值裁剪为列数据类型取值范围内的相应端点，并作为结果值存储。
当为整型列插入一个超出范围的值时，MySQL 会存储为列数据类型取值范围内的相应端点。
当为浮点型和定点型列插入一个超过指定（或默认）
M和D所表示的范围时，MySQL 会存储该取值范围内的相应端点的值。
假设表 t1 定义如下：
在启用 SQL 严格模式下，会报超出范围的错误：
当未启用 SQL 严格模式，会进行裁剪并产生警告（warning）：
如果未启用 SQL 严格模式，列赋值转换（column-assignment conversion）会因裁剪而发生，并会产生警告（warning），以上这些会发生在 ALTER TABLE、LOAD DATA、UPDATE 和多行 INSERT 语句中。在严格模式下，这些语句均会执行失败，并且某些、甚至所有值都不会被插入或更改，这取决于该表是否为事务表或其他一些因素。详情请看 Section 5.1.11, “Server SQL Modes”。
数值表达式求值期间可能会产生溢出而导致错误。例如，有符号 BIGINT 的最大值是 9223372036854775807，则以下表达式会报错：
要想以上表达式顺利运行，需要将值转为无符号：
是否发生溢出是取决于操作数的取值范围，因此处理上面表达式的另一种方法是使用精确算术法，因为 DECIMAL 的取值范围比整型大。
在整型的减法中，当其中一个操作数是 UNSIGNED，则默认会产生一个 UNSIGNED 结果值。如果结果是负数值，则会报错：
如果启用 SQL 的 NO_UNSIGNED_SUBTRACTION 模式，则结果为负数：
如果将这个表达式的结果值用于更新一个 UNSIGNED 整型列，则会被裁剪为该列数据类型的取值范围的最大值，或当启用 NO_UNSIGNED_SUBTRACTION 时，会裁剪为 0。如果开启 SQL 严格模式，则会报错，且该列的值保持不变。
最后更新于
这有帮助吗？