MySql表设计规范和技巧

前言

在日常项目中，无论是系统初始架构设计还是系统上线后功能迭代， 无论是基于DDD领域驱动设计还是基于MVC的分层架构设计，最终都离不开数据建模，数据建模完成后转化为不同的数据表，此篇聊一下建表的一些规范和技巧。

必要字段

create_date_time、create_name、modify_date_time、modify_name 为必要字段，保存数据的基本属性，使其可追溯

is_delete 必须字段，配合MybatisPlus逻辑删除功能使用，标识数据逻辑删除，生产数据最好都不要物理删除，保留完整的现场，某些场景需要恢复已删除的数据也更简单

预留字段

description、type、state、label、sorting、version为预留字段

type：数据类型

state：状态

label：标签

sorting：排序

version：乐观锁字段，配合MybatisPlus 乐观锁功能使用或者自定义使用

基础字段封装

针对这些通用字段，可以封装在一个实体类中，给其他实体类继承，避免每个Entity冗余生成

@Getter
@Setter
public class EntityBase implements Serializable {

    @ApiModelProperty(value = "序列")
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;

    @ApiModelProperty(value = "说明")
    private String description;

    @ApiModelProperty(value = "创建时间")
    @TableField(fill = FieldFill.INSERT)
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime createDateTime;

    @ApiModelProperty(value = "创建人")
    @TableField(fill = FieldFill.INSERT)
    private String createName;

    @ApiModelProperty(value = "修改时间")
    @TableField(fill = FieldFill.INSERT_UPDATE)
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime modifyDateTime;

    @ApiModelProperty(value = "修改人")
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private String modifyName;

    @ApiModelProperty(value = "删除状态（0：未删除，1：删除）")
    @TableLogic
    private Boolean isDelete;

    @ApiModelProperty(value = "排序")
    private Integer sorting;

    @Version
    @ApiModelProperty(hidden = true,value = "版本号")
    private Integer version;

}

@Data
@Accessors(chain = true)
@TableName("`area`")
@ApiModel(value="Area对象", description="区表")
public class Area extends EntityBase {

    @ApiModelProperty(value = "市代码")
    private String cityCode;

    @ApiModelProperty(value = "名称")
    private String name;

    @ApiModelProperty(value = "区编码")
    private String areaCode;

    @ApiModelProperty(value = "类型")
    private Integer type;

    @ApiModelProperty(value = "状态")
    private Integer state;

    @ApiModelProperty(value = "标签")
    private String label;
}

时间，操作人字段自动填充

通过实现MybatisPlus的MetaObjectHandler接口，实现通用字段自动填充，避免每次手动设值。

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Resource
    private GetUserInfo getUserInfo;

    @Override
    public void insertFill(MetaObject metaObject) {
        //第一个对应实体属性名, 第二个参数需要填充的值
        setFieldValByName("createDateTime", LocalDateTime.now(), metaObject);
        setFieldValByName("modifyDateTime", LocalDateTime.now(), metaObject);

        String currUserInfo = null;
        try {
            currUserInfo = getUserInfo.getCurrUserInfo();
        } catch (Exception e) {
        }
        currUserInfo = StringUtils.isNotBlank(currUserInfo) ? currUserInfo : "sys";
        setFieldValByName("createName", currUserInfo, metaObject);
        setFieldValByName("modifyName", currUserInfo, metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        //第一个对应实体属性名, 第二个参数需要填充的值
        setFieldValByName("modifyDateTime", LocalDateTime.now(), metaObject);
        String currUserInfo = null;
        try {
            currUserInfo = getUserInfo.getCurrUserInfo();
        } catch (Exception e) {
        }
        currUserInfo = StringUtils.isNotBlank(currUserInfo) ? currUserInfo : "sys";
        setFieldValByName("modifyName", currUserInfo, metaObject);
    }
}

public interface GetUserInfo {

    /**
     * 获取当前用户名
     */
    String getCurrUserInfo();


    /**
     * 获取登陆用户名
     */
    String getLoginName();
}

表设计常见问题

字段类型选择？

• 常见的一些状态位字段，使用tinyint类型更省空间

• 布尔字段使用bit类型，默认值使用b'0'

• 如果是大型长期维护系统，主键使用bigint类型， 主键值利用雪花算法生成

• 金额等浮点数据统一用decimal类型，小数点一般保留2位

什么时候需要中间表？

当两张表业务数据存在一对多，多对多的映射关系，适合新增一张中间表进行关联

什么时候适合冗余其他表数据？

该表查询需求较多，且需要关联查询关联表数据，关联表数据更新频率相对较低

一般的经验是，处于业务流程下游的表可以冗余上游的表数据，越是上游越适合被冗余，因为这个时候前面的流程基本已经确定，不太会修改。

总结

以上总结了一下MySql表设计的一些经验，关于CRUD代码生成可看