每一个常量、变量和参数都有数据类型，它用来指定一定的存储格式、约束和有效范围。MySQL 提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。

不同的 MySQL 版本支持的数据类型可能会稍有不同，用户可以通过查询相应版本的帮助文件来获得具体信息。该文章将以 MySQL5.7 版本为例，详细介绍 MySQL 中的各种数据类型。

数值类型

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

mysql_datatype01

注：无符号，表示设置的的数据为0或者正数；有符号则可以是负数。

在整数类型中，按照取值范围和存储方式不同，分为 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> create table t1 (id1 int, id2 int(5));
Query OK, 0 rows affected (0.03 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | YES  |     | NULL    |       |
| id2   | int(5)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
  • 在 ID1 和 ID2 中都插入数值 1，可以发现格式没有异常
mysql> insert into t1 values(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, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify id2 int(5) zerofill;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------------+-------+
| id1        | id2   |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.00 sec)

从上述查询结果可发现，在数值前面用字符“0”填充了剩余的宽度。

AUTO_INCREMENT

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

一个表中最多只能有一个 AUTO_INCREMENT 列。

mysql> create table ai(id int auto_increment not null primary key);
Query OK, 0 rows affected (0.02 sec)

浮点数和定点数

对于小数的表示，MySQL 分为两种方式：浮点数和定点数。浮点数包括 float 和 double，而定点数则只有 decimal 一种表示。定点数在 MySQL 内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

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

举例说明 float 、double 和 decimal 三者之间的不同：

  • 创建测试表 T2，分别将 ID1 、ID2 、ID3 字段设置为 float(5,2) 、double(5,2) 、decimal(5,2)
mysql> create table t2 (
    id1 float(5,2) default null, 
    id2 double(5,2) default null, 
    id3 decimal(5,2) default null 
);
Query OK, 0 rows affected (0.02 sec)
  • 往 ID1 、ID2 、ID3 字段中插入数据 1.23
mysql> insert into t2 values(1.23, 1.23, 1.23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)
  • 清空表，然后向 ID1 、ID2 、ID3 字段中都插入数据 1.456 
mysql> truncate table t2;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(1.456, 1.456, 1.456);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.46 | 1.46 | 1.46 |
+------+------+------+
1 row in set (0.00 sec)

从上述结果可知，此时虽然数据写入进去了，但是由于字段精度的限制，只保留了两位小数，且发生了四舍五入。

  • 将 ID1 、ID2 、ID3 字段的精度和标度全部去掉，清空表，再次插入数据 1.456 
mysql> alter table t2 modify id1 float;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t2 modify id2 double;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t2 modify id3 decimal;
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> desc t2;
+-------+---------------+------+-----+---------+-------+
| 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)

mysql> truncate table t2;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(1.456, 1.456, 1.456);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
| 1.456 | 1.456 |    1 |
+-------+-------+------+
1 row in set (0.00 sec)

注：对于浮点数，如果不写精度和标度，则会按照实际精度值显示数据；如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值 decimal(10,0) 来进行操作（上述操作中，ID3 字段结果为 1 ），并且如果数据超过了精度和标度，系统则会报错。

位类型

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

  • 创建表 T3，ID 字段为 BIT 类型
mysql> create table t3 (id bit(1));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)
  • 直接使用 SELECT 查询的结果为 NULL
mysql> select * from t3;
+------+
| id   |
+------+
|     |
+------+
1 row in set (0.00 sec)
  • 改用 bin() 、hex() 函数，结果可以正常显示为二进制数字和十六进制数字
mysql> select bin(id), hex(id) from t3;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.00 sec)

数据插入 BIT 类型时，首先转换为二进制，如果位数允许，将成功插入；如果位数小于实际定义的位数，则插入失败。

  • 往表 T3 的 ID 列插入数字 2，由于数字 2 的二进制是“10”，而 ID 字段的定义为 BIT(1)，将无法进行插入
mysql> insert into t3 values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1
  • 将 ID 列定义修改为 BIT(2) 后，重新插入，即可成功
mysql> alter table t3 modify id bit(2);
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t3 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> select bin(id), hex(id) from t3;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
| 10      | 2       |
+---------+---------+
2 rows in set (0.00 sec)

日期和时间类型

MySQL 中有多种数据类型可以用于日期和时间的表示，这些数据类型的主要区别如下：

  • 如果要用来表示年月日，通常用 DATE 来表示
  • 如果要用来表示年月日时分秒，通常用 DATETIME 来表示
  • 如果只用来表示时分秒，通常用 TIME 来表示
  • 如果要经常插入或者更新日期为当前系统时间，则通常使用 TIMESTAMP 来表示
  • 如果只是表示年份，可以用 YEAR 来表示，它比 DATE 占用更少的空间

mysql_datatype02

举例说明 DATE 、TIME 和 DATETIME 这三种最常用的日期类型：

  • 创建表 T4，字段分别为 date 、time 、datetime 三种日期类型
