1.问题及背景
最近涉及到一个有趣的业务问题,那就是如果业务需求经常变,就比如需要再一张数据表中经常增加一些字段或减少一些字段,应该怎么办?
按照我们常用的方法设计Mysql数据库,首先就需要明确表字段,那如果这个表字段都不能定我们还能设计好数据库没?答案是肯定的当然可以。
2.问题分析
如果你采用的非关系型数据库,那么恭喜你,上面的问题对你来说没有难度,很容易解决。但是如果你采用的是关系型数据库,按我之前做过的系统经验,我觉得在传统MySQL数据库中,有两种实现方式。分别是采用设计的思路,和一种JSON技术来实现,我觉得各有优缺点,大家不放接着往下看。
3.方案实现
方案一:通过设计表结构转换实现
第一种就是转换思路,就是把横向的表纵向设计,设计一个字段表,字段表的每一行就存储业务字段配置信息,然后具体的数据存在另外一张数据表中,然后通过字段表中的字段与数据表中的数据进行关联,这样我们就可以做到应该业务字段随意增减的需求。
第一步:创建字段配置表
CREATE TABLE `t_staff_type` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) NOT NULL COMMENT '人员类型type名称',
`memo` varchar(255) DEFAULT NULL COMMENT '字段备注',
`type` varchar(255) DEFAULT NULL COMMENT '字段类型',
`is_show` varchar(255) DEFAULT NULL COMMENT '是否统计显示(是/否)',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '修改时间',
`operator` varchar(255) NOT NULL COMMENT '操作人',
`is_delete` varchar(255) NOT NULL COMMENT '是否删除:NO表示正常,YES表示已删除',
`version` varchar(255) NOT NULL DEFAULT '1.0' COMMENT '版本',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='人员类型表';
第二步:创建业务数据存储表
CREATE TABLE `t_nature_staff_type` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`nature_id` int(11) NOT NULL COMMENT '单位性质',
`staff_type_id` int(11) NOT NULL COMMENT '员工类型',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '修改时间',
`operator` varchar(255) NOT NULL COMMENT '操作人',
`is_delete` varchar(255) NOT NULL COMMENT '是否删除:NO表示正常,YES表示已删除',
`version` varchar(255) NOT NULL DEFAULT '1.0' COMMENT '版本',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='不同类型的公司,对应不同类型的员工类型';
通过这样的设计,我们将一个横向的数据表结构,转换为纵向的数据表结构。
- 优点:如果业务需要新增字段或删除字段,就可以在字段配置表中进行快速的配置和实现。
- 缺点:提高了代码复杂度,代码逻辑比较容易出错,而且通过SQL工具查询出来内容不易直接查看。
方案二:通过MySQL提供的JSON字段实现
MySQL提供了一种数据库字段类型JSON,他可以实现字段JSON格式的存储和查询,效率非常高,这里面我们通过JSON字段,进行存储信息,就可以做到对JSON字符串中的字段任意增加和删除操作。
第一步:创建带有JSON字段的表
DROP TABLE IF EXISTS UserLogin;
CREATE TABLE UserLogin (
userId BIGINT NOT NULL,
loginInfo JSON,
PRIMARY KEY(userId)
);
第二步:插入JSON数据
SET @a = '
{
"cellphone" : "13918888888",
"wxchat" : "破产码农",
"QQ" : "82946772"
}';
INSERT INTO UserLogin VALUES (1,@a);
第三步:实现JSON字段的查询
查询方式一:
SELECT
userId,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;
查询方式二:
SELECT
userId,
loginInfo->>"$.cellphone" cellphone,
loginInfo->>"$.wxchat" wxchat
FROM UserLogin
4.回顾一下
应对用户需求字段不确定,我们觉得有两种方案可以备选,第一种方案需要转换一下设计思路,后期维护字段比较灵活易于理解,第二种方案比较简单,编码更加容易操作