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

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, ...);