MySQL插入多个表?(数据库规范化?)

2021-11-20 00:00:00 mysql

我尝试在同一个查询的多个表中搜索<​​code>insert信息，但发现这是不可能的?所以我想通过简单地使用多个查询来 insert 它，即;

I tried searching a way to insert information in multiple tables in the same query, but found out it's impossible? So I want to insert it by simply using multiple queries i.e;

INSERT INTO users (username, password) VALUES('test', 'test')
INSERT INTO profiles (userid, bio, homepage) VALUES('[id of the user here?]','Hello world!', 'http://www.stackoverflow.com')

但是如何将 users 的自动增量 id 提供给 profile<的手动"userid/code> 表?

But how can I give the auto-increment id from the users to the "manual" userid for the profile table?

推荐答案

不，您不能在一个 MySQL 命令中插入多个表.但是，您可以使用事务.

No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

BEGIN;
INSERT INTO users (username, password)
  VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage) 
  VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;

看看LAST_INSERT_ID() 重用自增值.

Have a look at LAST_INSERT_ID() to reuse autoincrement values.

你说经过这么长时间的尝试，它仍然不起作用.我不能简单地将刚刚生成的 ID 放在 $var 中并将该 $var 放在所有 MySQL 命令中吗?"

让我详细说明:这里有 3 种可能的方法:

Let me elaborate: there are 3 possible ways here:

  1. 在上面的代码中.这个这一切都在 MySQL 中完成，并且LAST_INSERT_ID() 在第二个语句将自动成为自动增量列的值插入第一个声明.

  1. In the code you see above. This does it all in MySQL, and the LAST_INSERT_ID() in the second statement will automatically be the value of the autoincrement-column that was inserted in the first statement.

不幸的是，当第二条语句本身在具有自动递增列的表中插入行时，LAST_INSERT_ID() 将更新为表 2 的，而不是表 1.如果您仍然之后需要表 1 的内容，我们将不得不将其存储在一个变量中.这将我们引向方式 2 和方式 3:

Unfortunately, when the second statement itself inserts rows in a table with an auto-increment column, the LAST_INSERT_ID() will be updated to that of table 2, and not table 1. If you still need that of table 1 afterwards, we will have to store it in a variable. This leads us to ways 2 and 3:

将把 LAST_INSERT_ID() 存入一个 MySQL 变量:

Will stock the LAST_INSERT_ID() in a MySQL variable:

INSERT ...
SELECT LAST_INSERT_ID() INTO @mysql_variable_here;
INSERT INTO table2 (@mysql_variable_here, ...);
INSERT INTO table3 (@mysql_variable_here, ...);

  • 将把 LAST_INSERT_ID() 存入一个php 变量(或任何语言可以连接到您的数据库选择):

  • Will stock the LAST_INSERT_ID() in a php variable (or any language that can connect to a database, of your choice):

    • 插入...
    • 使用您的语言来检索LAST_INSERT_ID()，方法是在 MySQL 中执行该文字语句，或者使用例如 php 的 mysql_insert_id() 为您执行此操作
    • INSERT [在此处使用您的 php 变量]
    • INSERT ...
    • Use your language to retrieve the LAST_INSERT_ID(), either by executing that literal statement in MySQL, or using for example php's mysql_insert_id() which does that for you
    • INSERT [use your php variable here]

    警告

    无论您选择哪种解决方法，您都必须决定应该发生什么执行在查询之间中断(例如，您的数据库服务器崩溃).如果您可以忍受有些已经完成，有些还没有"，请不要继续阅读.

    WARNING

    Whatever way of solving this you choose, you must decide what should happen should the execution be interrupted between queries (for example, your database-server crashes). If you can live with "some have finished, others not", don't read on.

    但是，如果您决定要么完成所有查询，要么不完成 - 我不希望某些表中的行但其他表中没有匹配的行，我总是希望我的数据库表保持一致"，您需要包装所有交易中的语句.这就是我在这里使用 BEGIN 和 COMMIT 的原因.

    If however, you decide "either all queries finish, or none finish - I do not want rows in some tables but no matching rows in others, I always want my database tables to be consistent", you need to wrap all statements in a transaction. That's why I used the BEGIN and COMMIT here.

    • 相关文章