参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

M表示整数类型的最大显示宽度。最大显示宽度为255.显示宽度与类型可以包含的值范围无关，如第11.2节“数字类型”中所述。对于浮点型和定点型，M是可以存储的总位数。

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

允许UNSIGNED属性的数字数据类型也允许SIGNED。但是，这些数据类型是默认带符号的，所以SIGNED属性不起作用。

SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名。

整数列的定义中的SERIAL DEFAULT VALUE是NOT NULL AUTO_INCREMENT UNIQUE的别名。

警告：

在UNSIGNED类型的整数值之间使用减法时，除非启用了NO_UNSIGNED_SUBTRACTION SQL模式，否则结果是无符号的。

BIT[(M)]

一个bit-value 类型。 M表示每个值的位数，从1到64.如果省略M，则缺省值为1。

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

一个非常小的整数。有符号范围是-128到127.无符号范围是0到255。

BOOL,BOOLEAN

这些类型是TINYINT（1）的同义词。零值被认为是false。非零值被认为是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的别名，如下所示：

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。

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列：

所有算法都使用带符号的BIGINT或DOUBLE值完成，所以除了位功能外，不应使用大于9223372036854775807（63位）的无符号大整数！如果这样做，则在将BIGINT值转换为DOUBLE时，由于舍入误差，结果中的某些最后数字可能会出错。在以下情况下，MySQL可以处理BIGINT：

使用整数将大的无符号值存储在BIGINT列中时。
在MIN（col_name）或MAX（col_name）中，其中col_name引用BIGINT列。
当使用两个操作数都是整数的运算符（+， - ，*等）时。

您可以始终使用字符串存储精确的整数值到BIGINT列中。在这种情况下，MySQL执行一个不涉及中间双精度表示的字符串到数字的转换。
当两个操作数都是整数值时， - ，+和*操作符使用BIGINT算术。这意味着如果你乘两个大整数（或者返回整数的函数的结果），当结果大于9223372036854775807时，你可能会得到意想不到的结果。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

精确的“定点”数值。 M是总位数（精度），D是小数点后的位数（标度）。小数点和（对于负数） - 符号不计入M.如果D为0，则值不包含小数点或小数部分。 DECIMAL的最大位数（M）为65.支持的最大小数位数（D）为30.如果省略D，则缺省值为0.如果省略M，则缺省值为10。

如果指定UNSIGNED，则不允许负值。

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标准的理论极限。根据您的硬件或操作系统，实际范围可能会稍小。

如果指定UNSIGNED，则不允许负值。

使用FLOAT可能会给你一些意想不到的问题，因为MySQL中的所有计算都是以双精度完成的。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

一个正常大小的（双精度）浮点数。允许值为-1.7976931348623157E + 308到-2.2250738585072014E-308,0和2.2250738585072014E-308到1.7976931348623157E + 308。这些是基于IEEE标准的理论极限。根据您的硬件或操作系统，实际范围可能会稍小。

M是总位数，D是小数点后的位数。如果省略M和D，则将值存储到硬件允许的限制内。双精度浮点数精确到小数点后15位。

如果指定，UNSIGNED不允许负值。

DOUBLE PRECISION [（M，D）] [UNSIGNED] [ZEROFILL]，REAL [（M，D）] [UNSIGNED] [ZEROFILL]

这些类型是DOUBLE的同义词。异常：如果启用REAL_AS_FLOAT SQL模式，则REAL是FLOAT的同义词而不是DOUBLE。

FLOAT（p）[UNSIGNED] [ZEROFILL]

一个浮点数。 p表示以位为单位的精度，但MySQL仅使用此值来确定是否对结果数据类型使用FLOAT或DOUBLE。如果p从0到24，则数据类型将变为FLOAT，而不包含M或D值。如果p从25到53，则数据类型将变为DOUBLE，而不包含M或D值。结果列的范围与本节前面所述的单精度FLOAT或双精度DOUBLE数据类型相同。

FLOAT（p）语法提供了ODBC兼容性。

