假设我有一个用于存储(数字)数据点的数据库。数据点被分组成观测值。每个数据点属于一个或多个观测值,每个观测值有一个或多个数据点。因此,我有三个表:
CREATE TABLE `data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` datetime NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB ;
CREATE TABLE `obs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`posthoc` tinyint(1) NOT NULL,
`comments` varchar(500) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB ;
CREATE TABLE `on_obs_data` (
# linker or bridge table or whatever these are called
`id_obs` int(11) NOT NULL,
`id_data` int(11) NOT NULL,
KEY `id_obs` (`id_obs`),
KEY `id_data` (`id_data`),
CONSTRAINT `on_obs_data_ibfk_1` FOREIGN KEY (`id_obs`) REFERENCES `obs` (`id`),
CONSTRAINT `on_obs_data_ibfk_2` FOREIGN KEY (`id_data`) REFERENCES `data` (`id`)
) ENGINE=InnoDB ;
问题是,我如何从一个电子表格填充这三个表(或者根据情况,通过LOAD DATA LOCAL INFILE
填充一个中间表)?我可以分别填充data
和obs
,没有任何问题,但是on_obs_data
需要知道这两个表中新创建条目的id。data
和obs
之间的信息没有重叠,并且不保证它们中的条目是唯一的,除了数据库在插入时生成的id
之外。将给定的data
条目与给定的obs
条目联系起来的唯一原因是它们最初位于电子表格的同一行。
我正在寻找可以在MySQL内部实现的解决方案,而不依赖于客户端脚本。
考虑到这对于规范化数据库中的引用完整性至关重要,我很惊讶没有一个干净或广泛宣传的模式,但以下是我想到的:
- 确保
data
和obs
表除了上面示例代码中的字段外还有一个额外的字段。我们叫它tempID
。 - 创建临时表*时,创建一个唯一id列。
- 在
data
和obs
表中正常插入从此表中选择的字段,并将中间表中的ID字段分别插入data
和obs
表的tempID
字段。 -
insert into on_obs_data (id_obs,id_data) select obs.id,data.id from obs,data where obs.tempID is not NULL and data.tempID is not NULL and obs.tempID = data.tempID
-
update obs set tempID = NULL; update data set tempID = NULL;
*我故意说"临时表"而不是"临时表",因为显然MySQL不允许临时表有自动递增的ID字段。ಠ_ಠ
但是有些事情仍然困扰着我——我认为这将是任何人试图更新规范化数据库都会遇到的第一个问题之一。下意识的假设是"MySQL是愚蠢的"或者"这些MySQL大师知道的不多",但是我已经知道,当我试图做出这样的假设时,通常是我忽视了一些显而易见的事情,而其他人都知道。那么,MySQL社区,我只是重新发明了轮子吗?有没有更简单的方法更新桥牌表?还是我用错了术语没人能回答这个问题因为没人理解?