MySQL :: MySQL 8.4 参考手册 :: 13.1.1 数字数据类型语法

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 13 种数据类型 / 13.1 数字数据类型 / 13.1.1 数字数据类型语法

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 98%

13.1.1 数值数据类型语法13.1.1 Numeric Data Type Syntax

对于整数数据类型，M 表示最小显示宽度。最大显示宽度为 255。显示宽度与类型可以存储的值的范围无关，如第 13.1.6 节，“数值类型属性” 所述。For integer data types, M indicates the minimum display width. The maximum display width is 255. Display width is unrelated to the range of values a type can store, as described in Section 13.1.6, “Numeric Type Attributes”.

对于浮点和定点数据类型，M 是可以存储的总位数。For floating-point and fixed-point data types, M is the total number of digits that can be stored.

整数数据类型的显示宽度属性已被弃用；您应该预期在未来版本的 MySQL 中将不再支持它。The display width attribute is deprecated for integer data types; you should expect support for it to be removed in a future version of MySQL.

如果为数字列指定 ZEROFILL，MySQL 会自动为该列添加 UNSIGNED 属性。If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

数字数据类型的 ZEROFILL 属性已被弃用；您应预期它在 MySQL 的未来版本中将被移除。请考虑使用替代方法来实现该属性的效果。例如，应用程序可以使用 LPAD() 函数将数字补零到所需宽度，或者可以将格式化后的数字存储在 CHAR 列中。The ZEROFILL attribute is deprecated for numeric data types; you should expect support for it to be removed in a future version of MySQL. Consider using an alternative means of producing the effect of this attribute. For example, applications could use the LPAD() function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR columns.

允许 UNSIGNED 属性的数值数据类型也允许 SIGNED。然而，这些数据类型默认是有符号的，因此 SIGNED 属性没有效果。Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.

UNSIGNED 属性在 FLOAT、DOUBLE 和 DECIMAL（及任何同义词类型）的列中已弃用；您应该预期在未来的 MySQL 版本中将不再支持它。对于此类列，考虑使用简单的 CHECK 约束。The UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms); you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.

SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

SERIAL DEFAULT VALUE 在整数列定义中是 NOT NULL AUTO_INCREMENT UNIQUE 的别名。SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

Warning

当你对整数值进行减法运算且其中一个值是 UNSIGNED 类型时，结果将是无符号，除非启用了 NO_UNSIGNED_SUBTRACTION SQL 模式。参见第 14.10 节，“类型转换函数和操作符”。When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled. See Section 14.10, “Cast Functions and Operators”.

BIT[(M)]
一种位值类型。M 表示每个值的位数，范围从 1 到 64。如果省略 M，默认值为 1。A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
TINYINT[(M)] [UNSIGNED] [ZEROFILL] TINYINT[(M)] [UNSIGNED] [ZEROFILL]
一个非常小的整数。带符号的范围是 -128 到 127。无符号的范围是 0 到 255。A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

BOOL, BOOLEAN BOOL, BOOLEAN

这些类型是 TINYINT(1) 的同义词。值为零被认为是 false。非零值被认为是 true：These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

然而，值 TRUE 和 FALSE 仅仅是 1 和 0 的别名，如下所示：However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

最后两条语句显示上述结果，因为 2 既不等于 1 也不等于 0。The last two statements display the results shown because 2 is equal to neither 1 nor 0.

SMALLINT[(M)] [UNSIGNED] [ZEROFILL] SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
小整数。带符号范围为 -32768 到 32767。无符号范围为 0 到 65535。A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
中等大小的整数。带符号的范围是 -8388608 到 8388607。无符号的范围是 0 到 16777215。A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL] INT[(M)] [UNSIGNED] [ZEROFILL]
一个普通大小的整数。带符号范围是 -2147483648 到 2147483647。无符号范围是 0 到 4294967295。A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL] INTEGER[(M)] [UNSIGNED] [ZEROFILL]
这个类型是 INT 的同义词。This type is a synonym for INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL] BIGINT[(M)] [UNSIGNED] [ZEROFILL]
一个大整数。带符号范围是 -9223372036854775808 到 9223372036854775807。无符号范围是 0 到 18446744073709551615。A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
关于 BIGINT 列时，你应该注意的一些事项：Some things you should be aware of with respect to BIGINT columns:
- 所有算术运算都使用有符号的 BIGINT 或 DOUBLE 值进行，因此除非使用位函数，否则不应使用大于 9223372036854775807（63 位）的无符号大整数！如果这样做，结果的某些最后几位可能由于将 BIGINT 值转换为 DOUBLE 时可能出现的舍入错误而不正确。 All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE.
  MySQL 可以在以下情况下处理 BIGINT：MySQL can handle BIGINT in the following cases:
  - 当使用整数在 BIGINT 列中存储大无符号值时。When using integers to store large unsigned values in a BIGINT column.
  - 在 MIN(col_name) 或 MAX(col_name) 中，其中 col_name 指的是一个 BIGINT 列。In MIN(col_name) or MAX(col_name), where col_name refers to a BIGINT column.
  - 使用操作符（+、-、* 等）时，如果两个操作数都是整数。When using operators (+, -, *, and so on) where both operands are integers.