MySQL支持所有标准的SQL数字数据类型。这些类型包括精确的数字数据类型（INTEGER，SMALLINT，DECIMAL和NUMERIC）以及近似的数字数据类型（FLOAT，REAL和DOUBLE PRECISION）。关键字INT是INTEGER的同义词，关键字DEC和FIXED是DECIMAL的同义词。 MySQL将DOUBLE作为DOUBLE PRECISION（非标准扩展）的同义词。除非启用了REAL_AS_FLOAT SQL模式，否则MySQL还将REAL视为DOUBLE PRECISION（非标准变体）的同义词。

BIT数据类型存储位值，并支持MyISAM，MEMORY，InnoDB和NDB表。

11.2.1 整数类型（精确值）

-INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

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

11.2.2 定点类型（准确值） - DECIMAL，NUMERIC

DECIMAL和NUMERIC类型存储精确的数字数据值。使用这些类型时，重要的是要保留精确的精度，例如使用货币数据。在MySQL中，NUMERIC被实现为DECIMAL，所以关于DECIMAL的以下remark同样适用于NUMERIC。

在DECIMAL列声明中，精度和比例可以是（通常是）指定的; 例如：

salary DECIMAL(5,2)

在这个例子中，5是精度，2是标度。精度表示为值存储的有效数字的数量，标度表示可以存储在小数点后面的数字的数量。

--也就是5个有效数据，保留2位小数

标准SQL要求DECIMAL（5,2）能够存储具有五位数和两位小数的任何值，因此可以存储在工资列中的值范围为-999.99至999.99。

在标准SQL中，语法DECIMAL（M）等价于DECIMAL（M，0）。同样，DECIMAL语法等价于DECIMAL（M，0），其中实现由M的值决定.MySQL同时支持DECIMAL语法的这些变体形式。 M的默认值是10。

如果标度为0，则DECIMAL值不包含小数点或小数部分。

DECIMAL的最大位数是65，但给定DECIMAL列的实际范围可以受给定列的精度或比例限制。当这样的列被赋予一个小数点后的数字超过指定标度尺允许的数值时，该值将被转换为该标度。（精确的行为是特定于操作系统的，但通常效果会截断到允许的位数。）

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

FLOAT和DOUBLE类型表示近似的数值数据值。 MySQL对于单精度值使用四个字节，对于双精度值使用八个字节。

对于FLOAT，SQL标准允许在括号内的关键字FLOAT后面的位中选择指定精度（但不包括指数的范围）。 MySQL也支持这个可选的精度规格，但精度值仅用于确定存储大小。从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。

因为浮点值是近似值而不是作为精确值存储的，所以将它们作为精确比较对待可能会导致问题。他们也受到平台或实施依赖关系的影响。

为了获得最大的可移植性，需要存储近似数值数据值的代码应该使用FLOAT或DOUBLE PRECISION，而不指定精度或位数。

11.2.4 位-值类型 -BIT

BIT数据类型用于存储位值。 BIT（M）可以存储M-byte值。 M可以从1到64。

要指定位值，可以使用b'值'符号。值是使用零和1写入的二进制值。例如，b'111'和b'10000000'分别代表7和128。

如果将值分配给长度小于M位的BIT（M）列，则该值左侧用零填充。例如，给BIT（6）列赋值b'101'实际上与赋值b'000101'相同。

NDB群集。给定NDB表中使用的所有BIT列的最大组合大小不得超过4096位。

11.2.5 数值类型属性

MySQL支持一个扩展，用于在类型的基本关键字后面的括号中指定整型数据类型的显示宽度。例如，INT（4）指定一个显示宽度为四位的INT。这个可选的显示宽度可以被应用程序用来显示宽度小于为列指定的宽度的整数值，方法是用空格填充它们。（也就是说，这个宽度出现在结果集返回的元数据中，它是否被使用取决于应用程序。）

显示宽度不限制可存储在列中的值的范围。它也不会阻止比列显示宽度更宽的值被正确显示。例如，指定为SMALLINT（3）的列的常用SMALLINT范围为-32768至32767，并且三位数所允许的范围之外的值全部使用三位以上的数字显示。

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

