mysql 提供了丰富的数据类型,大致可以分为数值类型、字符类型、日期时间类型、JSON 类型、数据空间类型,设计表时选择"合适的"数据类型可以有效的节省存储空间、提升查询效率。

1.数值类型 ​

在 Mysql 中数值类型又可以细分为整数类型、浮点数类型、定点数类型、位类型类型四种。

1.1 整数类型 ​

• 存储非负数值时,必须用 unsigned 修饰。
• tinyint 类型一般存储小数值或枚举值,例如存储 0 为男,1 为女,tinyint 相较于比 Enum 类型所占空间更少,因此查询效率更高。
• 使用 int 类型存储 IPv4 地址。如果忽略可读性推荐使用 int 类型存储 IPv4 的地址,IPv4 本质上是一个无符号 32 位的数值,IPv4 的可选范围是 0.0.0.0 ～ 255.255.255.255,用逗号隔开每个数的取值范围为 0-256,也就是 256 个数,256 是 2 的 8 次方,用二进制表示 8 位,IPv4 由 4 个数字组成,所以存储 IPv4 地址最多是 32 位,按照字节二进制转换,1Byte 等于 8 位二进制,因此存储 IPv4 地址最多需要 4 字节(Byte)。相较于 varchar 类型使用 int 存储 IPv4 地址所占用的空间更少,而且范围查询效率也更高(因为存储的是数值)。使用 int 存储 IP 地址虽然可读性差,但是 Mysql 提供了 INET_ATON、INET_NTOA 两个函数用于 IP 地址与无符号数值的转换。

use test;
 create table ip(ip int unsigned not null);
 # INET_ATON()函数用于将字符串ip地址转换为32位无符号整数
 # INET_NTOA()函数用于将32位无符号整数的IP地址转为字符串
 insert into ip values(INET_ATON('192.168.110.100'));
 select * from ip; # 查询结果为:3232263780
 select INET_NTOA(ip) from ip; // 查询结果为:'192.168.110.100'

• 推荐使用 bigint 做为大数据量数值列的存储类型,例如主键 id,随着时间和业务量的增加,数据不断积累膨胀,数据量可能超过亿级甚至更大规模,此时使用 int 类型远远满足不了业务需求。对于数据量或未来可能产生大量数据场景下,推荐使用 bigint 存储数据。bigint 另一个使用场景是存储大额度金额,由于 Mysql 的 decimal 类型存储精度丢失问题,使用 bigint 存储金额可以避免精度丢失。

1.2 浮点数类型 ​

在 Mysql 浮点数分为 FLOAT、DOUBLE 类型表示近似数值数据值,FLOAT 是一种单精度浮点数类型,它占用 4 个字节(32 位),可用于存储大约 7 位有效数字。 DOUBLE 是一种双精度浮点数类型,占用 8 字节,最多存储大约 15 位有效数字。在 SQL 标准语法下浮点数语法为类型(p),其中 p 表示精度值,仅用于确定存储大小,例如 FLOAT(4)表示存储一个单精度浮点数,其中精度参数为 4,DOUBLE(8)表示存储一个双精度浮点数,其中精度参数为 8。MySQL 除了使用 SQL 标准语法外,也支持非标准语法(从 MySQL8.0.17 开始,不推荐使用非标准语法),非标准语法浮点数语法为类型(M,D),其中 M 表示浮点数的总位数(范围值为 1 到 24),D 表示浮点数小数点后的位数(范围值为 0 到 23,且不能大于总位数 M),例如 FLOAT(8,2) 表示总共有 8 位数字,其中 2 位是小数点后的位数,即小数点前有 6 位。由于浮点数用于表示近似数值数据值,浮点数存储和计算时可能会出现精度丢失,因此,对于有精度要求的场景下推荐使用定点数类型。

1.3 定点数类型 ​

在 MySQL 中,DECIMAL 是一种用于存储精确小数的数据类型,适用于需要精确小数表示的场景,例如存储金额、百分比计算等场景。它用于表示固定点数,即指定了小数点前后的位数。相较于 FLOAT 和 DOUBLE 浮点数类型不同,后者是近似数值类型,可能存在精度损失。DECIMAL 类型的语法为DECIMAL(M, D),其中 M 是总位数,D 是小数点后的位数,例如 DECIMAL(10,2)表示表示总共有 10 位数字,其中有 2 位是小数点后的位数。

