通过不同的列插入创建表

I have a source(web pages) that have common data and uncommon data that which I need to store in one table.

我有一个源(网页),它有共同的数据和不常见的数据,我需要存储在一个表中。

The data can look like this:

数据可能如下所示:

model: xyz, attr_1: xyz, attr_2: xyz
model: xyz, attr_3: xyz, attr_4: xyz
model: xyz, attr_1: xyz, attr_4: xyz
model: xyz, attr_1: xyz, attr_5: xyz
model: xyz, attr_15: xyz, attr_20: xyz

This data will generate this DML:

此数据将生成此DML:

insert into table (model, attr_1, attr_2)values('xyz','xyz','xyz');
insert into table (model, attr_3, attr_4)values('xyz','xyz','xyz');
insert into table (model, attr_1, attr_4)values('xyz','xyz','xyz');
insert into table (model, attr_1, attr_5)values('xyz','xyz','xyz');
insert into table (model, attr_15, attr_20)values('xyz','xyz','xyz');

My problem is that I can't define the table before the insert commands so I can't know the columns and in every new insert I may discover new columns. I can't get all the insert commands before the actual insert. The only thing I think of is to insert every row to different table (using create table as insert into) and then use UNION ALL to create the final table. But this sound not so good idea.

我的问题是我无法在插入命令之前定义表,因此我无法知道列,并且在每个新插入中我可能会发现新列。在实际插入之前,我无法获取所有插入命令。我唯一想到的是将每一行插入到不同的表中(使用create table作为insert into),然后使用UNION ALL创建最终表。但这听起来并不是一个好主意。

EDIT I don't looking for normalized table.

编辑我不寻找规范化的表。

The end result should be(as for the example):

最终结果应该是(例如):

table_name
id       int
model    varchar
attr_1   varchar
attr_2   varchar
attr_3   varchar
attr_4   varchar
attr_5   varchar
attr_15  varchar
attr_20  varchar

3 个解决方案

#1

There's a really simple solution to this. You need to change your table:

这有一个非常简单的解决方案。你需要改变你的表:

table: model
modelName attribute value
xyz       1          xyz
xyz       2          xyz

Then when you do the INSERT, you would do:

然后当你执行INSERT时,你会这样做:

INSERT INTO `model` (`modelName`, `attribute`, `value`) VALUES ('xyz', 1, 'xyz')

This is a normalized table structure that allows for n amount of attributes.

这是一个规范化的表结构,允许n个属性。

#2

If you use an Array to get your data then you could use PHP's implode(', ', $array). But, you may not be using PHP. If that's the case you could always just concatenate what you're INSERTing with ,.

如果您使用数组来获取数据,那么您可以使用PHP的implode(',',$ array)。但是,您可能没有使用PHP。如果是这种情况,你可以随时连接你正在插入的内容,。

#3

Right solution is to normalize your schema.

正确的解决方案是规范化您的架构。

Create 2 tables: master table for main model - pretty much what you have now, but without attributes, and slave table to keep attributes. Something like this:

创建2个表:主模型的主表 - 几乎就是你现在拥有的,但是没有属性,而slave表来保存属性。像这样的东西:

CREATE TABLE master (
    master_id INTEGER PRIMARY KEY AUTOINCREMENT,
    model VARCHAR(50)
);

CREATE TABLE attrs (
    attr_id INTEGER PRIMARY KEY AUTOINCREMENT,
    master_id INTEGER NOT NULL,
    attr_name VARCHAR(20)
);

This schema is rather compact and has some important properties. For example, it allows you to keep arbitrary number of attributes associated with given model - it could be 0, or it could be 1000.

此架构相当紧凑,具有一些重要属性。例如,它允许您保留与给定模型关联的任意数量的属性 - 它可以是0,或者可以是1000。

To insert data, you will need insert in master table first, and then to attrs table.

要插入数据,首先需要在主表中插入,然后在attrs表中插入。

To retrieve data, use simple join like this:

要检索数据,请使用以下简单连接:

SELECT m.model,
       a.attr_name
FROM master m
JOIN attrs a ON m.model_id = a.model_id
WHERE ...

#1