主页 / dba / 问题 / 286540
Accepted
Arya
Asked: 2021-03-06 13:00:44 +0800 CST

如何为同一列存储多种数据类型（对于许多列）

  • 772

作为序言，已经提出了类似的问题，但我没有找到任何可以解决具有大量列受此问题影响的表的问题。所有类似的似乎都解决了一个有少量列受此问题影响的表......

...但是我有一个 MySQL 数据库表，其中有许多（例如，30）列当前设置为 data type decimal(5,2)。

现在，出现了一个新场景，我需要为这 30 列中的每一列输入一个decimal(5,2)OR a char(2)（即，一个或另一个，但绝不是两者）。为了给您提供上下文，我输入的是样品中检测到的元素数量，如果未检测到元素的数量，那么我必须能够识别出一个元素已被测试但未检测到，例如“ ND”。我还必须知道该元素是否尚未经过测试。例如，如果列是，那么它可能表明我尚未测试相应的元素。因此，与此场景相关的三种可能情况：DEFAULT NULL

  1. 一个decimal(5,2)值表示该元素已经过测试和检测。
  2. 值char(2)表示该元素已测试但未检测到。
  3. ( NULL...?) 值表示该元素未经过测试。

所以，我最初的想法是将更改列的类型更改为 a varchar，以便列能够存储 achar(2)或decimal(5,2)值，但这似乎是一条名为 的危险路径Entity-Attribute_Value，这可能需要我更多地构建应用层以适应由它引起的限制。

因此，我阅读了“SQL 反模式：避免数据库编程的陷阱”（http://rads.stackoverflow.com/amzn/click/1934356557）一书的第 6 章和其他几篇相关文章，其中两个最常见的答案似乎成为：

1. 使用基于您要执行的查询类型的设计。

就我而言，我想执行多种类型的查询，比较不同行的列值，确定平均值，可能还有更多无法预料的情况。

2.使用Class Table Inheritance设计。

我想实现该Class Table Inheritance设计，但这需要为每列创建更多表。考虑到许多列只需要两种数据中的一种，这似乎是一个过于复杂的解决方案。

例如，如果我的理解Class Table Inheritance是正确的，那么对于我的基表中的 30 列中的每一列，我必须为每个子类型创建一个表，并让它们的主键也作为基表的外键，这意味着我必须创建 60 个表。为了处理可能具有两种数据类型之一的 30 列，创建 60 个表似乎过多。此外，似乎我需要想办法管理这 30 对表，以便它们引用同一列，可能在应用程序层中。

在我结束这篇文章之前想到的一个可能的解决方案是：使用Class Table Inheritance设计，但不是为我的两种数据类型创建 30 对表，而是为这两种数据类型创建 2 个表并添加一个存储一个值的列关联每对，例如它们将关联的基表列的名称。但这似乎也不是好的做法。

有什么建议么？

mysql eav

3 个回答

  1. Best Answer

    我是你提到的那本书的作者。感谢您阅读我的书！

    我建议你阅读第 14 章，“对未知的恐惧”。

    对于您的情况：

    我正在输入样品中检测到的元素的数量，如果未检测到元素的数量，那么我必须能够识别出已测试但未检测到的元素，例如“ND”。

    我不会存储字符串值“ND”。NULL当值未知或不适用时，我会存储而不是数字。

    然后当你查询它时，你可以使用：

    SELECT COALESCE(myNumericColumn1, 'ND') AS myNumericColumn1
FROM Mytable
...;

    SQL 中的COALESCE() 函数返回其第一个非 NULL 参数。在此示例中，如果给定行的数字为 NULL，则结果将使用第二个参数中的默认值“ND”。

    您还可以使用此表达式为每列创建一个 VIEW，因此您不必在每个查询中重复它。

    回复您的评论：

    这听起来像是您需要将结果存储在rows中，而不是 _columns 中。每行一个数字结果。只有两个三列：

    • 样品编号
    • 元素 ID
    • 数值

    然后你可以区分三种状态：

    • 存在具有非 NULL 数值的行。
    • 存在一行，但数值为 NULL，表示测试已完成，但未检测到结果。
    • 没有一行，表明元素测试未完成。

    当您希望客户端显示样本的结果时，您将获取 30 行（如果不是在给定样本中测试了所有元素，则获取更少的行），然后在客户端中编写代码以遍历 30 行并呈现数据随你怎么便。

    • 3

  2. 将列保存为具有两个（或更多）属性的 json，以便您收到 json，并且必须检查哪些有数据。

    任何编程语言都支持 Json，您可以在其中保存几乎所有内容。

    主要问题是您必须了解如何处理 json，当然您必须更改每个需要列的查询，但只要它不是嵌套的，它就很容易管理。

    • 1

  3. 我会使用值0.0来表示测试未检测到元素，并NULL表示未执行测试。

    您的列值表示AMOUNT DETECTED。在这种情况下，“检测到”的数量为零，即使元素的实际数量不为零（但由于测试的灵敏度有限而无法检测到）。

    如果您的统计分析需要仅限于具有“检测到”值的结果，只需0.0从计算中排除值为 的记录，就像您使用 排除它们一样ND。

    • 0

相关问题