总览表

MySQL 支持标准 SQL 中所有的数据类型，此外还拓展了一些，总体来说可分为整数、浮点数、定点数和位，如下表所示

  • 整数类型

    名称 字节 最小值 最大值
    TINYINT 1 有符号 -128
    无符号 0    		 有符号 127
    无符号 255
    SMALLINT 2 有符号 -32768
    无符号 0    		 有符号 32767
    无符号 65535
    MEDIUMINT 3 有符号 -8388608
    无符号 0    		 有符号 -8388607
    无符号 1677215
    INT 或 INTEGER 4 有符号 -2147483648
    无符号 0    		 有符号 2147483647
    无符号 4294967295
    BIGINT 8 有符号 -9223372036854775808
    无符号 0    		 有符号 -9223372036854775807
    无符号 18446744073709551615

    说明：有符号/无符号 指的是这一字段是否有 UNSIGNED 这一属性

  • 浮点数类型

    名称 字节 最小值 最大值
    FLOAT 4 ±1.175494351E-38 ±3.402823466E+38
    DOUBLE 8 ±2.2250738585072014E-308 ±1.7976931348623157E+308

  • 定点数类型

    名称 字节 描述
    DEC(M,D) 或 DECIMAL(M,D) M+2 最大取值与 DOUBLE 相同， DEC 的有效取值范围由 M 和 D 决定

  • 位类型

    名称 字节 最小值 最大值
    BIT(M) 1~8 BIT(1) BIT(64)

整数类型

整数类型分为 5 个：TINYINT 、SMALLINT 、MEDIUMINT 、INT 、BIGINT，它们的大小在上表中，非常简洁明了

下面将介绍有关它的三个属性

ZEROFILL（补零）

这个在当前版本已经不建议使用了，并且会在未来的版本中删除，但是还是讲一下

对于整型数据，MySQL 支持 使用小括号来指示显示宽度 （这里的括号含义只是显示宽度，与存储没关系，记住啦！）并且这个一般是配合 ZEROFILL 属性使用的，例如 INT(5) 的意思是要显示的时候如果不足 5 位，就在前面补宽度到 5 位，如果不手动指定显示宽度的话默认是 INT(11)

下面来演示一下，先建表

1
CREATE TABLE t1 (id1 INT, id2 INT(5));

然后插入数据，并且发现格式没有变化（但是有些样例会补空格，我不知道是为什么）

1
2
INSERT INTO t1 VALUES(1,1);
SELECT * FROM t1;

image-20220628163218578

然后给它们加上 ZEROFILL 属性，可以发现有效果了

1
2
3
ALTER TABLE t1 MODIFY id1 INT ZEROFILL;
ALTER TABLE t1 MODIFY id2 INT(5) ZEROFILL;
SELECT * FROM t1;

image-20220628163421141

要记住，这个只是规定了显示补零的位数，不改变存储的大小

并且，如果数据超过了这个位数，那么宽度格式就没有意义了，按照原样输出

1
2
INSERT INTO t1 VALUES(1234567,1234567);
SELECT * FROM t1;

image-20220628163618590

UNSIGNED（无符号）

所有的整数都可以有一个属性 UNSIGNED（无符号），使用该属性时最小值位 0，最大值翻倍（就如上表所示）

并且，如果已经有了 ZEROFILL 属性， MySQL 会自动为该列添加 UNSIGNED

AUTO_INCREMENT（自增）

需要产生唯一标识符或顺序值时，可以使用该属性

AUTO_INCREMENT 的值一般从 1 开始，当欲插入 NULL 或 DEFAULT 到一个使用了该属性的列时，MySQL 会插入一个比该列中最大值大 1 的值