drop TABLE if exists t1;
CREATE TABLE t1 (
f1 DECIMAL(10, 2)
);
INSERT INTO t1(f1) VALUES (1111.111);
SELECT f1 FROM t1; # f1的结果值为 1111.11,超过小数点的位数会被丢失

1.4 位类型类型 ​

在 MySQL 中,BIT(M) 是用于存储位字段(bit field)的数据类型。BIT(M)表示存储一个二进制字符串,其中 M 表示位的数量,范围是从 1 到 64,如果为长度小于 M 位的列赋值,则该值将在左侧填充零。BIT 类型常用于存储标志或布尔值,可以有效地压缩存储。Mysql 提供了多个 BIT 相关函数,用于处理和操作二进制数据,常见函数如下:

• BIT_COUNT:用于计算一个二进制字符串或数字中设置为 1 的位数。
• BIT_AND:用于计算两个二进制字符串或数字的按位与操作。
• BIT_OR:用于计算两个二进制字符串或数字的按位或操作。
• BIT_XOR:用于计算两个二进制字符串或数字的按位异或操作。
• BIT_NOT:用于计算一个二进制字符串或数字的按位非操作。

CREATE TABLE t1 (
    bit_field1 BIT(8)
);
INSERT INTO t1(bit_field1) VALUES (B'10101010');
# 使用BIN函数查询bit_field字段转换为字符串显示
SELECT BIN(bit_field) FROM t1;

2.字符类型 ​

2.1 CHAR & VARCHAR ​

2.2 BINARY & VARBINARY ​

2.3 BLOB & TEXT ​

2.4 ENUM ​

ENUM(枚举)是 MySQL 中用于存储一个有限集合值的数据类型,适用于具有静态、固定和相对简单选项集合的场景。它允许定义一组可能的值,然后在表中使用该类型的列来存储其中的一个值,ENUM 类型将每个可能的值映射到整数,然后在数据库中存储该整数,因此,相较于 SET 类型更紧凑,而且 ENUM 类型的值是有序的,但在存储和检索时并不关心其顺序,插入时的顺序可能不会影响查询时的顺序。

CREATE TABLE t1 (
   f1 ENUM('Option1', 'Option2', 'Option3')
);
INSERT INTO t1(f1) VALUES ('Option2');
SELECT * FROM t1;

2.5 SET ​

SET 类型在 MySQL 中用于存储一个或多个由逗号分隔的字符串值,SET 类型适用于具有有限且相对静态的选项的场景,例如一些配置项或状态标志。

3.日期时间类型 ​

Mysql 提供了多种时间类型,用于存储不同精度和格式的日期和时间信息:

• 对处理时区或存储对存储空间敏感的场景下推荐使用 TIMESTAMP,相较于 DATETIME 而言,TIMESTAMP 支持时区,可以根据服务器时区自动调整存储的时间值,而且 TIMESTAMP 所占用的存储空间更小。
• 对时区无需求或存储空间不敏感的场景下推荐使用 DATETIME,虽然 DATETIME 占用空间比 TIMESTAMP 大,但是可读性更好,而且与时区无关的,它存储的日期和时间信息不受服务器时区设置的影响,这意味着在不同时区的服务器上,相同的 DATETIME 值表示相同的实际时间。由于 TIMESTAMP 存储值范围限制,可能会造成时间戳溢出,无法存储更广泛的时间范围。
• 创建表时推荐使用默认值来为日期列提供合理的初始值,以避免插入时的错误。
• 避免在日期列中使用零日期(如 '0000-00-00')这可能会导致混淆和不一致性。使用默认值或 NULL 来表示未知或不适用的日期。

4.JSON 数据类型 ​

MySQL 5.7 版本引入了对 JSON 数据类型的支持,并在 MySQL 8.0 版本进行了改进。JSON 类型允许在 MySQL 中存储和操作 JSON 格式的数据,JSON 类型的引入使得 MySQL 更加灵活地处理和查询 JSON 数据,尤其在与 NoSQL 数据库或需要存储和检索复杂的 JSON 结构的应用程序中,提供了更丰富的功能。

5.空间数据类型 ​