- 您总是可以通过使用字符串将精确整数值存储在BIGINT列中。在这种情况下，MySQL 执行一个字符串到数字的转换，而不涉及中间的双精度表示。You can always store an exact integer value in a BIGINT column by storing it using a string. In this case, MySQL performs a string-to-number conversion that involves no intermediate double-precision representation.
- -、+ 和 * 运算符在两个操作数都是整数值时使用 BIGINT 算术。这意味着如果你对两个大整数（或返回整数的函数结果）进行乘法操作，当结果大于 9223372036854775807 时，可能会得到意外的结果。The -, +, and * operators use BIGINT arithmetic when both operands are integer values. This means that if you multiply two big integers (or results from functions that return integers), you may get unexpected results when the result is larger than 9223372036854775807.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
一个打包的“精确”定点数。M 是数字总数（精度），D 是小数点后的数字数量（标度）。小数点以及（对于负数）- 符号不计入 M。如果 D 为 0，则值没有小数点或小数部分。DECIMAL 的最大数字数（M）是 65。支持的最大小数位数（D）是 30。如果省略 D，默认值为 0。如果省略 M，默认值为 10。（DECIMAL 字面量文本的长度也有限制；参见第 14.24.3 节，“表达式处理”。）A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10. (There is also a limit on how long the text of DECIMAL literals can be; see Section 14.24.3, “Expression Handling”.)
UNSIGNED，如果指定，则不允许负值。对于类型为 DECIMAL（及其任何同义词）的列，UNSIGNED 属性已被弃用；你应预期在 MySQL 的未来版本中将移除对此的支持。考虑对这样的列使用简单的 CHECK 约束。UNSIGNED, if specified, disallows negative values. The UNSIGNED attribute is deprecated for columns of type DECIMAL (and any synonyms); you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
所有与 DECIMAL 列进行的基本计算（+, -, *, /）都以 65 位数字的精度完成。All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
这些类型是DECIMAL的同义词。FIXED同义词可用于与其他数据库系统的兼容。These types are synonyms for DECIMAL. The FIXED synonym is available for compatibility with other database systems.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
一个小的（单精度）浮点数。允许的值范围是 -3.402823466E+38 到 -1.175494351E-38，0，以及 1.175494351E-38 到 3.402823466E+38。这些是基于 IEEE 标准的理论极限。实际范围可能会根据您的硬件或操作系统略有不同。A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M 是总位数，D 是小数点后的位数。如果省略 M 和 D，则值将存储为硬件允许的极限。单精度浮点数大约精确到 7 位小数。M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
FLOAT(M,D) 是一个非标准的 MySQL 扩展。这种语法已被弃用，并且你应预期在 MySQL 的未来版本中将不再支持它。FLOAT(M,D) is a nonstandard MySQL extension. This syntax is deprecated, and you should expect support for it to be removed in a future version of MySQL.
UNSIGNED，如果指定，将不允许负值。对于类型为 FLOAT（及其任何同义词）的列，UNSIGNED 属性已被弃用，并且你应预期在 MySQL 的未来版本中将不再支持它。对于此类列，建议改用简单的 CHECK 约束。UNSIGNED, if specified, disallows negative values. The UNSIGNED attribute is deprecated for columns of type FLOAT (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
使用 FLOAT 可能会带来一些意想不到的问题，因为 MySQL 中的所有计算都是使用双精度进行的。参见第 B.3.4.7 节，“解决没有匹配行的问题”。Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. See Section B.3.4.7, “Solving Problems with No Matching Rows”.
FLOAT(p) [UNSIGNED] [ZEROFILL] FLOAT(p) [UNSIGNED] [ZEROFILL]
一个浮点数。p表示精度（以位为单位），但 MySQL 仅使用该值来确定结果数据类型是使用FLOAT还是DOUBLE。如果p的值在 0 到 24 之间，数据类型将变为FLOAT，不带M或D值。如果p的值在 25 到 53 之间，数据类型将变为DOUBLE，不带M或D值。生成列的范围与本节前面描述的单精度FLOAT或双精度DOUBLE数据类型相同。A floating-point number. p represents the precision in bits, but MySQL uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT with no M or D values. If p is from 25 to 53, the data type becomes DOUBLE with no M or D values. The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.
UNSIGNED，如果指定，将不允许负值。对于类型为 FLOAT（及其任何同义词）的列，UNSIGNED 属性已被弃用，并且你应预期在 MySQL 的未来版本中将不再支持它。对于此类列，建议改用简单的 CHECK 约束。UNSIGNED, if specified, disallows negative values. The UNSIGNED attribute is deprecated for columns of type FLOAT (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
FLOAT(p) 语法用于 ODBC 兼容性。 FLOAT(p) syntax is provided for ODBC compatibility.
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
一个普通大小（双精度）的浮点数。允许的值范围是 -1.7976931348623157E+308 到 -2.2250738585072014E-308，0，以及 2.2250738585072014E-308 到 1.7976931348623157E+308。这些是基于 IEEE 标准的理论极限。实际范围可能会根据您的硬件或操作系统略小。A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M 是总位数，D 是小数点后的位数。如果省略 M 和 D，则值将存储到硬件允许的极限。双精度浮点数的精度大约为 15 位小数。M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
DOUBLE(M,D) 是一个非标准的 MySQL 扩展；并且已被弃用。您应该预期在 MySQL 的未来版本中将移除此语法的支持。DOUBLE(M,D) is a nonstandard MySQL extension; and is deprecated. You should expect support for this syntax to be removed in a future version of MySQL.
UNSIGNED，如果指定，则不允许负值。对于类型为 DOUBLE（及其任何同义词）的列，UNSIGNED 属性已被弃用，您应预期在 MySQL 的未来版本中将移除此支持。对于此类列，可以考虑改用简单的 CHECK 约束。UNSIGNED, if specified, disallows negative values. The UNSIGNED attribute is deprecated for columns of type DOUBLE (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL] DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
这些类型是 DOUBLE 的同义词。例外情况：如果启用了 REAL_AS_FLOAT SQL 模式，REAL 是 FLOAT 的同义词，而不是 DOUBLE。These types are synonyms for DOUBLE. Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.

首页上一级下一节