MYSQL，PHP插入到数据库中的多个表中 [英] MYSQL, PHP Insert into Multiple Tables in Database

问题描述

我试图插入信息到数据库中的多个表，我已经设法使它使用这个工作：

I'm trying to insert information into multiple tables within a database, i have managed to get it to work using this:

  $query = "INSERT INTO users (grp, email, college_id, tutor, year, password, register_date) VALUES ('$g', '$e', '$ci', '$tu', '$y', PASSWORD('$p'), NOW() )";
  $query2 = "INSERT INTO unit_26 (college_id) VALUES ('$ci')";
  $result = mysql_query ($query); // Run the Query Now woooo. 
  $result2 = mysql_query ($query2); // Run the Query Now woooo. 
  if ($result) { // If it Ran OK.

虽然它的工作原理和信息被添加到这两个表，我只是想知道，

Although it works and the information is added to both tables, I was just wondering if anyone has a better way of doing this, or of this way is wrong?

推荐答案

由于这两个插入是独立执行的，因此另一个同时运行的程序可能会看到数据库处于第一次插入的状态，但第二次不插入。

Since these two inserts are executed independently, another program running concurrently might see the database in a state where the first insert is done but the second isn't.

这是否是一个问题取决于应用程序逻辑。在你的情况下，很难说没有其他信息。可能不会。涉及两个帐户的金融交易是一个例子，其中这是问题：您不希望所有帐户余额的总和在任何时间是错误的。

Whether this is a problem or not depends on the application logic. In your case it's hard to tell without additional information. Probably not. A financial transactions involving two accounts is an example where this is a problem: you don't want the sum of all account balances to be wrong at any time.

如果你认为你需要这个，你可以使操作以性能为代价：另一个程序将在第一次插入之前或在第二次插入之后看到数据库。它的工作原理如下：

If you think you need this, you can make the operation atomic at the cost of performance: another program will either see the database before the first insert, or after the second insert. It works like this:

$result = FALSE;
if (mysql_query('BEGIN')) {
    if (mysql_query($query1) &&
        mysql_query($query2))
        $result = mysql_query('COMMIT'); // both queries looked OK, save
    else
        mysql_query('ROLLBACK'); // problems with queries, no changes
}

存储引擎必须支持事务，即，它必须是 InnoDB 。否则这将无法工作。

The storage engine has to support transactions, i.e., it has to be InnoDB. Otherwise this will silently not work.

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