用联结表插入数据 [英] Data insertion with junction tables

问题描述

我正在使用CodeIgniter中的联结表进行第一个主要数据库设计，但我不太确定如何处理数据插入。

假设我有两个表， some_table & another_table ，带有联结表， some_another 。现在，如果我想向 some_table 添加一条记录，是否还要为联结表编写单独的插入查询，还是有一种更好，更自动化的方式来处理此问题？ / p>

例如，我是否必须这样做：

插入some_table（col1 ，col2，col3）值（val1，val2，val3）

将内容插入some_another（col01，col02）值（val01，val02）

每条记录，或者有没有一种方法或工具可以为我处理更新联结表？ p>

如果我需要提供其他信息，请告诉我。谢谢！

解决方案

取决于连接表的目的。如果只想在其他两个表之间联接所有记录，则可以使用联接。如果要多对多关联，实际上并没有自动知道应该关联哪些关联的方法。

但是，如果要模拟联接并将所有关系简单地预加载到多对多表中（如果您希望以后可以禁用或操作的默认关系很有用），则可以为此使用触发器。

无论如何，您将需要对每个表使用单独的查询，因为MySQL不能通过单个查询将其插入到多个表中。

I'm working on my first major database design with junction tables in CodeIgniter, and I'm not too sure how to handle data insertion.

Let's say I have two tables, some_table & another_table, with a junction table, some_another. Now if I want to add a record to some_table, do I also write separate insertion queries for the junction table, or is there a better, more automated way to handle this?

For example, do I have to do this:

INSERT INTO some_table (col1, col2, col3) VALUES (val1, val2, val3)

INSERT INTO some_another (col01, col02) VALUES (val01, val02)

for each record, or is there a way or a tool that handles also updating junction tables for me?

If there's any other info I should provide, please let me know. Thanks!

解决方案

Depends on the purpose of the "junction table." If you just want a join of all records between the other two tables, use a join for that. If you want a "many to many" correlation, there's really not an automated way of knowing which ones should be associated.

If, however, you want to simulate a join and simply "preload" all the relationships into a many-to-many table (useful if you want default relationships that could be disabled or manipulated later), you could use triggers for that.

Regardless, you'll need to use separate queries for every table, as MySQL can't insert into multiple tables with a single query.

这篇关于用联结表插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！