注意：

当一个列被表达式或者union查询涉及时，ZEROFILL属性将被忽略。

如果将值大于显示宽度的值存储在具有ZEROFILL属性的整数列中，则当MySQL为某些复杂联接生成临时表时，可能会遇到问题。在这些情况下，MySQL假定数据值符合列显示宽度。

当表达式或UNION查询中涉及列时，ZEROFILL属性将被忽略。

如果将值大于显示宽度的值存储在具有ZEROFILL属性的整数列中，则当MySQL为某些复杂联接生成临时表时，可能会遇到问题。在这些情况下，MySQL假定数据值符合列显示宽度。

所有整型都可以有一个可选的（非标准）属性UNSIGNED。无符号类型可用于只允许列中的非负数，或者当您需要列的较大数值范围时。例如，如果INT列是UNSIGNED，则列的范围大小相同，但其端点从-2147483648和2147483647转变为0和4294967295。

浮点和定点类型也可以是UNSIGNED。与整数类型一样，此属性可防止将负值存储在列中。与整数类型不同，列值的上限范围保持不变。

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

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

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

插入NULL以生成AUTO_INCREMENT值需要将该列声明为NOT NULL。如果该列被声明为NULL，则插入NULL将存储NULL。将任何其他值插入AUTO_INCREMENT列时，该列将设置为该值，并重置序列，以便下一个自动生成的值按照插入的值顺序执行。

在MySQL 5.7中，不支持AUTO_INCREMENT列的负值。

11.2.6 超出范围和越界处理

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

如果启用了严格SQL模式，则MySQL会根据SQL标准拒绝超出范围的值并发生错误，并且插入失败。
如果未启用限制模式，则MySQL将该值剪切到列数据类型范围的相应端点，并存储结果值。

当超出范围的值分配给整数列时，MySQL将存储表示列数据类型范围的相应端点的值

当为浮点或定点列指定一个超出指定（或默认）精度和标度所暗示范围的值时，MySQL会存储代表该范围相应端点的值。

假设T1有下列定义：

CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);

如果启用严格的SQL模式，则会发生超出范围的错误：

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模式未启用的情况下，将发生带有警告的裁剪：

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模式，则由于剪辑而发生的列分配转换会报告为ALTER TABLE，LOAD DATA INFILE，UPDATE和多行INSERT语句的警告。在严格模式下，这些语句失败，并且部分或全部值不会被插入或更改，具体取决于表是否是事务表和其他因素。

在数值表达式评估期间溢出会导致错误。例如，最大的带符号BIGINT值是9223372036854775807，所以下面的表达式会产生一个错误：

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

要在这种情况下使操作成功，请将该值转换为无符号;

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;

+-------------------------------------------+

| CAST(9223372036854775807 AS UNSIGNED) + 1 |

+-------------------------------------------+

| 9223372036854775808 |

+-------------------------------------------+

是否发生溢出取决于操作数的范围，因此处理上述表达式的另一种方式是使用精确值算术，因为DECIMAL值的范围比整数大：

mysql> SELECT 9223372036854775807.0 + 1;

+---------------------------+

| 9223372036854775807.0 + 1 |

+---------------------------+

| 9223372036854775808.0 |

+---------------------------+

整数值之间的减法，其中一个是UNSIGNED类型，默认情况下会生成无符号结果。如果结果否则会导致错误：

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)'

如果NO_UNSIGNED_SUBTRACTION SQL模式被启用，结果是否定：

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

mysql> SELECT CAST(0 AS UNSIGNED) - 1;

+-------------------------+

| CAST(0 AS UNSIGNED) - 1 |

+-------------------------+

| -1 |

+-------------------------+

如果此操作的结果用于更新UNSIGNED整数列，则结果将被剪裁为列类型的最大值，如果启用NO_UNSIGNED_SUBTRACTION，则剪切为0。如果启用严格SQL模式，则会发生错误并且列保持不变。

ITPub博客

11.1 数值类型

最新文章