tulip notes
EffectTang
2024-10-21
目录

表设计实战-自增、资金与字符

# 表设计实战-自增、资金与字符

# 整型类型与自增设计

在真实业务场景中，整型类型最常见的就是在业务中用来表示某件物品的数量。例如上述表的销售数量，或电商中的库存数量、购买次数等。在业务中，整型类型的另一个常见且重要的使用用法是作为表的主键，即用来唯一标识一行数据。

整型结合属性 auto_increment，可以实现自增功能，但在表结构设计时用自增做主键，希望你特别要注意以下两点，若不注意，可能会对业务造成灾难性的打击：

  • 用 BIGINT 做主键，而不是 INT；
  • 自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前）。
CREATE TABLE User (

  userId INT AUTO_INCREMENT PRIMARY KEY,
  money DECIMAL(8,2) NOT NULL,
  
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1
2
3
4
5
6

在 MySQL 8.0 版本之前，自增值并不是立即持久化的。这意味着在某些情况下，自增值可能会因为事务回滚或数据库崩溃等原因而回溯（即回退到之前的值）。具体来说：

事务回滚：如果在一个事务中插入了一条记录，但是该事务最终被回滚，那么自增值并不会恢复到回滚前的状态。这意味着自增值会跳过已回滚的记录所分配的值。

数据库崩溃：如果数据库在插入新记录后突然崩溃，自增值可能已经被更新，但尚未持久化到磁盘(比如：自增后，下一序列为4)。在这种情况下，重启数据库后，自增值可能会回溯到崩溃前的状态（比如：下一序列又变为3）。

INT 的范围最大在 42 亿的级别，在真实的互联网业务场景的应用中，很容易达到最大值。例如一些流水表、日志表，每天 1000W 数据量，420 天后，INT 类型的上限即可达到。

因此，用自增整型做主键，一律使用 BIGINT，而不是 INT。不要为了节省 4 个字节使用 INT，当达到上限时，再进行表结构的变更，将是巨大的负担与痛苦。

在 Java 中，与 MySQL 的 BIGINT 类型对应的 Java 类型主要有两种：long 和 BigInteger。

# 主键建议

为了之后更好的分布式架构扩展性，不建议使用整型类型做主键，更为推荐的是字符串类型。此外，还要一点要注意：当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误。因此，不建议用整型做主键。

# 资金字段设计

通常来说，金融字段使用DECIMAL类型，因为这样可以精确到分，比如：DECIMAL(8,2),

create table Order (
	orderId char(12) not null,
  money decimal(8,2) not null,
  .....
)
1
2
3
4
5

但实际中，在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为 整型类型。也就是说，资金类型更推荐使用用分单位存储，而不是用元单位存储。

另外，类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用 BIG INT 来存储金额相关的字段。

字段存储时采用分存储，即便这样 BIG INT 也能存储千兆级别的金额。这里，1兆 = 1万亿。而关于小数部分的展示，交给前端即可。存储仍存储为分。

# 字符串类型

CHAR(N) 用来保存固定长度的字符，N 的范围是 0 ~ 255，请牢记，N 表示的是字符，而不是字节。VARCHAR(N) 用来保存变长字符，N 的范围为 0 ~ 65536， N 表示字符。

在超出 65536 个字符的情况下，可以考虑使用更大的字符类型 TEXT 或 BLOB，两者最大存储长度为 4G，其区别是 BLOB 没有字符集属性，纯属二进制存储。

# 字符集

在表结构设计中，除了将列定义为 CHAR 和 VARCHAR 用以存储字符以外，还需要额外定义字符对应的字符集，因为每种字符在不同字符集编码下，对应着不同的二进制值。常见的字符集有 GBK、UTF8，通常推荐把默认字符集设置为 UTF8。

而且随着移动互联网的飞速发展，推荐把 MySQL 的默认字符集（charset）设置为 UTF8MB4，否则，某些 emoji 表情字符无法在 UTF8 字符集下存储，比如 emoji 笑脸表情，对应的字符编码为 0xF09F988E：

包括 MySQL 8.0 版本在内，字符集默认设置成 UTF8MB4，8.0 版本之前默认的字符集为 Latin1。

鉴于目前默认字符集推荐设置为 UTF8MB4，所以在表结构设计时，可以把 CHAR 全部用 VARCHAR 替换，底层存储的本质实现一模一样。

# 排序规则

排序规则（Collation）是比较和排序字符串的一种规则，每个字符集都会有默认的排序规则，你可以用命令 SHOW CHARSET 来查看：

mysql> SHOW CHARSET LIKE 'utf8%';

+---------+---------------+--------------------+--------+

| Charset | Description   | Default collation  | Maxlen |

+---------+---------------+--------------------+--------+

| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci |      4 |

+---------+---------------+--------------------+--------+

mysql> SHOW COLLATION LIKE 'utf8mb4%';

+----------------------------+---------+-----+---------+----------+---------+---------------+

| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |

+----------------------------+---------+-----+---------+----------+---------+---------------+

| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |

| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |

| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |

| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |

| utf8mb4_bin                | utf8mb4 |  46 |         | Yes      |       1 | PAD SPACE     |

......
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

排序规则以 _ci 结尾，表示不区分大小写（Case Insentive），_cs 表示大小写敏感，_bin 表示通过存储字符的二进制进行比较。需要注意的是，比较 MySQL 字符串，默认采用不区分大小的排序规则。

牢记，绝大部分业务的表结构设计无须设置排序规则为大小写敏感！除非你能明白你的业务真正需要。

# 修改字符集

ALTER TABLE emoji_test CHARSET utf8mb4;
1

其实，上述修改只是将表的字符集修改为 UTF8MB4，下次新增列时，若不显式地指定字符集，新列的字符集会变更为 UTF8MB4，但对于已经存在的列，其默认字符集并不做修改

正确修改列字符集的命令应该使用 ALTER TABLE … CONVERT TO…这样才能将之前的列 a 字符集从 UTF8 修改为 UTF8MB4：

mysql> ALTER TABLE emoji_test CONVERT TO CHARSET utf8mb4;

Query OK, 0 rows affected (0.34 sec)
1
2
3

# 性别字段设计

设计表结构时，你会遇到一些固定选项值的字段。例如，性别字段（Sex），只有男或女；又或者状态字段（State），有效的值为运行、停止、重启等有限状态。

我观察后发现，大多数开发人员喜欢用 INT 的数字类型去存储性别字段，比如：

CREATE TABLE `User` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `sex` tinyint DEFAULT NULL,

  ......

  PRIMARY KEY (`id`)

) ENGINE=InnoDB；
1
2
3
4
5
6
7
8
9
10
11

