No Comments

数据库设计——自定义扩展数据

在开发中，应业务需要，我们的关系型数据库的数据字段需要进行扩展，我们如何在原有的表基础上实现扩展呢?

直接添加字段

顾名思义，直接修改数据表，增加字段。通常在业务确定需要某个属性时这样操作，但业务数据不定时就不适合了。

预留字段

普通预留字段

在建表时，新增几个预留的字段，类型为字符串。

CREATE TABLE `p_test` (
  `id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `sex` TINYINT(2) DEFAULT 1 COMMENT '性别 1男 2女',
  `birthday` VARCHAR(15) DEFAULT '' COMMENT '生日 2004-05-09',
  `ext1` VARCHAR(255) DEFAULT '' COMMENT '预留扩展字段1',
  `ext2` VARCHAR(100) DEFAULT '' COMMENT '预留扩展字段2',
  `ext3` VARCHAR(100) DEFAULT '' COMMENT '预留扩展字段3',
  `ext4` VARCHAR(100) DEFAULT '' COMMENT '预留扩展字段4',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

优点

  • 查询效率高，并支持关联查询
  • 可以正常的CURD

缺点

  • 自定义扩展字段数量有限
  • 字段一样，但是含义不一样，需要前端做大量适配。
  • 字段的类型可能不一样，预留字段还得考虑不同的类型，所以要使用varchar类型

预留字段+元数据

CREATE TABLE `p_test` (
  `id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `sex` TINYINT(2) DEFAULT 1 COMMENT '性别 1男 2女',
  `birthday` VARCHAR(15) DEFAULT '' COMMENT '生日 2004-05-09',
  `ext_type` tinyint(2) DEFAULT 1 COMMENT '扩展类型',
  `ext1` VARCHAR(255) DEFAULT '' COMMENT '预留扩展字段1',
  `ext2` VARCHAR(100) DEFAULT '' COMMENT '预留扩展字段2',
  `ext3` VARCHAR(100) DEFAULT '' COMMENT '预留扩展字段3',
  `ext4` VARCHAR(100) DEFAULT '' COMMENT '预留扩展字段4',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

CREATE TABLE `p_test_ext` (
  `ext_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ext_type` TINYINT(20) UNSIGNED NOT NULL DEFAULT 1 COMMENT '',
  `ext_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '扩展字段名 ext1 ext2等',
  `ext_mapping` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '扩展字段名映射',
  `ext_remark` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`ext_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

// 或者直接在配置文件中定义
// ext_type => ['ext_name'=>'',ext_remark=>'']

EAV模型

EAV模型中，对象存储在一个表中会有三种属性描述：实体、属性、值。实体表示一条数据。

这种模型带来了数据的灵活性，增加对象的属性不需要动态增加数据表的字段。但是EAV表也有较大的性能问题。通常，EAV表带来的一个问题是当查找多个字段时，需要进行关联查询join, 这样的查询效率比较低。

CREATE TABLE `p_goods` (
  `goods_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `goods_name` VARCHAR(120) NOT NULL DEFAULT '' COMMENT '商品的名称',
  `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '状态0回收站 1正常',
  PRIMARY KEY (`goods_id`),
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='商品详情'

CREATE TABLE `p_goods_attribute` (
  `attr_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `attr_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '属性名',
  `attr_type` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '类型：1单行输入 2单选 3多选 4下拉框 等',
  `attr_values` TEXT NOT NULL COMMENT '可选属性值，多个逗号相隔',
  `sort` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '排序，按顺序排',
  PRIMARY KEY (`attr_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='商品属性值约束'

CREATE TABLE `p_goods_attr` (
  `goods_attr_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `goods_id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' COMMENT '实体：商品ID',
  `attr_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT '属性：属性ID',
  `attr_value` TEXT NOT NULL COMMENT '值：属性值',
  PRIMARY KEY (`goods_attr_id`),
  KEY `goods_id` (`goods_id`),
  KEY `attr_id` (`attr_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='商品属性'

优点

  • 扩展能力强
  • 理论上增加字段无上限
  • 可以支持几乎所有的自定义字段类型的需求
  • EAV只处理非空属性，不需要为空值保留额外的存储空间。这使得EAV模型相当节省空间。

缺点

  • 使用EAV时，确定数据是否稀疏和数据量非常重要，因为采用不恰当的数据集时，EAV设计的复杂性超过了其优势所在。
  • 关联查询效率低下
  • 需要维护自定义字段与值的关系表

Json格式

就是说把需扩展的一组字段都到到一个字段里，各个字段用JSON的方式组成一个大的字符串。

注意：MySQL5.7.8开始，支持JSON类型，以前我们通常使用text或varchar类型储存，5.7.8后应该改为JSON类型。想象一下，如果对Json数据的局部修改从读取-反序列化-修改-序列化-回写变为直接修改，能节省多少资源；以前我们读取局部数据时必须经历读取-反序列化-根据Key查找，而现在我们可以直接根据Key读取；也可以直接通过MySQL的JSON函数直接作为SQL的WHERE条件。

CREATE TABLE `p_test` (
  `id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `sex` TINYINT(2) DEFAULT 1 COMMENT '性别 1男 2女',
  `birthday` VARCHAR(15) DEFAULT '' COMMENT '生日 2004-05-09',
  `ext` TEXT COMMENT '扩展字段',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

其中 ext字段 储存的内容为 json字符串。

优点：

  • 扩展能力强
  • 理论上无上限
  • 可以支持几乎所有自定义字段的需求
  • 无需维护自定义字段与值的关系

缺点

  • 自定义字段不支持与其他表相同字段进行关联查询
  • 自定义字段检索需要通过其他方式，例如搜索引擎、cast类型转化、特殊函数json_extract()等

动态修改数据库字段

有两张表，自定义字段表，业务表。字段表记录所有的主要字段信息，业务表字段根据字段表对应的信息增减字段。
在字段表中，分为系统字段和自定义字段，例如：field_id<500是系统字段，field_id>500是自定义字段，系统字段不允许修改删除，自定义字段可修改删除。

当在pre_field中添加字段时，同时会在pre_yewu表中添加一列。

// 字段表
CREATE TABLE `pre_field` (
  `field_id` INT(11) NOT NULL AUTO_INCREMENT,
  `types` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分类',
  `field` VARCHAR(50) NOT NULL COMMENT '字段名',
  `name` VARCHAR(50) NOT NULL COMMENT '标识名',
  `form_type` VARCHAR(20) NOT NULL COMMENT '字段类型',
  `default_value` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '默认值',
  `max_length` INT(4) NOT NULL DEFAULT '0' COMMENT '字数上限',
  `is_unique` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否唯一（1是，0否）',
  `is_null` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否必填（1是，0否）',
  `is_hidden` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否隐藏：1隐藏；0不隐藏',
  `input_tips` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '输入提示',
  `setting` TEXT COMMENT '设置',
  `order_id` INT(4) NOT NULL DEFAULT '0' COMMENT '排序ID',
  `operating` INT(10) NOT NULL DEFAULT '0' COMMENT '0改删，1改，2删，3无',
  `create_time` INT(11) NOT NULL COMMENT '创建时间',
  `update_time` INT(11) NOT NULL COMMENT '更新时间',
  `type` INT(2) NOT NULL DEFAULT '0' COMMENT '薪资管理 1固定 2增加 3减少',
  `remark` VARCHAR(64) DEFAULT NULL COMMENT '字段说明',
  `options` LONGTEXT COMMENT '如果类型是选项，此处不能为空，多个选项以，隔开',
  PRIMARY KEY (`field_id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='自定义字段表'

// 业务表
CREATE TABLE `pre_yewu` (
  `yewu_id` INT(11) NOT NULL AUTO_INCREMENT, // 原有字段
  `name` VARCHAR(255) DEFAULT NULL COMMENT '线索名称', // 关联field字段--系统字段不能修改删除
  `source` VARCHAR(500) DEFAULT NULL COMMENT '线索来源', // 关联field字段--系统字段不能修改删除
  `telephone` VARCHAR(255) DEFAULT NULL COMMENT '电话',
  `mobile` VARCHAR(255) DEFAULT NULL COMMENT '手机',
  `industry` VARCHAR(500) DEFAULT NULL COMMENT '客户行业',
  `level` VARCHAR(500) DEFAULT NULL COMMENT '客户级别',
  `detail_address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
  `email` VARCHAR(255) DEFAULT NULL COMMENT '电子邮箱',
  `remark` TEXT COMMENT '备注',  // 关联field字段--系统字段不能修改删除
  `create_user_id` INT(10) NOT NULL COMMENT '创建人ID', // 原有字段
  `owner_user_id` INT(10) NOT NULL COMMENT '负责人ID',
  `next_time` INT(11) DEFAULT NULL COMMENT '下次联系时间',
  `follow` VARCHAR(20) DEFAULT NULL COMMENT '跟进',
  `create_time` INT(11) NOT NULL COMMENT '创建时间',
  `update_time` INT(11) NOT NULL COMMENT '更新时间',
  `is_dealt` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '是否已经处理（待办事项）：1已处理；0未处理；',
  `is_allocation` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否是分配给我的线索：1是；0不是',
  `last_time` INT(10) UNSIGNED DEFAULT NULL COMMENT '最后跟进时间',
  `last_record` VARCHAR(512) DEFAULT NULL COMMENT '最后跟进记录',
  `ro_user_id` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '只读权限',
  `rw_user_id` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '读写权限',
  `crm_utcvjo` VARCHAR(255) DEFAULT NULL COMMENT '日期区间', // 关联field字段--自定义字段可删除修改
  `crm_trkydg` VARCHAR(255) DEFAULT NULL COMMENT '地址信息', // 关联field字段--自定义字段可删除修改
  `crm_rvqycn` VARCHAR(500) DEFAULT NULL COMMENT '多选选项', // 关联field字段--自定义字段可删除修改
  PRIMARY KEY (`yewu_id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='业务表'

MongoDB存储自定义字段

mongodb天然支持对json格式的存储特性也可以实现自定义字段的存储。

最后更新于  2022-06-22 11:43:59  并被添加「」标签，已有 1387 位童鞋阅读过。

本站使用「署名 4.0 国际」创作共享协议，可自由转载、引用，但需署名作者且注明文章出处

此处评论已关闭