如何在mysql中存储类型可以是数字，日期或字符串的数据

小编典典

如何在mysql中存储类型可以是数字，日期或字符串的数据

sql

我们正在开发一个监控系统。在我们的系统中，值是由在不同服务器上运行的代理报告的。报告的观察结果可以是如下值：

一个数字值。例如，“ CPU使用率” =55。表示正在使用55％的CPU）。
某些事件被解雇了。例如“备份完成”。
状态：例如，SQL Server处于脱机状态。

我们要存储这些观测值（这些观测值是事先未知的，将被动态添加到系统中，而无需重新编译）。

我们正在考虑将不同的列添加到观察表中，如下所示：

IntMeasure -> INTEGER
FloatMeasure -> FLOAT
Status -> varchar(255)

因此，如果我们希望存储的值是一个数字，则可以根据类型使用IntMeasure或FloatMeasure。如果值是状态，则可以存储状态文字字符串（如果决定添加Statuses（id，name）表，则可以存储状态id）。

我们认为可能有一个更正确的设计，但由于连接和动态表名称（取决于类型）而可能变得缓慢而黑暗。如果我们无法在查询中提前指定表，联接将如何工作？

阅读 181

2021-05-30

共1个答案

小编典典

我还没有做过正式的研究，但是根据我自己的经验，我猜想超过80％的数据库设计缺陷是由性能（最重要的考虑因素）引起的。

如果一个好的设计需要多个表，则创建多个表。不要自动假定要避免加入联接。它们很少是性能问题的真正原因。

在数据库设计的所有阶段中，首要的考虑因素是数据完整性。“答案可能并不总是正确的，但我们可以很快将其提供给您”，这并不是任何商店都应努力实现的目标。一旦锁定了数据完整性，
如果性能成为问题 ，就可以解决。不要牺牲数据完整性，尤其是要解决可能不存在的问题。

考虑到这一点，请看您需要什么。您有需要存储的观察值。这些观察值可以在属性的数量和类型上有所不同，并且可以是诸如测量值，事件通知和状态更改之类的事物，并且还可以添加将来的观察值。

这似乎适合于标准的“类型/子类型”模式，其中“观察”条目是类型，每种观察值或类型是子类型，并建议某种形式的类型指示符字段，例如：

create table Observations(
   ...,
   ObservationKind  char( 1 ) check( ObservationKind in( 'M', 'E', 'S' )),
   ...
);

但是在检查约束中对这样的列表进行硬编码具有非常低的可维护性级别。它成为架构的一部分，并且只能使用DDL语句进行更改。您的DBA不会期待的。

因此，在自己的查找表中具有各种观察结果：

ID  Name         Meaning
==  ===========  =======
M   Measurement  The value of some system metric (CPU_Usage).
E   Event        An event has been detected.
S   Status       A change in a status has been detected.

（char字段也可以是int或smallint。我在这里使用char进行说明。）

然后用PK和所有观察共有的属性填写“观察”表。

create table Observations(
   ID               int identity primary key,
   ObservationKind  char( 1 ) not null,
   DateEntered      date not null,
   ...,
   constraint FK_ObservationKind foreign key( ObservationKind )
      references ObservationKinds( ID ),
   constraint UQ_ObservationIDKind( ID, ObservationKind )
);

在Kind字段和PK的组合上创建唯一的索引似乎很奇怪，这本身就是唯一的，但是请耐心等待一下。

现在，每种类型或子类型都有自己的表。请注意，每种观察都获得一个表，而不是数据类型。

create table Measurements(
    ID                   int not null,
    ObservationKind      char( 1 ) check( ObservationKind = 'M' ),
    Name                 varchar( 32 ) not null, -- Such as "CPU Usage"
    Value                double not null, -- such as 55.00
    ...,  -- other attributes of Measurement observations
    constraint PK_Measurements primary key( ID, ObservationKind ),
    constraint FK_Measurements_Observations foreign key( ID, ObservationKind )
        references Observations( ID, ObservationKind )
);

对于其他类型的观察，前两个字段将相同，除了检查约束会将值强制为适当的类型。其他字段的编号，名称和数据类型可能有所不同。

让我们研究一个可能存在于Measurements表中的元组示例：

ID    ObservationKind  Name       Value  ...
====  ===============  =========  =====
1001  M                CPU Usage  55.0   ...

为了使该元组存在于此表中，必须首先在观察表中存在一个匹配条目，其ID值为1001，观察种类值为“
M”。ID值为1001的其他任何条目都不能存在于观察表或度量表中，并且根本不能存在于任何其他“种类”表（事件，状态）中。所有种类表的工作方式相同。

我将进一步建议为每种观察创建一个视图，以将每种观察与主要观察表连接起来：

create view MeasurementObservations as
    select ...
    from   Observations o
    join   Measurements m
        on m.ID = o.ID;

任何仅适用于测量的代码都只需要点击此视图即可，而不是基础表。使用视图在应用程序代码和原始数据之间创建抽象墙，极大地增强了数据库的可维护性。

现在，创建另一种观察值，例如“ Error”，涉及到对ObservationKinds表的简单插入语句：

F   Fault        A fault or error has been detected.

当然，您需要为这些错误观察创建一个新的表和视图，但是这样做不会对现有的表，视图或应用程序代码产生影响（当然，除了编写新的代码以与新的观察一起使用之外）。

2021-05-30