MySQL 数据类型：数值数据类型

来自Wikioe
跳到导航 跳到搜索


整数类型(精确值)：INT（INTEGER），SMALLINT，TINYINT，MEDIUMINT，BIGINT

MySQL 支持 SQL 标准整数类型 INTEGER（或INT）和 SMALLINT。作为对标准的扩展，MySQL 还支持 TINYINT，MEDIUMINT 和 BIGINT 整数类型。

Type Storage(Bytes) 最小值（有符号） 最大值（有符号） 最小值（无符号） 最大值（无符号）
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列声明中，可以(通常是)指定精度和小数位数。例如： 

salary 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。
  • 如果小数位数为 0，则DECIMAL值不包含小数点或小数部分。
  • DECIMAL的最大位数为 65，但是给定DECIMAL列的实际范围可能受给定列的精度或小数位数的限制。当为这样的列分配的值的小数点后的位数比指定刻度允许的位数多时，该值将转换为该刻度。 (精确的行为是特定于操作系统的，但是通常效果是将其截断为允许的位数.)

浮点类型(近似值)：FLOAT，DOUBLE

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


对于 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”。
  • 由于浮点值是近似值而不是作为精确值存储的，因此在比较中尝试将它们视为精确值可能会导致问题。它们还受平台或实现依赖性的约束。

位值类型：BIT

BIT数据类型用于存储位值。“BIT(M)”类型可以存储“M”位值。“M”的范围是 1 到 64.


要指定位值，可以使用“b'value'”表示法。“value”是使用零和一写入的二进制值。例如，“b'111'”和“b'10000000'”分别代表“7”和“128”。

数值类型属性

  1. MySQL 支持扩展，可以选择在整数类型后的括号中指定整数数据类型的显示宽度。
    例如，“INT(4)”指定显示宽度为四位数的INT。
  2. 显示宽度不限制可以存储在列中的值的范围。也不会阻止宽于列显示宽度的值正确显示。
    例如，指定为“SMALLINT(3)”的列通常具有“-32768”到“32767”的 SMALLINT 范围，并且使用三位以上的数字完整显示三位数所允许的范围之外的值。
  3. 与可选的（非标准）“ZEROFILL”属性结合使用时，默认的空格填充将替换为零。
    例如，对于声明为“INT(4) ZEROFILL”的列，将检索到的“5”显示为为“0005”。
    • 表达式或UNION查询中涉及的列将忽略 ZEROFILL 属性。
    • 如果将大于显示宽度的值存储在具有 ZEROFILL 属性的整数列中，则当 MySQL 为某些复杂的联接生成临时表时，可能会遇到问题。在这些情况下，MySQL 假定数据值适合列显示宽度。
  4. 所有整数类型都可以具有可选的（非标准）“UNSIGNED”属性。无符号类型可用于仅允许一列中使用非负数，或者在您需要该列的较大的较高数字范围时使用。
    例如，如果 INT 列为 UNSIGNED，则列范围的大小相同，但其端点从 -2147483648 和 2147483647 向上移动到 0 和 4294967295。
    • 浮点和定点类型也可以是 UNSIGNED。与整数类型一样，此属性可防止将负值存储在列中。与整数类型不同，列值的上限范围保持不变。
  5. 如果为数字列指定 ZEROFILL，则 MySQL 将自动添加 UNSIGNED 属性。
  6. 整数或浮点数据类型可以具有“AUTO_INCREMENT”属性。当您将 NULL 的值插入到已索引的 AUTO_INCREMENT 列中时，该列将设置为下一个序列值。通常，它是 value+1，其中“value”是 table 中当前列的最大值。
    • （AUTO_INCREMENT序列以1开头）
  7. 将 0 存储到 AUTO_INCREMENT 列与存储 NULL 具有相同的效果，除非启用了“NO_AUTO_VALUE_ON_ZERO”的 SQL 模式。
  8. 插入 NULL 以生成 AUTO_INCREMENT 值要求将该列声明为“NOT NULL”。如果将该列声明为“NULL”，则插入 NULL 将存储 NULL。当您在 AUTO_INCREMENT 列中插入任何其他值时，该列将设置为该值，并且将重置序列，以便从插入的值开始依次生成下一个自动生成的值。
  9. AUTO_INCREMENT 列不支持负值。

超出范围和溢出处理

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

  1. 如果启用了严格的 SQL 模式，则按照 SQL 标准，MySQL 会拒绝超出范围的值并产生错误，并且插入将失败。
    假设 tablet1具有以下定义：
    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)
  2. 如果未启用任何限制模式，MySQL 会将值裁剪到列数据类型范围的适当端点，并存储结果值。
    接上例：
    如果未启用严格 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，UPDATE 和多行 INSERT 语句的警告。在严格模式下，这些语句将失败，并且某些或所有值都不会插入或更改，这取决于该 table 是否为事务 table 和其他因素。


数值 table 达式求值期间的溢出会导致错误。

例如，最大的有符号 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 模式，则会发生错误并且该列将保持不变。

取自“http://wiki.eijux.com/index.php?title=MySQL_数据类型：数值数据类型&oldid=6520”