寻找查询/触发器以干净地从文件插入“多对多”或“桥” MySQL 中的表

发布于 2024-12-11 18:11:12 字数 1229 浏览 1 评论 0原文

假设我有一个用于存储（数字）数据点的数据库。数据点被分组为观察值。每个数据点属于一个或多个观测值，并且每个观测值都有一个或多个数据点。因此，我有三个表：

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 内部实现而不依赖客户端脚本的解决方案。

原文

Let's say I have a database for storing (numeric) datapoints. Datapoints are grouped together into observations. Each datapoint belongs to one or more observations and each observation has one or more datapoints. So, I have three tables:

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 ;

The problem is, how do I populate these three tables from a single spreadsheet (or as the case may be, a single interim table populated via LOAD DATA LOCAL INFILE)? I can populate data and obs individually with no problems, but on_obs_data needs to know the IDs of the newly created entries in the two tables. None of the information between data and obs overlaps, and the entries in the respective are not guaranteed to be unique other than ids which are generated by the database on insert. The only thing linking a given data entry to a given obs entry is the fact that they were originally on the same row of a spreadsheet.

I'm looking for solutions that can be implemented inside MySQL without relying on client-side scripting.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

痴梦一场 2024-12-18 18:11:12

我很惊讶没有一个干净或广为人知的模式，因为这对于规范化数据库中的引用完整性至关重要，但这是我想到的：

确保 data< /code> 和 obs 表除了上面示例代码中的字段外还有一个额外字段。我们将其称为 tempID。确保该字段允许有 NULL 值。
创建临时表*时，创建一列唯一 ID。
插入到 data 和 obs 表中通常从此表中选择的字段，并将临时表中的 ID 字段放入相应的 tempID 字段中data 和 obs 表。
insert into on_obs_data (id_obs,id_data) select obs.id,data.id from obs,data 其中 obs.tempID 不为 NULL 且 data.tempID 不为 NULL 且 obs.tempID = data.tempID
<代码>更新 obs 设置 tempID = NULL; update data set tempID = NULL;

* 我故意说“临时表”而不是“临时表”，因为显然 MySQL 不允许临时表有自动递增的 ID 字段。 ಠ_ಠ

但有些事情仍然困扰着我——我认为这将是任何试图更新规范化数据库的人首先遇到的问题之一。下意识的假设是“MySQL 很愚蠢”或“这些 MySQL 专家了解不多”，但我了解到，当我试图做出这种假设时，我常常会忽略一些其他人都知道的显而易见的事情。那么，MySQL 社区，我是不是重新发明了轮子？你们其他人是否有一些更简单的方法来更新桥接表？或者我使用了错误的术语，没有人能回答这个问题，因为没有人理解它？