MySQL插入多个表? (数据库规范化?) [英] MySQL Insert into multiple tables? (Database normalization?)
问题描述
我尝试在同一查询中搜索多个表中的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')
但是如何为profile表将自动增量id从users赋予手动" userid?
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.
让我详细说明:这里有3种可能的方式:
Let me elaborate: there are 3 possible ways here:
-
在上面的代码中.这 在MySQL中完成所有操作，并且
LAST_INSERT_ID()在第二个 语句将自动为 自动增量列的值 插入第一个 声明.
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):
-
INSERT ... - 使用您的语言来检索
LAST_INSERT_ID()，方法是在MySQL中执行该文字语句，或者使用例如php的mysql_insert_id()为您执行此操作 -
INSERT [use your php variable here]
INSERT ...- Use your language to retrieve the
LAST_INSERT_ID(), either by executing that literal statement in MySQL, or using for example php'smysql_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.
如果您需要更多信息，请再次评论:)
Comment again if you need more info :)
这篇关于MySQL插入多个表? (数据库规范化?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
