MySQL文档阅读（一）-数字类型

这个系列参考自MySQL官方文档：MySQL5.7官方文档

MySQL支持很多系列的SQL数据类型：数字类型（numeric types）、日期和时间类型（date and time types）、字符串类型（字符和字节）、特殊类型和JSON数据类型。

数据类型描述遵循如下约定：

• M表示整数类型最大的显示宽度值；对于浮点数和固定长度数据类型，M表示数字在MySQL中的总的存储长度；对于字符串类型，M表示字符串的最大长度。数据类型的最大值就是M的最大值。
• D决定了浮点类型和固定（fixed-point）类型，表示十进制数据的位数。D的最大值是30，但要小于M-2。
• fsp应用在TIME、DATETIME和TIMESTAMP类型上，这个数字表示秒的分数部分。fsp的值应该是0到6：0表示没有分数部分（如果没有提供fsp值，默认值为0）。
• 中括号（[]）表示数据类型中的可选部分。

数据类型概览

1. 数字类型

• 整数（INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT）
  在创建表的时候，让我们填写的那个数字就是M，表示可显示的最大宽度，而数字的实际范围则由数据类型决定。例如：TINYINT[(M)] [UNSIGNED] [ZEROFILL]中，M就是表示可显示宽度，TINYINT实际可表示的数值范围如下图所示。

  
  MySQL支持的整数类型

• Fixed-Point类型（DECIMAL、NUMERIC）
  DECIMAL和NUMERIC类型代表精确数字类型。当业务需求（例如涉及金钱的数据）要求在数据库中存储精确数值时，需要使用这些类型。在MySQL中NUMERIC也实现为DECIMAL，因此后续针对DECIMAL的叙述也适用于NUMERIC。
  MySQL以二进制格式存储DECIMAL值。在DECIMAL的列定义中需要制定precision和scale值，例如：

salary DECIMAL(5, 2)

在这个例子中，precision是5，scale是2；其中，precision表示用于表示数字的位数、scale表示小数点所在的位置。在标准SQL语法中，要求DECIMAL(5,2)表示5位数字，以及2位小数，它的取值范围是[-999.99, 999.99]。按照SQL语法，DECIMAL(M)等同于DECIMAL(M,0)；DECIMAL也等同于DECIMAL(M,0)，M的默认值是10。如果scale等于0，则DECIMAL值不包括小数部分。

• 浮点类型（FLOAT、DOUBLE）
  FLOAT和DOUBLE类型代表近似值。MySQL使用4个字节表示单精度值、使用8个字节表示双精度值。对于FLOAT类型，SQL标准规定一份可选的精度规范，MySQL也支持这种可选的精度规范，但是精度值仅仅用于决定存储空间大小。单精度的FLOAT类型表示4个字节；24-53的精度则需要双精度DOUBLE类型表示。
  MySQL允许一个非标准的语法：FLOAT(M, D)、REAL(M, D)，或者DOUBLE PRECISION(M, D)，在这里，M表示数值的最大存储位数是M，而D则表示小数点后有多少位。例如，FLOAT(7, 4)可以表示-999.9999。MySQL在存储数值的时候会执行四舍五入，因此，对于FLOAT(7, 4)如果你插入的数字是999.00009，则实际存储的值是999.0001。
  正因为浮点数表示近似值而不是精确值，因此在试图把它们当做精确值进行比较的时候会有问题。具体的比较结果由系统决定，如果需要更详细资料，参考：Section B.5.4.8, “Problems with Floating-Point Values”
• Bit值类型（BIT）
  BIT数据类型用于存储bit值，BIT(M)可存储M-bit值，M的范围是1~64。
  为了标识是bit值，需要使用b'value'格式表示。value是一个0和1表示的二进制值。例如：b'111'表示7，b'10000000'表示128。
  如果你给一个BIT(M)的列赋值一个小于M位的值，MySQL会在值的左边填充0，例如，如果将b'101'赋值给BIT(6)的列，则对应存储的值是b'000101'。
• 数字类型属性
  MySQL支持通过在数据类型后面加括号的方式限制整数类型的显示宽度。例如，INT(4)定义了一个整数类型，但是显示宽度为4。如果应用中的数值小于指定的宽度，则数字的左边用空格填充。
  显示宽度并不会限制该列能够存储的值；也会让宽于指定宽度的值正确显示出来。例如，某个列的数据类型设定为SMALLINT(3)，则该列可存储的范围是-32768到32767，这些超出指定宽度的值也能正确显示。
  在使用ZEROFILL属性时，数字左边空余的部分会由0填充，例如：对于一个定义为INT(4) ZEROFILL的列，给定一个值为5，将显示为0005。
  所有的Integer类型都有一个可选的属性——UNSIGNED。当某个属性只需要存放非负数字或者需要更大的数字上限时。例如：如果一个属性定义为UNSIGNED INT，这个属性能表示的范围跟SIGNED相同，但是由[-2147483648,2147483647]平移到[0, 4294967295]。
  浮点数和精确数值也可以设置成UNSIGNED，这种情况下，跟整数类型一样不允许存储负数；跟整数类型不一样的是，该属性的表示范围上限与SIGNED相同。
  如果你给一个numeric列指定了ZEROFILL属性，则MySQL会自动为其设置UNSIGNED属性。
  对于INTEGER和浮点数类型，还有一个额外的属性——AUTO_INCREMENT。当你将一个NULL值插入到一个加了AUTO_INCREMENT索引的列，该列将会被设置为下一个顺序值。一般来说是现有的value+1，而这个value值正是当前表中该属性列的最大值（AUTO_INCREMENT属性列的值从1开始计数）。
  将0存入AUTO_INCREMENT列和NULL的情况具备相同的效果，除非MySQL开启了NO_AUTO_VALUE_ON_ZERO模式。
  如果要达到“插入NULL值产生自增值”的效果，需要将对应的AUTO_INCREMENT列设置为NOT NULL。如果该列设置为NULL（可空），那么在插入NULL值的时候就会直接存储为NULL。
  MySQL5.7并不支持负数的自动增长。

注意：
当该属性属于某个表达式或者UNION查询时，MySQL会忽略ZEROFILL属性。

• 溢出处理
  当MySQL存储的值超过了某个属性所能表示的范围，这时候实际存储的结果取决于当时MySQL中的SQL模式：

  • 如果开启了严格SQL模式，MySQL会拒绝溢出的值，会报错，插入数据失败；
  • 如果没有开启严格SQL模式，则MySQL会根据数据类型能表示的最大值将该溢出值截断，并存储该数据类型能表示的最大值；例如：当把一个溢出值赋值给一个Integer属性，MySQL实际上存储的是该Integer类型所能表示的边界值。如果你将256存入TINYINT或者TINYINT UNSIGNED列，MySQL会分别存储127或255。当把一个溢出值赋值给一个浮点数或者一个FIXED-POINT列时，MySQL会截断并存储该数据类型能表示的边界值。

  在numberic表达式求值过程中发生溢出，则会导致一个错误。例如，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                       |
+-------------------------------------------+

两个Integer相减，如果其中一个为UNSIGNED，则最后的结果为UNSGINED。当计算过程中发现结果为负数，则会报出错误：

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模式——“NO_UNSIGNED_SUBTRACTION”开启时，才会显示正确的负数结果：

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。如果开启了严格模式，则会报出错误，并且对应的属性值不会改变。