vlambda博客
学习文章列表

MySQL 支持所有标准 SQL 中的数值类型，其中包括：
❶ 严格数值类型：INTEGER、SMALLINT、DECIMAL、NUMERIC；
❷ 近似数值类型：FLOAT、REAL、DOUBLE；
❸在❶❷基础上做了扩展，扩展后增加了TINYINT、MEDIUMINT、BIGINT 3 种长度不同的整型，并增加了 BIT 类型，用来存放位数据。

如下表，列出了 MySQL 中支持的所有数值类型，其中 INT 是 INTEGER 的同名词，DEC 是 DECIMAL 的同名词。

1. 整数类型

按照取值范围和存储方式不同，分TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT 这 5 个类型。如果超出类型范围，会发生"Out of range"错误提示。为了避免此类问题发生，在选择数据类型时要根据应用的实际情况确定取值范围，最后根据确定的结果甚至选择数据类型。

对于整数类型，MySQL 还支持在类型名称后面的小括号内指定显示宽度，例如 int(5) 表示当数值宽度小于 5 位的时候在数字前面填满宽度，如果不显示指定宽度则默认为 int(11)。一般配合 zerofill 使用，顾名思义，zerofill 就是用"0"填充的意思，也就是在数字位数不够的空间用字符"0"填满。

如下示例描述了填充前后的区别：

# ❶ 创建表 t1，有 id1 和 id2 两个字段，指定其数值宽度分别为 int 和 int(5)
mysql> use test;
Database changed
mysql> create table t1(id1 int, id2 int(5));
Query OK, 0 rows affected, 1 warning (0.05 sec)
# ❷ 在 id1 和 id2 中都插入数值 1，可以发现格式没有异常
mysql> insert into t1 value(1, 1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
----------------------
    id1        id2    
----------------------
     1           1    
----------------------
1 row in set (0.00 sec)
# ❸ 分别修改 id1 和 id2 的字段类型，加入 zerofill 参数
mysql> alter table t1 modify id1 int zerofill;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> alter table t1 modify id2 int(5) zerofill;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 2
mysql> select * from t1;
--------------------------------------------
             id1                      id2    
--------------------------------------------
          0000000001            00001    
--------------------------------------------
1 row in set (0.00 sec)

可以发现，在数值前面字符"0"填充了剩余的宽度。大家可能会有疑问，设置了宽度限制后，如果插入大于宽度限制的值，会不会截断或者插不进去报错？答案是：不会对插入的数据有任何影响，还是按照类型的实际精度进行保存，这时的宽度格式实际已经没有意义，左边不会再填充任何的"0"字符。

所有的整数类型都有一个可选属性 UNSIGNED（无符号），如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取 0，上限取原值的 2 倍。例如，tinyint 有符号范围是 -128 ~ +127，而无符号范围是 0 ~ 255。如果一个列指定为 zerofill，则 MySQL 自动为该列添加 UNSIGNED 属性。

所有的整数类型都有一个可选属性 AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。在插入 NULL 到一个 AUTO_INCREMENT 列时，MySQL 插入一个比该列中当前最大值大 1 的值。

一个表中最多只能有一个 AUTO_INCREMENT 列，对于任何想要使用 AUTO_INCREMENT 的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。

2. 浮点数和定点数

对于小数的表示，MySQL 分为两种方式：浮点数和定点数。

浮点数包括 FLOAT（单精度）和 DOUBLE（双精度），而定点数则只有 DECIMAL 一种表示。定点数在 MySQL 内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

浮点数和定点数都可以用类型名称后加"(M,D)"的方式来进行表示，"(M,D)"表示该值一共显示 M 位数字（整数位+小数位），其中 D 位位于小数点后面，M 和 D 又称为精度和标度。

FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度来现实，这取决于实际的硬件和操作系统决定；DECIMAL 在不指定精度时，默认的整数位为 10，默认的小数位为 0。

下面通过一个例子来比较 FLOAT 、DOUBLE 和 DECIMAL三者之间的不同。

# ❶ 创建测试表 t1
CREATE TABLE t1 (
    id1 float(5,2) default NULL,
    id2 double(5,2) default NULL,
    id3 decimal(5,2) default NULL
);
# ❷ 往 id1、id2、id3 这 3 个字段中插入数据 1.23
mysql> insert into t1 values(1.23, 1.23, 1.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
----------------------
 id1   id2   id3  
----------------------
 1.23  1.23  1.23 
----------------------
1 row in set (0.00 sec)
# ❸ 同时向 id1、id2、id3 字段中都插入数据 1.234
# id1 和 id2 由于标度的限制，舍去了最后一位，数据变为了 1.23
# id3 被截断
mysql> insert into t1 values(1.234, 1.234, 1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t1;
----------------------
 id1   id2   id3  
----------------------
 1.23  1.23  1.23 
 1.23  1.23  1.23 
----------------------
2 rows in set (0.00 sec)
# ❹ 将 id1、id2、id3 字段的精度和标度全部去掉
mysql> alter table t1 modify id1 float;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t1 modify id2 double;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t1 modify id3 decimal;
Query OK, 2 rows affected, 2 warnings (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 2
mysql> desc t1;
--------------------------------------------------------
 Field  Type           Null  Key  Default  Extra 
--------------------------------------------------------
 id1    float                 YES        NULL           
 id2    double             YES        NULL           
 id3    decimal(10,0)   YES        NULL           
--------------------------------------------------------
3 rows in set (0.00 sec)
# ❺ 再次插入数据 1.234
# id1、id2 字段中可以正常插入数据
# id3 字段的小数位被截断
mysql> insert into t1 values(1.234, 1.234, 1.234);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t1;
----------------------
 id1    id2    id3  
----------------------
 1.234  1.234   1 
----------------------
1 rows in set (0.00 sec)

上述示例验证了上面提到的浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入的结果插入，系统不会报错；定点数如果不写精度和标度，则会按照默认值 decimal(10,0) 来进行操作，并且如果数据超越了精度和标度值，系统则会报错。

3. 位类型

BIT（位）类型，用于存放位字段值，BIT(M) 可以用来存放多位二进制数，M 范围从 1～64，如果不写则默认为 1 位，对于位字段，直接使用 SELECT 命令将不会看到结果，可以用 bin() 函数显示为二进制格式或者 hex() 函数显示为十六进制格式进行读取。

下面的例子中，对测试表 t2 中的 bit 类型字段 id 做 insert 和 select 操作，重点观察一下 select 的结果：

# ❶ 创建测试表 t2
CREATE TABLE t2 (
    id bit(1) default NULL
);
# ❷ 向 id 插入数字 1
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.01 sec)
# ❸ 可以发现 select * 的结果为 NULL
mysql> select * from t2;
------
 id   
------
     
------
1 row in set (0.00 sec)
# ❹ 改用 bin() 和 hex() 函数查询
# 结果可以正常显示为二进制数字和十六进制数字
mysql> select bin(id), hex(id) from t2;
--------------------
 bin(id)  hex(id) 
--------------------
    1           1       
--------------------
1 row in set (0.00 sec)
❺ 插入数字 2，因为它的二进制码是"10"，而 id 的定义是 bit(1)，无法进行插入
mysql> insert into t2 values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1
❻ 将 id 定义修改为 bit(2) 后，重新插入，插入成功
mysql> alter table t2 modify id bit(2);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t2 values(2);
Query OK, 1 row affected (0.01 sec)