如何将数据插入到包含有外键依赖关系的多个表中(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:

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

Table: contacts
--------------------------------------------------------------------
|  contact_id  |  first_name  | last_name  |  prof_id  |  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:

“contacts”表有一个主键(contact_id)，它只是自动递增的，而外键(prof_id)引用“专业”表，外键(zip_code)引用“zip_code”表。这些表格看起来是这样的:

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

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

其中'prof_id'是一个INT类型的非空AUTO_INCREMENT主键，以及

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

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

其中“zip_code”是一个INT(5)而不是NULL主键。

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

---

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.

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

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?

我的问题是，最好的推荐方式是什么?我知道我可以坐在这里写一个声明来检查给定的职业是否存在，如果不存在，那么输入它，然后获取密钥。对zip_code也是如此。然后，最后将所有这些插入到联系人中，但是我知道在更少的(也许是一种)声明中必须有更好的方法来完成这个任务，特别是考虑到这可能会导致一个问题，比如说，数据库在所有这些中处于离线状态。是否有一种方法可以使用这个INSERT中的join来将所有的东西串联到正确的位置?我应该使用一系列事务语句来处理这个问题吗?

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.

我在SQL的学习阶段，但是我觉得我所使用的书籍和资源已经开始使用嵌套的查询，并加入到假设我们已经填充了所有这些数据表的假设下。我甚至愿意接受关于我应该在谷歌上搜索什么来更好地了解这一点的建议，或者任何可以帮助填补这一空白的资源。理想情况下，我希望看到一些功能正常的SQL代码来实现这一点。如果需要，假设PHP是与数据库交互的语言，但是命令行sql是我的目标。谢谢大家，希望我讲得很清楚。

1 个解决方案

#1

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.

简而言之，您希望使用事务(关于此的更多文档)，以便插入是原子的。这是确保插入所有(或没有)数据的唯一方法。否则，您可能会遇到这样的情况:在某些插入之后，数据库变得不可用，而另一些则无法完成。事务告诉数据库，您正在做的是“要么全部要么没有”，因此如果出现问题，应该回滚。

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:

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

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;