如何在mysql中存储类型可以是数字，日期或字符串的数据 [英] How to store a data whose type can be numeric, date or string in mysql
问题描述
- 数值。例如CPU USAGE= 55.含义55％的CPU在
中使用）。 - 某些事件已被触发。例如备份完成。
- 状态：例如SQL Server已脱机。
我们想保存这些观察结果（这些不是事先知道的，而且将动态添加到系统中重新编译）
我们正在考虑在观察表中添加不同的列，如下所示：
code> IntMeasure - > INTEGER
FloatMeasure - > FLOAT
状态 - > varchar（255）
所以如果我们想要存储的值是一个数字，我们可以使用IntMeasure或FloatMeasure根据类型。如果值是一个状态，我们可以存储状态文字字符串（或者如果我们决定添加一个Statuses（id，name）表）的状态id）。
我们假设可能有一个更正确的设计，但由于联接和动态表名称可能会变慢和黑暗，这取决于类型？如果我们不能在查询中提前指定表，连接如何工作？
我没有做过正式的研究，但是从我自己的经验来看，我猜想80％以上的数据库设计缺陷都是从性能设计中产生的，作为最重要的（如果不是）考虑。
如果一个好的设计要求多个表，创建多个表。不要自动假设连接是要避免的。它们很少是性能问题的真正原因。
主要考虑因素是数据库设计的所有阶段，首先是数据完整性。 答案可能并不总是正确的，但我们可以很快得到它，这不是任何一家店铺都应该朝着这个目标迈进的一个目标。一旦数据完整性被锁定，如果性能成为问题，那么可以解决这个问题。不要牺牲数据的完整性，特别是解决可能不存在的问题。
考虑到这一点，看看你需要什么。你有需要存储的观察。这些观察结果可以在属性的数量和类型方面有所不同，可以是测量的价值，事件的通知和状态的变化等，以及添加未来观察的可能性。
这似乎符合标准的类型/子类型模式，观察条目是类型，每种类型或类型的观察是子类型，并提出一些形式的类型指标字段，例如：
创建表观察（
...，
ObservationKind char 1）检查（ObservationKind in（'M'，'E'，'S'）），
...
）;
但是在检查约束中硬编码这样的列表具有非常低的可维护性级别。它成为模式的一部分，只能使用DDL语句进行更改。不是你的DBA会期待的。
所以在自己的查找表中有这样的观察：
ID名称含义
== =========== =======
M测量某些系统的值metric（CPU_Usage）。
E事件已检测到事件。
S状态已检测到状态更改。
（char字段也可以是int或smallint，我在这里使用char来说明）
然后用一个PK填写观察表，以及所有观察结果将是常见的属性。
create table Observations（
ID int identity primary key，
ObservationKind char（1）not null，
DateEntered date not null，
..
约束FK_ObservationKind外键（ObservationKind）
引用ObservationKinds（ID），
约束UQ_ObservationIDKind（ID，ObservationKind）
）;
在Kind字段和PK的组合中创建一个唯一的索引似乎很奇怪，独一无二，但我忍受一下。
现在每种或者亚型都有自己的表。请注意，每种观察都得到一个表，而不是数据类型。
创建表测量（
ID不是null，
ObservationKind char（1）check（ObservationKind ='M'），
名称varchar（32）not null， - 如CPU Usage
Value double not null， - 如55.00
...， - 测量观察的其他属性
约束PK_Measurements主键（ID，ObservationKind），
约束FK_Measurements_Observations外键（ID，ObservationKind）
参考文献观察（ID，ObservationKind）
）;
前两个字段对于其他类型的观察将是相同的，除了检查约束将强制价值到适当的种类。其他字段可能在数量，名称和数据类型上有所不同。
我们来看看测量表中可能存在的一个示例元组：
ID观察名称值...
==== =============== ===== ==== =====
1001 M CPU使用率55.0 ...
为了在此表中存在该元组，匹配条目必须首先存在于Observation表中，ID值为1001，观察类型值为M。在观察表或测量表中不能存在ID值为1001的其他条目，并且根本不存在任何其他种表（事件，状态））。对于所有类型的表，这种方式的工作方式相同。
我将进一步推荐为每种观察创建一个视图，这将提供每种类型的主观观察表：
创建视图MeasurementObservations as
select ...
from Observations o
join在m.ID = o.ID上测量m
;
只有使用测量功能的任何代码只需要点击此视图而不是基础表。使用视图在应用程序代码和原始数据之间创建一个抽象墙，极大地提高了数据库的可维护性。
现在创建另一种观察，如错误涉及到ObservationKinds表的简单插入语句：
F故障检测到故障或错误。
当然，您需要创建一个新表并查看这些错误观察，但这样做会对现有的表，视图或应用程序代码没有影响（除了当然要编写新代码以使用新的观察结果）。
We're developing a monitoring system. In our system values are reported by agents running on different servers. This observations reported can be values like:
- A numeric value. e.g. "CPU USAGE" = 55. Meaning 55% of the CPU is in use).
- Certain event was fired. e.g. "Backup completed".
- Status: e.g. SQL Server is offline.
We want to store this observations (which are not know in advance and will be added dynamically to the system without recompiling).
We are considering adding different columns to the observations table like this:
IntMeasure -> INTEGER
FloatMeasure -> FLOAT
Status -> varchar(255)
So if the value we whish to store is a number we can use IntMeasure or FloatMeasure according to the type. If the value is a status we can store the status literal string (or a status id if we decide to add a Statuses(id, name) table).
We suppose it's possible to have a more correct design but would probably become to slow and dark due to joins and dynamic table names depending on types? How would a join work if we can't specify the tables in advance in the query?
I haven't done a formal study, but from my own experience I would guess that more than 80% of database design flaws are generated from designing with performance as the most important (if not only) consideration.
If a good design calls for multiple tables, create multiple tables. Don't automatically assume that joins are something to be avoided. They are rarely the true cause of performance problems.
The primary consideration, first and foremost in all stages of database design, is data integrity. "The answer may not always be correct, but we can get it to you very quickly" is not a goal any shop should be working toward. Once data integrity has been locked down, if performance ever becomes an issue, it can be addressed. Don't sacrifice data integrity, especially to solve problems that may not exist.
With that in mind, look at what you need. You have observations you need to store. These observations can vary in the number and types of attributes and can be things like the value of a measurement, the notification of an event and the change of a status, among others and with the possibility of future observations being added.
This would appear to fit into a standard "type/subtype" pattern, with the "Observation" entry being the type and each type or kind of observation being the subtype, and suggests some form of type indicator field such as:
create table Observations(
...,
ObservationKind char( 1 ) check( ObservationKind in( 'M', 'E', 'S' )),
...
);
But hardcoding a list like this in a check constraint has a very low maintainability level. It becomes part of the schema and can be altered only with DDL statements. Not something your DBA is going to look forward to.
So have the kinds of observations in their own lookup table:
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.
(The char field could just as well be int or smallint. I use char here for illustration.)
Then fill out the Observations table with a PK and the attributes that would be common to all observations.
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 )
);
It may seem strange to create a unique index on the combination of Kind field and the PK, which is unique all by itself, but bear with me a moment.
Now each kind or subtype gets its own table. Note that each kind of observation gets a table, not the data type.
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 )
);
The first two fields will be the same for the other kinds of observations except the check constraint will force the value to the appropriate kind. The other fields may differ in number, name and data type.
Let's examine an example tuple that may exist in the Measurements table:
ID ObservationKind Name Value ...
==== =============== ========= =====
1001 M CPU Usage 55.0 ...
In order for this tuple to exist in this table, a matching entry must first exist in the Observations table with an ID value of 1001 and an observation kind value of 'M'. No other entry with an ID value of 1001 can exist in either the Observations table or the Measurements table and cannot exist at all in any other of the "kind" tables (Events, Status). This works the same way for all the kind tables.
I would further recommend creating a view for each kind of observation which will provide a join of each kind with the main observation table:
create view MeasurementObservations as
select ...
from Observations o
join Measurements m
on m.ID = o.ID;
Any code that works solely with measurements would need to only hit this view instead of the underlying tables. Using views to create a wall of abstraction between the application code and the raw data greatly enhances the maintainability of the database.
Now the creation of another kind of observation, such as "Error", involves a simple Insert statement to the ObservationKinds table:
F Fault A fault or error has been detected.
Of course, you need to create a new table and view for these error observations, but doing so will have no impact on existing tables, views or application code (except, of course, to write the new code to work with the new observations).
这篇关于如何在mysql中存储类型可以是数字，日期或字符串的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
