根据主表主键值向子表插入数据 [英] Inserting data into a child table based on master table primary key values

问题描述

我目前正在与一家电子商务购物网站合作.无需描述用户可以随时随地提交订单.

I'm currently working with an e-commerce shopping site. It's quite unnecessary to describe that users can submit their orders from anywhere at any time.

我在 MySql 数据库中有两个表 order 和 order_details(还有其他的)来维护我们可以轻松想象的订单详细信息，因此我认为，我需要没有描述两个表的整体结构.

I have two tables in MySql database order and order_details (and there are others too) to maintain the order details that we can easily imagine therefore I think, I need not describe the whole structure of both of the tables.

order 表基本上包含像 order_id 这样的字段，它是一个自动递增的主键，order_date，order_status 以及在此上下文中我们不需要的其他一些内容.

The order table basically contains the fields like order_id which an auto-incremented primary key, order_date, order_status and some others that we don't need in this context.

order_details 表基本上包含像 order_id 这样的字段，它是 order 表的外键，<代码>product_name、product_price 等

The order_details table basically contains the fields like order_id which a foreign key of the order table, product_name, product_price etc.

当客户提交订单时，要进行的第一个条目在order 表中，并基于该表中的order_id，该表的详细信息订单被插入到 order_details 表中.

When a customer submits an order, the first entry which is to be made is in the order table and based on the order_id in this table, the details of this order is inserted into the order_details table.

插入order_details 表需要从order 表中获取适当的order_id(该表是自动递增的，因此，我们不能提前拥有).我目前正在从 order 表中获取这个 order_id 类似这样的东西.

Inserting into the order_details table requires an appropriate order_id to be fetched from the order table (which is auto-incremented and therefore, we can not have it in advance). I'm currently fetching this order_id from the order table something like this.

select max(order_id) from order;

此 SQL 语句为我提供了 order 表中的最新 order_id 并使用此值，我可以将特定订单的详细信息插入 order_details 表格.

This SQL statement gives me the latest order_id from the order table and using this value, I can insert the details of the particular order into the order_details table.

现在，让我们假设我当前提交的订单包含购物车中的一些产品.我的订单已成功插入到 order 表中，但在插入 order_details 之前(以及在检索 ma​​x order_id 之前)从 order 表)，这个过程被服务器上的操作系统调度器出于某种原因切换掉，以执行另一个客户的订单，并且该另一个客户的订单的处理已完全完成(插入到两个表、order 和 order_details).

Now, let's assume that I'm currently submitting an order containing some products in the shopping cart. My order is inserted into the order table successfully but before inserting into the order_details (and before retrieving the max order_id from the order table), this process is switched away by the operating system scheduler on the server for some reasons to execute another customer's order and the processing of that another customer's order is fully completed (inserted into both of the tables, the order and the order_details).

在完成此订单处理(另一个客户的订单)后，我的订单仍处于待处理状态，但在从 ma​​x order_id 之前已将其切换掉>order 表.当上面的 SQL 语句被执行时，它会从 order 表中获取与另一个客户的订单相关的 max order_id 和 与我的不相关导致交易处于不一致状态！

After completing this order processing (that another customer's order), my order which is still pending starts executing which was switched away before retrieving the max order_id from the order table. When the above SQL statement is executed, it will fetch the max order_id from the order table which is relevant to that another customer's order and not relevant to mine resulting the transaction in an inconsistent state!

我想问的是如何从 order 表中正确获取适当的 order_id，该表始终与特定订单相关，并保证 任何时候都不会以不一致的状态结束交易.这样做的正确方法是什么?

What I want to ask is that how to correctly fetch an appropriate order_id from the order table which is always relevant to a particular order and that guarantees that no transaction ends with an inconsistent state at any time. What's the correct way to do so?

推荐答案

MySQL 的 LAST_INSERT_ID() 函数将返回当前连接上最后一个 INSERT 操作的第一次成功插入的自动递增值.

MySQL's LAST_INSERT_ID() function will return the auto-incremented value from the first successful insert of the last INSERT operation on the current connection.

您的 MySQL API 通常会为此函数调用提供一些包装器，例如mysql_insert_id() 在 PHP 中.

Your MySQL API will usually provide some wrapper around this function call, e.g. mysql_insert_id() in PHP.

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