关于该属性，有下面几点说明：

  1. 只有整数类型才能使用该属性

  2. 必须同时有 NOT NULL 属性，建议同时使用 UNSIGNED 属性

  3. 只有主键（primary key）或唯一键（unique）或为它们的一部分才能使用该属性（其实与上一条一个意思）

  4. 建表时，可以使用 AUTO_INCREMENT= N 来指定自增起始值，建表后可用 ALTER TABLE table_name AUTO_INCREMENT= N 来重设自增的起始值

  5. 当插入/更新记录时，如果为 AUTO_INCREMENT 数据列明确指定了一个数值，则会出现两种情况

    • 如果插入/更新的值与已有的编号重复，则会出现出错信息，因为 AUTO_INCREMENT 数据列的值必须是唯一的
    • 如果插入/更新的值大于已编号的值，则会把该值插入/更新到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号

  6. 如果自增序列的最大值被删除了，则在插入新记录时，该值被重用

小数类型（浮点与定点）

对于小数，MySQL 有两种方式：浮点与定点。浮点包括 FLOAT 和 DOUBLE ，定点数只有 DECIMAL 一种。定点数以字符串的形式存放，比浮点数精确，适合用来表示货币等精度高的数据

与整型类似，小数后面也可以加带括号的后缀，但是含义是不同的

首先是与整型的不同，整数和浮点数后面的括号形如 (M,D) ，精度 M 表示一共有多少位（整数加小数），标度 D 表示其中小数有多少位，

  • 对于浮点数，正如上表所述，这个类型的存储大小是不受这个后缀改变的，只是当保存数据的时候，会按照这个后缀的精度进行四舍五入保存，并且如果超过了长度限制（即使这个类型原本是能装下的）就会报错
  • 对于定点数，这个后缀会同时决定类型的大小和存储规则

如果不指定精度的话，浮点数会按照实际的精度（有实际的软硬件决定）显示，定点数默认为 (10,0)

下面是一个例子，来比较 FLOAT 、 DOUBLE 和 DECIMAL 三者的不同

首先建个表，字段分别为 float(5,2) 、 double(5,2) 和 decimal(5,2)

1
2
3
4
5
CREATE TABLE t1 (
    id1 float(5,2) default NULL,
    id2 double(5,2) default NULL,
    id3 decimal(5,2) default NULL
);

然后分别插入数据 1.23 ，可以看见数据都正确地被插入了

1
2
INSERT INTO t1 VALUES(1.23,1.23,1.23);
SELECT * FROM t1;

image-20220630215501621

接下来尝试数据 1.234 ，可以看见前两个浮点类型舍去了最后一位，并且定点类型报警被截断了

（如果是在传统的 SQLMode ，这条记录是无法被插入的）

1
2
3
INSERT INTO t1 VALUES(1.234,1.234,1.234);
SHOW WARNINGS;
SELECT * FROM t1;

image-20220630215739437

现在把三个字段的精度和标度全部去掉，再插入一次数据

1
2
3
4
5
6
ALTER TABLE t1 MODIFY id1 FLOAT;
ALTER TABLE t1 MODIFY id2 DOUBLE;
ALTER TABLE t1 MODIFY id3 DECIMAL;
DESC t1;
INSERT INTO t1 VALUES(1.234,1.234,1.234);
SELECT * FROM t1;

image-20220630221310419

位类型

对于 BIT（位）类型，顾名思义用于存放位， BIT(M) 可以存放多位二进制数，M 的范围为 1~64 ，默认值为 1

对于位字段，可以使用 SELECT 查询 16 进制结果（但在 MySQL 5.7 中是不显示的），也可以使用 BIN() 或 HEX() 函数读取

1
2
3
4
5
CREATE TABLE t2 (id BIT);
DESC t2;
INSERT INTO t2 VALUES(1);
SELECT * FROM t2;
SELECT BIN(id),HEX(id) FROM t2;

image-20220701224711079

插入数据时，首先会将数字转换为二进制然后插入， 如果位数不够就会报错

1
2
3
4
INSERT INTO t2 VALUES(2);
ALTER TABLE t2 MODIFY id BIT(2);
INSERT INTO t2 VALUES(2);
SELECT BIN(id),HEX(id) FROM t2;

image-20220701224916068