mysql> create table t4(d date, t time, dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 用 now() 函数插入当前日期
mysql> insert into t4 values(now(), now(), now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2021-11-19 | 21:10:11 | 2021-11-19 21:10:11 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

从上述结果可知，DATETIME 是 DATE 和 TIME 的组合，可以根据不同的需要，来选择不同的日期或时间类型以满足不同的需求。

字符串类型

MySQL 中提供了多种对字符数据的存储类型，包括了 CHAR 、VARCHAR 、BINARY 、TEXT 等多种字符串类型。

mysql_datatype03

CHAR 和 VARCHAR 类型

CHAR 和 VARCHAR 很类似，都用来保存 MySQL 中较短的字符串。二者的主要区别在于存储方式的不同：CHAR 列的长度固定为创建表时声明的长度，长度可以为从 0 ~ 255 的任何值；而 VARCHAR 列中的值为可变长字符串，长度可以指定为 0 ~ 65536 之间的值。在检索的时候，CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格。

  • 创建表 T5，并定义两个字段 “v VARCHAR(5)” 和 “c CHAR(5)”
mysql> create table t5 (v varchar(5), c char(5));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t5;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| v     | varchar(5) | YES  |     | NULL    |       |
| c     | char(5)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
  • v 列和 c 列同时插入字符串 “ab ”
mysql> insert into t5 values('ab   ', 'ab   ');
Query OK, 1 row affected (0.00 sec)
  • 显示查询结果
mysql> select * from t5;
+-------+------+
| v     | c    |
+-------+------+
| ab    | ab   |
+-------+------+
1 row in set (0.00 sec)

mysql> select length(v), length(c) from t5;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         5 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)

从上述结果可知，CHAR 列最后的空格在查询时都已经被删除，而 VARCHAR 依然保留空格。

BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。

  • 创建表 T6，字段为 “c BINARY(3)”
mysql> create table t6 (c binary(3));
Query OK, 0 rows affected (0.02 sec)
  • 往字段 c 中插入字符 “a”
mysql> insert into t6 set c = 'a';
Query OK, 1 row affected (0.01 sec)
  • 分别用以下几种模式来查看 c 列的内容
mysql> select *, hex(c), c='a', c='a\0', c='a\0\0' from t6;
+------+--------+-------+---------+-----------+
| c    | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a    | 610000 |     0 |       0 |         1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)

从上述结果可知，当保存 BINARY 值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义长度。对于一个 BINARY(3) 列，当插入数据“a”时，会变为“a\0\0”。

ENUM 类型

ENUM 中文名称叫枚举类型，它的值范围需要在创建表时通过指定枚举值方式显示指定。对 1 ~ 255 个成员的枚举需要 1 个字节存储；对于 255 ~ 65536 个成员，需要 2 个字节存储，最多允许有 65536 个成员。

  • 创建表 T7，定义 gender 字段为枚举类型，成员为 “M” 和 “F”
mysql> create table t7 (gender enum('M', 'F'));
Query OK, 0 rows affected (0.02 sec)
  • 插入 4 条不同的记录
mysql> insert into t7 values('M'), ('1'), ('f'), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t7;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| NULL   |
+--------+
4 rows in set (0.00 sec)

从上述结果可知，ENUM 类型时忽略大小写的，在存储 'M' 、'f' 时将它们都转成了大写，还可以看出对于插入不在 ENUM 指定范围内的值时，并没有返回警告，而是插入了 enum('M', 'F') 的第一个值 'M'。

另外，ENUM 类型只允许从值集合中选取单个值，而不能一次取多个值。

SET 类型

SET 和 ENUM 类型非常类似，也是一个字符串对象，里面可以包含 0 ~ 64 个成员。根据成员的不同，存储上也有所不同。

  • 1 ~ 8 成员的集合，占 1 个字节
  • 9 ~ 16 成员的集合，占 2 个字节
  • 17 ~ 24 成员的集合，占 3 个字节
  • 25 ~ 32 成员的集合，占 4 个字节
  • 33 ~ 64 成员的集合，占 8 个字节

SET 和 ENUM 除了存储之外，最主要的区别在于 SET 类型一次可以选取多个成员，而 ENUM 只能选取一个。

  • 创建表 T8
mysql> create table t8 (col set('a','b', 'c', 'd'));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t8;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| col   | set('a','b','c','d') | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • 插入多组不同的成员
mysql> insert into t8 values ('a,b'), ('a,d,a'), ('a,c'), ('a');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t8;
+------+
| col  |
+------+
| a,b  |
| a,d  |
| a,c  |
| a    |
+------+
4 rows in set (0.00 sec)

SET 类型可以从允许值集合中选择任意 1 个或多个元素进行组合，所以对于输入的值只要是在允许值的范围内，都可以正确地注入到 SET 类型的列中。

对于超出允许值范围的值，例如 ('a,d,f') 将不允许注入到上述例子设置的 SET 类型列中，而对于 ('a,d,a') 这样包含重复成员的集合将只取一次，写入后的结果为“a,d”。

参考资料

《深入浅出 MySQL 数据库开发 第2版 -- 唐汉明》

原创文章，转载请注明出处：http://www.opcoder.cn/article/50/