MySQL 特殊字段的设计

前言

在企业级开发中，除了常见的基本字段类型外，涉及金额、百分比、状态等特殊字段的设计也非常重要。这些字段在设计时需要特别注意数据精度、性能、规范性等问题。

1. 金额字段设计

1.1 数据类型选择

在数据库中存储金额时，推荐使用 decimal 类型，而不是浮点数（如 float、double），以避免精度问题。

• decimal(m, d)：其中 m 表示总位数，d 表示小数位数。适用于存储精确的数值，例如金额、利率等。
  • 设计示例：decimal(18, 2) 可以存储最大 16 位整数和 2 位小数，适用于存储金额。

price decimal(18, 2) not null;

1.2 Spring Boot 实体类映射

在 Spring Boot 实体类中，金额字段通常映射为 BigDecimal 类型，以确保精度的准确性。

@Column(name = "price", precision = 18, scale = 2, nullable = false)
private BigDecimal price;

• precision：表示总位数。
• scale：表示小数位数。

1.3 注意事项

• 避免使用浮点数：浮点数容易产生精度丢失问题，不适合存储精确的金额数据。
• 设置合理的长度：根据业务需求，确定 decimal 的精度和小数位数，避免空间浪费。

2. 百分比和比率字段设计

2.1 数据类型选择

百分比和比率数据可以采用 decimal 或 double 类型存储，通常保留两位小数。

• decimal(m, d)：适用于需要高精度的百分比和比率数据。
  • 设计示例：decimal(5, 2) 可以表示 100.00% 的数据。

discount_rate decimal(5, 2) not null;

2.2 Spring Boot 实体类映射

在 Spring Boot 中，可以映射为 BigDecimal 或 Double 类型，推荐使用 BigDecimal 以确保精度。

@Column(name = "discount_rate", precision = 5, scale = 2, nullable = false)
private BigDecimal discountRate;

3. 状态字段设计

3.1 数据类型选择

状态字段通常表示记录的状态，如启用、禁用、审核状态等。可以使用 tinyint 或 enum 类型。

• tinyint：适用于有限的状态值，如 0 表示禁用，1 表示启用。
  • 设计示例：tinyint(1) 表示布尔值或状态。

status tinyint(1) not null default 0;

• enum：适用于具有明确状态集合的字段，例如订单状态（pending、shipped、delivered 等）。

order_status enum('pending', 'shipped', 'delivered') not null;

3.2 Spring Boot 实体类映射

对于使用 tinyint 表示的状态字段，通常映射为 Boolean 或 Integer 类型；对于 enum，可以映射为 String 或枚举类。

@Column(name = "status", nullable = false)
private Integer status;

// 如果使用枚举
@Column(name = "order_status", nullable = false)
@Enumerated(EnumType.STRING)
private OrderStatus orderStatus;

public enum OrderStatus {
    PENDING, SHIPPED, DELIVERED
}

4. 计数器和序列号设计

4.1 数据类型选择

计数器、序列号等通常使用 int 或 bigint 类型，根据数据增长的需求选择合适的长度。

• int：适用于不超过 21 亿的数据范围，适合一般的计数器或序列号。
• bigint：适用于需要处理大数据量的情况。

sequence_number bigint(20) unsigned not null auto_increment;

4.2 Spring Boot 实体类映射

计数器和序列号通常映射为 Long 或 Integer 类型。

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "sequence_number", nullable = false)
private Long sequenceNumber;

5. 日期和时间字段设计

5.1 数据类型选择

日期和时间字段设计中常用的类型包括：

• datetime：适用于记录精确的日期和时间，例如订单创建时间、更新时间等。
• date：适用于仅记录日期，例如生日、事件日期等。
• timestamp：通常用于自动记录数据的创建或更新时的时间戳，具备自动更新的特性。

created_at datetime not null default current_timestamp;
updated_at timestamp not null default current_timestamp on update current_timestamp;

5.2 Spring Boot 实体类映射

日期和时间字段在 Spring Boot 中通常映射为 LocalDate、LocalDateTime 或 Instant 类型。

@Column(name = "created_at", nullable = false)
private LocalDateTime createdAt;

@Column(name = "updated_at", nullable = false)
private Instant updatedAt;

6. IP 地址和网络相关字段设计

6.1 数据类型选择

IP 地址可以使用 varchar 或 inet 类型（取决于数据库版本），通常建议使用 varchar 以便兼容 IPv4 和 IPv6。

ip_address varchar(45) not null;

6.2 Spring Boot 实体类映射

IP 地址字段在 Spring Boot 中通常映射为 String 类型。

@Column(name = "ip_address", length = 45, nullable = false)
private String ipAddress;

7. JSON 和动态数据字段设计

7.1 数据类型选择

对于需要存储 JSON 格式的数据，MySQL 提供了原生的 json 类型。

settings json not null;

7.2 Spring Boot 实体类映射

在 Spring Boot 中，JSON 字段通常映射为 String，并在需要时进行序列化和反序列化处理。

@Column(name = "settings", columnDefinition = "json")
private String settings;