如何将数据插入涉及外键依赖关系的多个表(MySQL) [英] How to insert data to multiple tables with foreign key dependencies involved (MySQL)

问题描述

我正在寻找将数据插入多个 MySQL 表的最佳实践方法，其中某些列是外键依赖项.下面是一个例子:

I am looking for the best-practice way to insert data to multiple MySQL tables where some columns are foreign key dependencies. Here is an example:

Table: contacts
--------------------------------------------------------------------
|  contact_id  |  first_name  | last_name  |  prof_id  |  zip_code  |
--------------------------------------------------------------------

'contacts' 表有 PRIMARY KEY (contact_id)，它只是自动递增，还有 FOREIGN KEY (prof_id) REFERENCES 'profession' 表，FOREIGN KEY (zip_code) REFERENCES 'zip_code' 表.这些表看起来像这样:

The 'contacts' table has PRIMARY KEY (contact_id) which simply auto_increments, and FOREIGN KEY (prof_id) REFERENCES 'profession' table, FOREIGN KEY (zip_code) REFERENCES 'zip_code' table. Those tables would look something like this:

Table: profession
----------------------------
|  prof_id  |  profession  |
----------------------------

其中 'prof_id' 是一个 INT NOT NULL AUTO_INCREMENT PRIMARY KEY，并且

where 'prof_id' is an INT NOT NULL AUTO_INCREMENT PRIMARY KEY, and

Table: zip_code
--------------------------------
|  zip_code  | city  |  state  |
--------------------------------

其中 'zip_code' 是一个 INT(5) NOT NULL PRIMARY KEY.

where 'zip_code' is an INT(5) NOT NULL PRIMARY KEY.

我有一个要为其添加记录的新人，例如:

I have a new person I want to add a record for, let's say:

first_name = 'John', last_name = 'Doe', profession = 'Network Administrator', city = 'Sometown', state = 'NY', zip_code = 12345

<小时>

这就是我想要做的:我想获取该信息并将其插入到适当的表和列中.为了防止列中出现重复值(例如职业)，我首先要确保还没有网络管理员"条目，如果有，我只想获取其键值，如果不要插入它然后获取它的键值.zip_code、city、state 也是如此——如果它存在，就使用那个 zip_code 键，否则插入新数据并获取关联的键.然后，最后，我想使用提供的信息将新联系人输入到联系人"表中，包括与其他表中的职业和位置相关的适当键值.

---

Here's what I'm trying to do: I want to take that information and insert it into the appropriate tables and columns. To prevent duplicate values in a column (like profession for example) I'd first want to make sure there isn't already an entry for "Network Administrator", and if there was I'd like to just get its key value, if not insert it and then get its key value. The same goes for zip_code, city, state - if it exists just use that zip_code key, otherwise insert the new data and grab the associated key. Then, finally, I'd want to enter the new contact to 'contact' table using the supplied information, including the appropriate key values associated with profession and location from the other tables.

我的问题是，最好的推荐方法是什么?我知道我可以坐在这里写一个语句来检查给定的职业是否存在，如果不存在则输入它，然后获取密钥.对 zip_code 执行相同的操作.然后最后将所有这些插入到联系人中，但是我知道必须有更好的方法来用更少的(可能是一个)语句来完成此操作，尤其是考虑到这可能会导致问题，例如，如果数据库在中途脱机片刻所有这一切.有没有办法在这个 INSERT 中使用 JOIN 来基本上让所有东西级联到正确的位置?我应该使用 TRANSACTION 系列语句来处理这个问题吗?

My question is, what is the best recommended way to do this? I know I can sit here and write single statements to check if the given profession exists, if not then enter it, then get the key. Do the same for zip_code. Then finally Insert all of that into contacts, however I know there must be a better way to accomplish this in fewer (perhaps one) statement, especially considering this could cause a problem if, say, the database went offline for a moment in the midst of all of this. Is there a way to use JOINs with this INSERT to essentially have everything cascade into the correct place? Should I look to handle this with a TRANSACTION series of statements?

我正处于使用 SQL 的学习阶段，但我觉得到目前为止我使用的书籍和资源已经在假设我们已经填充了所有这些数据表的情况下跳转到使用嵌套查询和 JOINS.我什至愿意接受关于我应该在谷歌上搜索什么以更好地学习这一点的建议，或者任何可以帮助填补这一空白的资源.理想情况下，我很想看到一些可以运行的 SQL 代码来执行此操作.如有必要，假设 PHP 作为与数据库交互的语言，但命令行 sql 是我的目标.提前致谢，希望我说清楚了.

I am in the learning stage with SQL but I feel that the books and resources I have used thus far have sort of jumped to using nested queries and JOINS under the assumption we have all of these tables of data already populated. I'm even open to suggestions on WHAT I should be Googling to better learn this, or any resources that can help fill this gap. Ideally, I'd love to see some functioning SQL code to do this though. If necessary, assume PHP as the language to interact with the database, but command-line sql is what I was aiming for. Thanks ahead of time, hopefully I made everything clear.

推荐答案

总之，你要使用 交易 (有关此的更多文档)，以便您的插入是原子.这是确保插入所有(或不插入)数据的唯一方法.否则，您可能会遇到您描述的情况，即在某些插入后数据库变得不可用而其他插入无法完成.一个事务告诉数据库你正在做的要么全有要么全无，因此如果出现问题，它应该回滚.

In short, you want to use transactions (more doc on this) so that your inserts are atomic. This is the only way to guarantee that all (or none) of your data will be inserted. Otherwise, you can get into the situation you describe where the database becomes unavailable after some insertions and others are unable to complete. A transaction tells the database that what you are doing is all-or-nothing and so it should roll back if something goes wrong.

当您使用合成主键时，PHP 和其他语言提供了获取最后插入的 id 的机制.如果你想完全在 MySQL 中完成，你可以使用 LAST_INSERT_ID() 函数.你会得到这样的代码:

When you are using synthetic primary keys, as you are, PHP and other languages provide mechanisms for getting the last inserted id. If you want to do it entirely in MySQL you can use the LAST_INSERT_ID() function. You will end up with code like this:

START TRANSACTION;
INSERT INTO foo (auto,text)
    VALUES(NULL,'text');         # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table
COMMIT;

这篇关于如何将数据插入涉及外键依赖关系的多个表(MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！