数据库动态属性数据存储
需求描述
实体的属性可能会随着业务的发展和变化而动态变化。例如，一个博客平台的文章实体可能最初只包含标题、内容和作者等基本属性，但随着功能的扩展，可能需要添加评论数量、浏览量、点赞数等属性。
亦或者一个产品属性不固定，可能一个产品包含名字，颜色，尺寸属性，其他同类产品增加了防水、抗摔等属性；等等数据结构会随着产品更新而变化，不希望重新改代码和数据结构的时候，怎么动态存储兼容这些变化；
方案一：EAV模型 Entity-Attribute-Value
EAV（Entity-Attribute-Value）模型是一种数据建模方法，用于处理具有动态、可变属性的实体。它通常用于解决以下问题：
- 灵活的属性存储： 在传统的关系型数据库模型中，每个实体通常有一个固定的属性集合，这样的设计无法满足实体属性的动态变化需求。EAV 模型允许实体拥有可变数量和类型的属性，使得系统可以灵活地存储和管理不同实体的属性。
- 应对不规则数据结构： 某些情况下，数据的结构可能是不规则的或者难以提前确定的。EAV 模型允许在不改变数据模型的情况下，动态地增加、删除或修改实体的属性，从而适应不同类型和形式的数据。
- 多态数据类型： EAV 模型可以存储多态数据类型，例如文本、数字、日期等，而不需要事先定义数据类型。这种灵活性使得 EAV 模型适用于处理各种类型的数据。
- 支持动态查询： 由于属性存储在单独的表中，并且可以根据需要动态增加或修改，因此 EAV 模型具有很强的灵活性和可扩展性，可以支持动态查询和分析需求。
尽管 EAV 模型具有灵活性和可扩展性等优点，但也存在一些挑战，如查询性能下降、数据完整性约束难以实现等。因此，在使用 EAV 模型时需要谨慎权衡其优缺点，并根据实际情况进行选择和设计。
-- 主表，存储所有数据的共同属性
CREATE TABLE main_table (
id INT PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 属性表，存储不同类型数据的不同属性
CREATE TABLE attribute_table (
id INT PRIMARY KEY AUTO_INCREMENT,
main_id INT, -- 关联主表
attribute_name VARCHAR(255), -- 属性名称
attribute_value TEXT, -- 属性值
FOREIGN KEY (main_id) REFERENCES main_table(id)
);
可以看到主表和属表的关系是 1对多，所以可以实现相同属性放在主表，不同属性放在属表，1对多关系，属表的多条数据，对应多个不同的属性；
-- 插入主表数据
INSERT INTO main_table (type) VALUES ('type1');
SET @main_id = LAST_INSERT_ID();
-- 插入属性表数据
INSERT INTO attribute_table (main_id, attribute_name, attribute_value) VALUES
(@main_id, 'name', 'kangqing'),
(@main_id, 'age', '30'),
(@main_id, 'city', 'Tianjin');
-- 插入主表数据
INSERT INTO main_table (type) VALUES ('type2');
SET @main_id = LAST_INSERT_ID();
-- 插入属性表数据
INSERT INTO attribute_table (main_id, attribute_name, attribute_value) VALUES
(@main_id, 'title', 'Product A'),
(@main_id, 'price', '100.00'),
(@main_id, 'quantity', '50');
例如：
主表 和 属表的结构如下
id | type
----------------
1 | type1
2 | type2
main_id | attribute_name | attribute_value
--------------------------------------------
1 | name | kangqing
1 | age | 30
2 | title | Product A
2 | price | 100.00
SELECT
mt.id,
mt.type,
MAX(CASE WHEN at.attribute_name = 'name' THEN at.attribute_value END) AS name,
MAX(CASE WHEN at.attribute_name = 'age' THEN at.attribute_value END) AS age,
MAX(CASE WHEN at.attribute_name = 'city' THEN at.attribute_value END) AS city,
MAX(CASE WHEN at.attribute_name = 'title' THEN at.attribute_value END) AS title
FROM
main_table mt
LEFT JOIN
attribute_table at ON mt.id = at.main_id
GROUP BY
mt.id, mt.type;
-- 联表查询之后的结果
main_id | type | name | age | city | title
---------------------------------------------
1 | type1 | John | 30 | NULL | NULL
2 | type2 | NULL | NULL| NULL | Product A
-- 改进一下,参数for循环分配
<select id="searchMainTable" resultType="java.util.Map">
SELECT
mt.id,
mt.type,
<foreach collection="attributeNames" item="attributeName" separator=",">
MAX(CASE WHEN at.attribute_name = #{attributeName} THEN at.attribute_value END) AS ${attributeName}
</foreach>
FROM
main_table mt
LEFT JOIN
attribute_table at ON mt.id = at.main_id
GROUP BY
mt.id, mt.type;
</select>
// 数据库，或者配置文件获取
List<String> attributeNames = Arrays.asList("name", "age", "city", "title");
params.put("attributeNames", attributeNames);
List<Map<String, Object>> resultList = mainMapper.searchMainTable(params);
示例：
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.*;
@RestController
public class EntityController {
@Autowired
private EntityMapper entityMapper;
@GetMapping("/entities")
public List<Map<String, Object>> getEntitiesByAttribute(
@RequestParam("attribute") String attribute,
@RequestParam("value") String value
) {
// 执行查询操作
List<Map<String, Object>> entities = entityMapper.findByAttribute(attribute, value);
// 对查询结果进行处理，合并相同实体 ID 的属性
Map<Integer, Map<String, String>> mergedEntities = new HashMap<>();
for (Map<String, Object> entity : entities) {
int entityId = (int) entity.get("entity_id");
String attr = (String) entity.get("attribute");
String val = (String) entity.get("value");
if (!mergedEntities.containsKey(entityId)) {
mergedEntities.put(entityId, new HashMap<>());
}
mergedEntities.get(entityId).put(attr, val);
}
// 将处理后的结果转换为列表返回给前端
List<Map<String, Object>> result = new ArrayList<>();
for (Map.Entry<Integer, Map<String, String>> entry : mergedEntities.entrySet()) {
Map<String, Object> entityMap = new HashMap<>();
entityMap.put("entity_id", entry.getKey());
entityMap.putAll(entry.getValue());
result.add(entityMap);
}
return result;
}
}
[
{
"entity_id": 1,
"name": "John",
"age": "30"
},
{
"entity_id": 2,
"name": "Jane",
"age": "25",
"city": "New York"
}
]
方案二： JSON结构存储动态属性
MySQL 中的 JSON 数据类型允许存储和操作 JSON 文档。通过 JSON 数据类型，可以存储动态变化的属性，以适应实体属性的动态变化需求。以下是 MySQL 中 JSON 数据类型的介绍：
- JSON 数据类型： MySQL 5.7 版本及以上支持 JSON 数据类型。JSON 数据类型可以存储 JSON 文档，包括对象、数组、字符串、数字、布尔值和 null 值等。JSON 文档以原生的 JSON 格式存储，不需要转换成字符串形式。
- 存储格式： JSON 数据类型存储在数据库中的格式与原生的 JSON 格式相同，不需要额外的解析或转换。可以直接将 JSON 文档插入到 JSON 数据类型的列中，也可以通过 JSON 函数操作 JSON 数据。
- 动态属性： JSON 数据类型允许存储动态变化的属性。在 JSON 文档中，可以动态地增加、删除或修改属性，以适应实体属性的动态变化需求。这使得 MySQL 中的 JSON 数据类型成为一种灵活的解决方案，适用于存储实体属性的动态变化情况。
- 查询和操作： MySQL 提供了一系列的 JSON 函数，用于操作和查询 JSON 数据。这些函数包括 JSON_EXTRACT、JSON_CONTAINS、JSON_ARRAY、JSON_OBJECT 等，可以用于从 JSON 文档中提取数据、判断是否包含某个值、构建 JSON 数组和对象等操作。
- 索引支持： MySQL 5.7 版本及以上支持对 JSON 数据类型的索引。可以通过创建虚拟列和使用函数索引来对 JSON 列中的数据进行索引，从而提高查询性能。
通过 MySQL 中的 JSON 数据类型，可以方便地存储和操作动态变化的属性，适应实体属性的动态变化需求。这使得 MySQL 成为一种强大的解决方案，适用于存储各种类型和形式的数据。
-- 数据库结构,不同的属性结构以JSON格式存在 attributes 中
CREATE TABLE products (
id SERIAL PRIMARY KEY,
attributes JSON
);
-- 测试数据
INSERT INTO products (attributes)
VALUES
('{"name": "MacBook", "color": "silver", "price": 12345, "storage": "512GB", "screen_size": "13 inches"}'),
('{"name": "Dell", "color": "black", "price": 1699.99, "storage": "1TB", "graphics_card": "NVIDIA"}'),
('{"name": "iPhone", "color": "green", "price": 699.99, "storage": "128GB", "water_resistant": true}');
-- 条件查询
SELECT *
FROM products
WHERE JSON_EXTRACT(attributes, '$.color') = 'silver'
AND JSON_EXTRACT(attributes, '$.price') < 18000;
-- 更新
UPDATE products
SET attributes = JSON_SET(attributes, '$.price', 1999.98)
WHERE id = 1;
-- 创建虚拟列索引
ALTER TABLE products
ADD COLUMN color VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))) STORED;
CREATE INDEX idx_color ON products(color);
本文由mdnice多平台发布
相关内容