其中，tinyint 列 sex 表示用户性别，但这样设计问题比较明显。

  • 表达不清：在具体存储时，0 表示女，还是 1 表示女呢？每个业务可能有不同的潜规则；
  • 脏数据：因为是 tinyint，因此除了 0 和 1，用户完全可以插入 2、3、4 这样的数值，最终表中存在无效数据的可能，后期再进行清理，代价就非常大了。

# ENUM跟check

这里可以尝试，MySQL 中的ENUM 类型，它是用于存储一组固定的字符串值中的一个。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('PENDING', 'SHIPPED', 'DELIVERED', 'CANCELLED') NOT NULL
);
1
2
3
4

在 Java 中，可以将 status 字段映射为 String 类型

public class Order {
    private int id;
    private String status; // MySQL ENUM 类型映射为 Java String 类型
    
    // Getter and Setter methods
  	// ......
}

// 使用示例
public class Main {
    public static void main(String[] args) {
        Order order = new Order();
        order.setStatus("SHIPPED");
        System.out.println(order.getStatus()); // 输出 "SHIPPED"
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

此外还可以给性别字段加上约束。在 MySQL 8.0 及更高版本中，CHECK 约束已经被正式支持，并且可以在创建表时定义约束条件。(MySQL 8.0 之前的版本并没有提供约束功能)

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10, 2) NOT NULL,
    status ENUM('PENDING', 'SHIPPED', 'DELIVERED', 'CANCELLED') NOT NULL,
    CHECK (amount >= 0),
    CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
);
1
2
3
4
5
6
7

在这个例子中，amount 列的值必须大于等于 0，并且 status 列的值必须是预定义的枚举值之一。如果插入以外的值，则会报错。当然，你也可以在现有表中添加 CHECK 约束：

ALTER TABLE orders
ADD CONSTRAINT check_amount CHECK (amount >= 0);
1
2

但这也会有问题，如果状态字段在后续会扩展，修改表时影响是否很大,欢迎自行搜索。

# 账户密码存储设计

切记，在数据库表结构设计时，千万不要直接在数据库表中直接存储密码，一旦有恶意用户进入到系统，则面临用户数据泄露的极大风险。比如金融行业，从合规性角度看，所有用户隐私字段都需要加密，甚至业务自己都无法知道用户存储的信息（隐私数据如登录密码、手机、信用卡信息等）。

相信不少开发开发同学会通过函数 MD5 加密存储隐私数据，这没有错，因为 MD5 算法并不可逆。然而，MD5 加密后的值是固定的，如密码 12345678，它对应的 MD5 固定值即为 25d55ad283aa400af464c76d713c07ad。

因此，可以对 MD5 进行暴力破解，计算出所有可能的字符串对应的 MD5 值。

所以，在设计密码存储使用，还需要加盐（salt），每个公司的盐值都是不同的，因此计算出的值也是不同的。但盐值，切记不能取固定值，因为如果一个秘密被破解，那对应盐值也会被知晓，仍存在被暴力破解的可能。

所以一个真正好的密码存储设计，应该是：动态盐 + 非固定加密算法。

我比较推荐这么设计密码，列 password 存储的格式如下：

$salt$cryption_algorithm$value
1

# 总结

以下是一个例子

CREATE TABLE User (

    id BIGINT NOT NULL AUTO_INCREMENT,

    name VARCHAR(255) NOT NULL,

    sex CHAR(1) NOT NULL,

    password VARCHAR(1024) NOT NULL,

    regDate DATETIME NOT NULL,

    CHECK (sex = 'M' OR sex = 'F'),

    PRIMARY KEY(id)

);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

MySQL 8.0 之前的版本并没有提供约束功能。自 MySQL 8.0.16 版本开始，数据库原生提供 CHECK 约束功能，可以方便地进行有限状态列类型的设计。

当sex字段插入，M、F以外的字段时，会报错。

上次更新: 2025/04/23, 16:23:16
MySql的事务4个隔离级别
表设计-时间类型选择

← 表设计-时间类型选择→

最近更新
01
面向切面跟自定义注解的结合
05-22
02
时间跟其他数据的序列化
05-19
03
数据加密与安全
05-17
更多文章>
Theme by Vdoing | Copyright © 2023-2025 EffectTang