数据库设计采用行扩展，实现系统设置功能

一、平时我们实现系统设置功能的方式

在我们开发的系统功能中，大部分都有设置功能，比如APP端的用户推送开关设置，平时我们是这样设计的：

public class AppSettingPush {

    @ApiModelProperty(value = "会员ID")
    private Long memberId;

    @ApiModelProperty(value = "获赞推送")
    private Boolean belike;
    
    @ApiModelProperty(value = "评论推送")
    private Boolean com;
    
    @ApiModelProperty(value = "收藏推送")
    private Boolean coll;
    
    @ApiModelProperty(value = "转发推送")
    private Boolean tran;
    
    @ApiModelProperty(value = "关注推送")
    private Boolean focus;
    
    @ApiModelProperty(value = "@我的推送")
    private Boolean at;
}

采用这种方式有一个弊端，那就是将来如果要加一个推送设置开关，比如聊天推送，就需要增加字段，修改表结构。另外，如果随着业务功能的增加，还需要增加其他模块设置 ，比如用户空间数据设置（是否显示关注列表、是否显示粉丝列表、是否显示被点赞列表等等），又需要增加一个设置表，且重新需要实现一套CURD代码。

public class AppSettingSpaceData{

    @ApiModelProperty(value = "会员ID")
    private Long memberId;

    @ApiModelProperty(value = "是否显示关注列表")
    private Boolean lfouce;

    @ApiModelProperty(value = "是否显示粉丝列表")
    private Boolean lfans;

    @ApiModelProperty(value = "是否显示被点赞列表")
    private Boolean lbelike;

    @ApiModelProperty(value = "是否显示访客列表")
    private Boolean lvisit;

    @ApiModelProperty(value = "是否显示点赞列表")
    private Boolean llike;

    @ApiModelProperty(value = "是否显示收藏列表")
    private Boolean lcoll;

    @ApiModelProperty(value = "是否显示我评论的动态列表")
    private Boolean lcd;

    @ApiModelProperty(value = "是否显示@我的动态列表")
    private Boolean lad;
}

二、使用行扩展实现通用设置功能

2.1 表结构

