MySQL 基本数据字段设计

前言

在使用 Spring Boot 进行开发时，数据库表的字段设计对于系统的稳定性、扩展性和性能至关重要。以下是 MySQL 数据字段设计的一些关键原则、类型选择和注意事项，旨在帮助你在实际开发中设计符合企业要求的数据库表结构和实体类字段。

1. 数据库字段设计原则

1.1 数据类型的选择

• 选择合适的数据类型：使用尽量小的数据类型以节省存储空间。例如，对于整数型数据，如果值范围不大，可以使用 tinyint、smallint 代替 int 或 bigint。
• 避免使用过大的数据类型：例如，文本数据应使用 varchar 而非 text，并指定合理的长度。
• 统一的数据类型：对于同样用途的字段（如 ID），在不同表中使用一致的数据类型和长度，方便后期维护和扩展。

1.2 表字段命名规范

• 驼峰转下划线：在数据库表字段命名中，推荐使用下划线分隔的命名风格（如 user_name），而在实体类中使用驼峰命名（如 userName）。
• 避免保留字：表字段名避免使用 MySQL 的保留字，如 key、order、group 等。
• 简洁清晰：字段名应表达字段含义，避免模糊命名。尽量保持字段名简短，同时保持可读性。

1.3 字段的默认值与约束

• 非空字段设置默认值：对于不能为 null 的字段，推荐设置合理的默认值（如布尔类型设置为 false，数值类型设置为 0）。
• 使用唯一约束：对于需要唯一性的字段（如用户名、邮箱等），使用 unique 约束。
• 使用合理的长度约束：例如，手机号字段可使用 varchar(11)，防止意外的过长输入。

2. 常见字段类型选择

2.1 数值类型

• tinyint（1 字节）：用于存储很小的整数，取值范围为 -128 到 127，无符号时为 0 到 255。常用于布尔值（tinyint(1)）。

  • Spring Boot 实体映射：private Byte isActive;

• smallint（2 字节）：用于存储较小的整数，取值范围为 -32,768 到 32,767，无符号时为 0 到 65,535。

  • Spring Boot 实体映射：private Short age;

• int（4 字节）：常用的整数类型，取值范围为 -2,147,483,648 到 2,147,483,647，无符号时为 0 到 4,294,967,295。

  • Spring Boot 实体映射：private Integer quantity;

• bigint（8 字节）：用于存储大整数，取值范围为 -2^63 到 2^63-1，无符号时为 0 到 2^64-1。

  • Spring Boot 实体映射：private Long totalSales;

2.2 字符串类型

• varchar(n)：可变长度字符串，n 表示最大字符数。通常用于存储用户输入的数据，如姓名、地址、邮箱等。

  • Spring Boot 实体映射：private String userName;

• char(n)：固定长度字符串，适用于长度固定的值，如性别、状态码等。

  • Spring Boot 实体映射：private String gender;

• text：长文本，适用于存储大量文本数据，如评论、描述等。

  • Spring Boot 实体映射：private String description;

2.3 日期和时间类型

• datetime：用于存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS。常用于记录创建时间、更新时间等。

  • Spring Boot 实体映射：private LocalDateTime createdTime;

• date：仅存储日期，格式为 YYYY-MM-DD。适用于生日、重要日期等。

  • Spring Boot 实体映射：private LocalDate birthDate;

• timestamp：与 datetime 类似，但带有时区信息，且通常用于记录数据变更时间。

  • Spring Boot 实体映射：private Instant lastModifiedTime;

2.4 布尔类型

• tinyint(1)：MySQL 中没有原生的布尔类型，通常使用 tinyint(1) 来表示布尔值，0 为 false，1 为 true。
  • Spring Boot 实体映射：private Boolean isEnabled;

2.5 JSON 数据类型

• json：适用于存储 JSON 格式的数据，常用于配置项、动态属性等。
  • Spring Boot 实体映射：private String settings;

3. 表字段设计的最佳实践

3.1 主键设计

• 自增主键：通常使用 bigint 类型作为自增主键，确保唯一性和可扩展性。
  • 示例：

    id bigint(20) unsigned NOT NULL AUTO_INCREMENT;
    

• UUID 作为主键：在分布式系统中，推荐使用 UUID 作为主键，通过 char(36) 存储。
  • 示例：

    id char(36) NOT NULL;
    

3.2 外键设计

• 外键约束：外键通常使用与主键相同的数据类型，并添加 foreign key 约束，以确保数据完整性。
  • 示例：

    dept_id bigint(20) unsigned NOT NULL,
    foreign key (dept_id) references departments(id);
    

3.3 索引设计

• 唯一索引：对于需要唯一性的字段（如用户名、邮箱），建议创建唯一索引。

  • 示例：

    create unique index idx_user_email on users(email);
    

• 联合索引：对于经常一起查询的多个字段，建议创建联合索引，以提高查询效率。

  • 示例：

    create index idx_user_name_age on users(name, age);
    

3.4 表字段设计中的其他注意事项

• 字符集和排序规则：默认使用 utf8mb4 编码，确保支持多语言字符集，排序规则选择 utf8mb4_unicode_ci。
• 字段长度设置：根据实际需求合理设置 varchar 等字段的长度，避免浪费空间。

4. 在 Spring Boot 实体类中的映射示例

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_name", length = 50, nullable = false)
    private String userName;

    @Column(name = "email", unique = true, length = 100, nullable = false)
    private String email;

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

    @Column(name = "created_time", nullable = false)
    private LocalDateTime createdTime;

    @Column(name = "is_enabled", nullable = false)
    private Boolean isEnabled;

    // Getters and Setters
}