3.1　数值数据类型

MySQL支持所有标准SQL数值数据类型，这种数据类型包括整数类型、浮点数类型、定点数类型和bit数据类型。数值数据类型由精确数据类型和近似数值数据类型。

3.1.1　整数类型

整数类型，也是MySQL中的精确数值类型。整数类型主要有tinyint、smallint、int、mediumint、bigint、decimal和numeric等。bit数据类型也属于整数类型，存储位值（0和1）。

整数类型定义的语法格式如下所示，这里只简单定义几种。

     -- tinyint 语法格式
     tinyint[(m)] [unsigned] [zerofill]
     -- smallint 语法格式
     smallint[(m)] [unsigned] [zerofill]
     -- int语法格式
     int[(m)] [unsigned] [zerofill]

说明：

• m：表示最大显示宽度。最大显示宽度为255。对于整数数据类型，显示宽度与类型可以存储的值的范围无关；对于浮点和定点数据类型，m是可以存储的总位数。
• unsigned：表示整数是无符号类型（unsigned），可以为负数。如果是有符号类型（signed），则数值范围是0～255。
• zerofill：如果为数字列指定zerofill，则MySQL会自动将unsigned属性添加到该列。从MySQL 8.0.17开始，zerofill属性已正式被数据类型弃用，读者可以暂时不用关注。

整数类型在工作中用得非常多，这个知识点也很重要。整数类型字面意思就是用来存储整数，仍然以animal表为例：

     -- auto-generated definition
     create table animal
     (
        Id              int             not null comment '主键'  primary key,
        name            varchar(50)   not null comment '动物名称',
        en_name         varchar(255)   null comment '英文名',
        age             smallint        not null comment '年龄',
        description  text            null comment '详细介绍',
        create_time  datetime        not null comment '创建时间',
        update_time  datetime        not null comment '更新时间',
        is_delete    tinyint         not null comment '是否删除，1 表示已删除，0 表示未删除'
     ) charset = utf8mb4;

animal表的age字段使用smallint整数类型（范围：-32768～32767），有符号类型（signed）。无符号类型（unsigned）属性的数值数据类型也允许有符号类型（signed）。这些数据类型默认是有符号的，因此smallint默认就是有符号类型。其实动物的年纪不可能出现负数，因此age字段可以优化为无符号类型，具体SQL语句如下：

     ### 使用alter语句修改age字段符号类型
     mysql> alter table animal modify age smallint unsigned not null comment '年龄';

除了考虑整数类型的符号性外，还有一点非常重要，就是整数类型占用的存储空间不同。表3-1给出了每种整数类型所需的存储空间和范围。

表3-1　整数类型存储空间和范围（来自MySQL官网）

从表中可以看出，tinyint占用1字节，smallint、mediumint、int以及bigint分别占2、3、4、8字节。占字节数少的整数可以节省存储空间，但是，不能为了节省存储空间就牺牲可靠性。比如animal表的age字段，使用无符号的smallint类型（范围为0～32767）而不使用无符号的tinyint类型（范围为0～255）。在实际工作中，系统故障产生的成本远远超过增加存储空间所产生的成本。因此，建议首先确保数据不会超过整数的取值范围，在这个前提之下，再去考虑如何节省存储空间。

3.1.2　浮点数类型

浮点数类型简单理解就是用来保存小数（例如9.9、12.9等）的数据类型，主要有float和double，浮点数类型是一种近似数值类型。

float表示单精度浮点数，占4字节，精度是6位；double表示双精度浮点数，占8字节，精度是16位。

我们可以这样理解：

（1）当数据插入float类型的字段时，MySQL会优先保留6位有效数字，对右侧的数字进行四舍五入。

（2）当数据插入double类型的字段时，MySQL会优先保留16位有效数字，对右侧的数字进行四舍五入。

float类型示例如下：

double类型示例如下：

MySQL支持可选精度规范，如float(p)。p的取值范围是0～53，超过范围会报错。另外，p仅用来决定MySQL内部是采用float还是double存储。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。

浮点数最大的问题就是不精准，例如：

预期的结果是7.89，可实际的结果是7.9799999999999995，明显不符合预期。那有没有更精确的数据类型呢？当然有，就是接下来要讲解的定点数类型。

3.1.3　定点数类型

MySQL使用decimal和numeric类型存储精确的数字数据值，当需要保持精确的精度时使用这些类型，例如货币数据，因此，定点数类型也是精确数据类型。在MySQL中，numeric被实现为decimal，因此以下关于decimal的用法同样适用于numeric。

MySQL使用decimal（m,d）的方式表示高精度小数。其中，m表示整数部分加小数部分，一共有多少位，m≤65；d表示小数部分位数，d<m。

decimal示例如下：

标准SQL要求decimal（5，2）能够存储任何具有5位整数和2位小数的值，因此可以存储的值范围为-999.99～999.99。在标准SQL中，语法decimal（m）等效于decimal（m，0）。类似地，语法decimal等效于decimal（m，0）。m的默认值为10。

从结果可以看出，得出的结果满足预期。定点数类型取值范围相对较小，但是精准，没有误差，适合对精度要求极高的场景，比如涉及金额计算的场景。定点数类型非常重要，在工作中会经常被用到，因此读者要重点掌握，用心实践方能出真知。

3.1.4　bit类型

bit类型用于存储位值。bit（m）允许存储m位值，m的范围为1～64。要指定位值，可以使用b'value'表示法。值是使用0和1写入的二进制值。例如，b'111'和b'10000000'分别表示7和128。bit类型示例如下：

bin()、oct()以及hex()函数用于将字段的值转化为二进制、八进制以及十六进制。如果为长度小于m位的bit(m)列赋值，则该值将在左侧用零填充。例如，将值b'101'分配给bit(6)列，实际上与赋值b'000101'相同。

3.1.5　数值类型属性

MySQL支持在类型的基本关键字后面的括号中指定整数数据类型的显示宽度，例如int(4)指定一个显示宽度为4位数的int。当整数值的宽度小于为列宽度指定的显示宽度时，MySQL用空格左填充它们。

显示宽度不限制可存储在列中的值范围，它也不会阻止宽于列显示宽度的值正确显示。例如，指定为smallint(3)的列通常具有-32768～32767的smallint范围，并且超出3位数字允许范围的值将使用3位以上的数字完整显示。

当与zerofill属性结合使用时，默认的以空格填充将替换为以0填充。例如，对于声明为int(4)zerofill的列，将检索值5填充为0005。

3.1.6　超出范围和溢出处理

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

• 如果启用了严格SQL模式，则MySQL会拒绝超出范围的值并显示错误，插入失败。
• 如果未启用限制模式，则MySQL会将值裁剪到列数据类型范围的相应端点，并改为存储结果值。

当将超出范围的值分配给整数列时，MySQL将存储表示列数据类型范围的相应端点的值（也就是最值）。下面看一个具体示例：

     -- 创建表
     mysql> 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

如果未启用严格SQL模式，则会出现带有警告的提示。

如果是数值表达式，在计算期间溢出则会导致错误。例如，最大的有符号bigint值是9223372036854775807，因此以下表达式会产生错误：

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

若要使操作在这种情况下成功，可将值转换为无符号值：

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

默认情况下，整数值之间的减法（其中一个值的类型为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模式，则结果为负数： iJsBsJWEsfrMRE/q2HJSaRSv3O0fOxxghZY+U5u+WFkp0ShjnPGbcMLjlkMcWDO7