DB中的变体数据类型 [英] Variant data type in DB

问题描述

我正在寻找一种在数据库中(可能是mysql)具有变体列的方法，我知道这是不可能的，但是我需要的是一种模仿这种行为的方法.

I'm looking for a way to have a variant column in my database (mysql probably), I know this is not possible, but what I need is a way to emulate this behavior.

我有一对简单的表，例如:

I have a simple pair of tables like:

#task table
(
id int ..., 
date timestamp, 
owner int
) 

#info table
(
id int ..., 
relative int, #points to Task 
name varchar, 
value VARIANT
)

基本上，我需要将可变数量的信息字段与每个任务相关联，并且每个information.value的类型都不同(字符串，日期时间，布尔值和整数).

Basically I need to associate a variable number of information fields to each task, and each information.value would be of distinct type (string, datetime, bool and integer).

我计划创建每种类型的四列，而不是单个VARIANT，并填充正确的列.但是该表将增长很多(600Mb/月)，我认为这将是巨大的空间浪费.

I've planned to create four columns of each type, instead of a single VARIANT, and populate the correct one. But that table will grow a lot (600Mb/month), and I think this will be a huge waste of space.

有人知道更好的方法吗?

Does someone know a better way to accomplish that?

我不知道这是否会让情况变得更糟或更糟，但是我将在Django中做到！

I don't know if this will let that even worse or better but I'll do this in django!

推荐答案

您正在实现一种称为实体属性值(EAV)模型的东西.如果您不知道它是什么，您已经很好地描述了它.

You are implementing something called an entity-attribute-value (EAV) model. You've described it pretty well, in case you don't know what it is.

就数据结构而言，当字符串类型具有 NULL 值时，它们所占空间很小.但是其他类型确实会占用空间，因此您将浪费空间.您可以将所有内容存储为一个字符串-数字存储为数字，日期存储为YYYY-MM-DD，并使用单个字符串存储.但是，您的确失去了本机数据类型的一些灵活性.

In terms of the data structure, string types occupy little space when they have NULL values. But other types do occupy space, so you will have wasted space. You could store everything as a string -- numbers as numbers, dates as YYYY-MM-DD, and make do with a single string. You do lose some of the flexibility of a native data type though.

通常，EAV模型的计算量很大.每月600 MB是相当可观的数据量.在MySQL中，倾倒千兆字节的数据以将记录重新组合在一起可能会很痛苦(对于 group by 而言，性能较差).我通常会推荐一种混合EAV模型，在该模型中，常规"记录存储了常用的属性，而EAV片段仅用于罕见的属性.

In general, EAV models are computationally expensive. 600 Mbytes per month is a respectable amount of data. Pouring through gigabytes of data to bring records back together can be painful in MySQL (which has poor performance for group by). I generally recommend a hybrid EAV model, where a "regular" record stores commonly used attributes and the EAV piece is only there for the uncommon attributes.

这篇关于DB中的变体数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！