CREATE TABLE `t_mem_member_settings` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `member_id` bigint NOT NULL COMMENT '会员id',
  `setting_module` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '设置模块',
  `setting_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '设置类型',
  `setting_value` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '设置值',
  `order_num` int DEFAULT '0' COMMENT '显示排序字段',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `IDX_deleted` (`deleted`) USING BTREE,
  KEY `idx_member_module` (`member_id`,`deleted`,`setting_module`) USING BTREE,
  KEY `idx_member_type` (`member_id`,`deleted`,`setting_type`,`setting_module`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=210391324491786 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 ROW_FORMAT=DYNAMIC COMMENT='会员APP设置'

2.2 实体对象

public class MemberSettings{

    @ApiModelProperty(value = "会员ID")
    private Long memberId;

    @ApiModelProperty(value = "设置模块")
    private MemberSettingModuleEnum settingModule;

    @ApiModelProperty(value = "设置类型")
    private MemberSettingTypeEnum settingType;

    @ApiModelProperty(value = "设置值")
    private String settingValue;

    @ApiModelProperty(value = "显示排序字段")
    private int orderNum;
}

2.3 模块枚举

@Getter
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(description = "会员APP设置模块")
public enum MemberSettingModuleEnum {
    PUSH("PUSH", "推送"),
    SPACE_DATA("SPACE_DATA", "空间数据")
    ;

    @EnumValue
    @JsonValue
    private String code;
    private String name;
}

2.4 设置项枚举

@Getter
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(description = "会员APP设置类型")
public enum MemberSettingTypeEnum {
    PUSH_BE_LIKE("PUSH_BE_LIKE", "获赞推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,1),
    PUSH_COM("PUSH_COM", "评论推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,2),
    PUSH_COLL("PUSH_COLL", "收藏推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,3),
    PUSH_TRAN("PUSH_TRAN", "转发推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,4),
    PUSH_FOCUS("PUSH_FOCUS", "关注推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,5),
    PUSH_AT("PUSH_AT", "@我的推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,6),
    PUSH_IM_CHAT("PUSH_IM_CHAT", "@IM聊天推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,7),
    PUSH_IM_APPLY("PUSH_IM_APPLY", "IM申请推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,8),

    SPACE_DATA_LFOUCE("SPACE_DATA_LFOUCE", "是否显示关注列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,1),
    SPACE_DATA_LFANS("SPACE_DATA_LFANS", "是否显示粉丝列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,2),
    SPACE_DATA_LBELIKE("SPACE_DATA_LBELIKE", "是否显示被点赞列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,3),
    SPACE_DATA_LVISIT("SPACE_DATA_LVISIT", "是否显示访客列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,4),
    SPACE_DATA_LLIKE("SPACE_DATA_LLIKE", "是否显示点赞列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,5),
    SPACE_DATA_LCOLL("SPACE_DATA_LCOLL", "是否显示收藏列表", MemberSettingModuleEnum.SPACE_DATA,"0",Boolean.class,6),
    SPACE_DATA_LCD("SPACE_DATA_LCD", "是否显示我评论的动态列表", MemberSettingModuleEnum.SPACE_DATA,"0",Boolean.class,7),
    SPACE_DATA_LAD("SPACE_DATA_LAD", "是否显示@我的动态列表", MemberSettingModuleEnum.SPACE_DATA,"0",Boolean.class,8);

    @EnumValue
    @JsonValue
    private String code;
    private String name;
    private MemberSettingModuleEnum settingModule;
    private String defaultValue;
    private Class valueType;
    private int orderNum;

    public static List listMemberSetting(MemberSettingModuleEnum module){
        MemberSettingTypeEnum[] arry = MemberSettingTypeEnum.values();
        List list = new ArrayList<>();

        for(MemberSettingTypeEnum item : arry){
            if(item.getSettingModule() == module){
                list.add(item);
            }
        }
        return list;
    }
}

2.5 contoller类

@Slf4j
@Api(tags="会员APP通用设置")
@RestController
@RequestMapping("membersettings")
public class MemberSettingsController{

    @Resource
    private MemberSettingsService memberSettingsService;

    @PutMapping("list")
    @ApiOperation(value="按模块查询会员APP设置数据")
    public Result listMemberSettings(@RequestBody QueryMemberSettingsVO queryVO){
        return Result.success(memberSettingsService.listMemberSettings(queryVO));
    }

    @PutMapping("update")
    @ApiOperation(value="更新设置")
    public Result update(@RequestBody @Valid MemberSettingsUpdateVO updateVO){
        return Result.success(memberSettingsService.update(updateVO));
    }

    @PutMapping("updatebatch")
    @ApiOperation(value="批量更新设置")
    public Result updateBatch(@RequestBody @Valid MemberSettingsUpdateBatchVO updateBatchVO){
        return Result.success(memberSettingsService.updateBatch(updateBatchVO));
    }

    @PostMapping("boolsetting")
    @ApiOperation(value="获取用户Bool类型设置值")
    public Result getMemberBoolSetting(@RequestBody SearchMemberSettingsVO search){
        return Result.success(memberSettingsService.getMemberBoolSetting(search));
    }

    @PostMapping("settingvalue")
    @ApiOperation(value="获取用户设置的Value值")
    public Result getMemberSettingValue(@RequestBody SearchMemberSettingsVO search){
        return Result.success(memberSettingsService.getMemberSettingValue(search));
    }
}

2.6 service实现类

@Slf4j
@Service
public class MemberSettingsServiceImpl implements MemberSettingsService {

    @Override
    public MemberSettingsVO listMemberSettings(QueryMemberSettingsVO queryVO) {
        log.info("listMemberSettings queryVO:{}",queryVO);
        MemberSettingsVO memberSettingsVO = memberSettingsRdsHelper.get(queryVO.getMemberId());

        // Redis没数据,则从数据库取数据
        if (memberSettingsVO == null) {
            memberSettingsVO = new MemberSettingsVO();
            memberSettingsVO.setMemberId(queryVO.getMemberId());

            List dbList = mapper.listMemberSettings(queryVO);
            if (dbList == null || dbList.size() == 0) {
                //数据库无数据，则生成默认记录
                saveDefaultValue(queryVO);
                dbList = mapper.listMemberSettings(queryVO);
            }else{
                // 检查是否有配置新的枚举项
                List typeList = MemberSettingTypeEnum.listMemberSetting(queryVO.getSettingModule());
                if(dbList.size()!=typeList.size()){
                    dbList = dealNewTypes(dbList,typeList,queryVO);
                }
            }

            memberSettingsVO.setSettingItems(dbList);
            memberSettingsRdsHelper.add(memberSettingsVO);
        }else{
            // 检查是否有配置新的枚举项
            List dbList = memberSettingsVO.getSettingItems();
            List typeList = MemberSettingTypeEnum.listMemberSetting(queryVO.getSettingModule());
            if(dbList.size()!=typeList.size()){
                memberSettingsRdsHelper.del(queryVO.getMemberId());

                // 为了防止Redis数据出错，从数据库取数据
                dbList = mapper.listMemberSettings(queryVO);
                dbList = dealNewTypes(dbList,typeList,queryVO);

                memberSettingsVO.setSettingItems(dbList);
                memberSettingsRdsHelper.add(memberSettingsVO);
            }
        }
        return memberSettingsVO;
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean update(MemberSettingsUpdateVO updateVO) {
        memberSettingsRdsHelper.del(updateVO.getMemberId());
        memberSettingsItemRdsHelper.del(updateVO.getMemberId(),updateVO.getSettingType().getCode());
        mapper.update(updateVO);
        return true;
    }

    @Override
    public boolean updateBatch(MemberSettingsUpdateBatchVO updateBatchVO) {
        return updateBatch(updateBatchVO.getUpdateList());
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean updateBatch(List updateVOList) {
        if (updateVOList != null && updateVOList.size() > 0) {
            for (MemberSettingsUpdateVO item : updateVOList) {
                update(item);
            }
        }
        return true;
    }

    @Override
    public boolean insertBatch(List updateVOList) {
        if (updateVOList != null && updateVOList.size() > 0) {
            List entityList = new ArrayList<>();
            for (MemberSettingsUpdateVO item : updateVOList) {
                MemberSettings entity = new MemberSettings();
                BeanUtil.copyProperties(item, entity);

                entity.setOrderNum(entity.getSettingType().getOrderNum());
                entity.setSettingModule(entity.getSettingType().getSettingModule());

                entityList.add(entity);
            }
            saveBatch(entityList);
        }
        return true;
    }

    @Override
    public String getMemberSettingValue(SearchMemberSettingsVO search) {
        MemberSettings memberSettings = memberSettingsItemRdsHelper.get(search.getMemberId(),search.getSettingType());

        // Redis没数据,则从数据库取数据
        if(memberSettings == null){
            memberSettings = mapper.getMemberSettings(search);

            // 数据库没数据，则表示是新配置的枚举项
            if(memberSettings == null){
                MemberSettingTypeEnum item = MemberSettingTypeEnum.valueOf(search.getSettingType());
                if(item!=null){
                    memberSettings =  packageEntity(search.getMemberId(),item);
                    save(memberSettings);
                }
            }

            if(memberSettings!=null){
                memberSettingsItemRdsHelper.add(memberSettings);
            }
        }

        if(memberSettings!=null){
            return memberSettings.getSettingValue();
        }else{
            return null;
        }
    }

    @Override
    public Boolean getMemberBoolSetting(SearchMemberSettingsVO search) {
        Member member = memberService.getById(search.getMemberId());
        if(member == null || member.getForbiddenStatus() == MemberForbiddenStatusEnum.FORBIDDEN){
            return false;
        }
        String value = getMemberSettingValue(search);
        boolean result = "1".equals(value) ? true : false;
        return result;
    }

    /**
     * 生成默认记录
     *
     * @param queryVO
     */
    private void saveDefaultValue(QueryMemberSettingsVO queryVO) {
        List list = MemberSettingTypeEnum.listMemberSetting(queryVO.getSettingModule());
        Long memberId = queryVO.getMemberId();

        List entityList = packageSettings(memberId,list);
        saveBatch(entityList);
    }

    private List packageSettings(Long memberId,List list){
        List entityList = new ArrayList<>();
        for (MemberSettingTypeEnum item : list) {
            MemberSettings entity = packageEntity(memberId,item);
            entityList.add(entity);
        }
        return entityList;
    }

    private MemberSettings packageEntity(Long memberId,MemberSettingTypeEnum item){
        MemberSettings entity = new MemberSettings();
        entity.setMemberId(memberId);
        entity.setSettingType(item);
        entity.setSettingModule(item.getSettingModule());
        entity.setSettingValue(item.getDefaultValue());
        entity.setOrderNum(item.getOrderNum());
        return entity;
    }

    private List dealNewTypes(List dbList,List typeList,QueryMemberSettingsVO queryVO){
        List dbTypeList = new ArrayList<>();
        for(MemberSettingsItemVO dbItem : dbList){
            dbTypeList.add(dbItem.getSettingType());
        }

        // 差集 (typeList - dbTypeList) = 新配置的枚举类型
        List newTypeList = typeList.stream().filter(item -> !dbTypeList.contains(item)).collect(Collectors.toList());
        List entityList = packageSettings(queryVO.getMemberId(),newTypeList);

        //将新的枚举项存入数据库
        saveBatch(entityList);
        //重新查询数据
        dbList = mapper.listMemberSettings(queryVO);

        return dbList;
    }
}

2.7 扩展说明

如果有新的设置项或设置模块，新增枚举类型即可，不需要修改其他代码。

2.8 优化说明

将排序字段由int类型改成float类型扩展性更好一点。例如，现在只有设置项：A:1、B:2、C:3、D:4，由于需求变化，需要在B后面增加一个设置项，则可以设置成E:2.01