問題描述
我嘗試在同一個查詢的多個表中搜索insert信息，但發(fā)現(xiàn)這是不可能的?所以我想通過簡單地使用多個查詢來 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 命令中插入多個表.但是，您可以使用事務(wù).
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.
你說經(jīng)過這么長時間的嘗試，它仍然不起作用.我不能簡單地將剛剛生成的 ID 放在 $var 中并將該 $var 放在所有 MySQL 命令中嗎?"
讓我詳細(xì)說明:這里有 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.
不幸的是，當(dāng)?shù)诙l語句本身在具有自動遞增列的表中插入行時，LAST_INSERT_ID() 將更新為表 2 的，而不是表 1.如果您仍然之后需要表 1 的內(nèi)容，我們將不得不將其存儲在一個變量中.這將我們引向方式 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 變量(或任何語言可以連接到您的數(shù)據(jù)庫選擇):
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 中執(zhí)行該文字語句，或者使用例如 php 的mysql_insert_id()為您執(zhí)行此操作 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'smysql_insert_id()which does that for you INSERT [use your php variable here]
警告
無論您選擇哪種解決方法，您都必須決定應(yīng)該發(fā)生什么執(zhí)行在查詢之間中斷(例如，您的數(shù)據(jù)庫服務(wù)器崩潰).如果您可以忍受有些已經(jīng)完成，有些還沒有"，請不要繼續(xù)閱讀.
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.
但是，如果您決定要么完成所有查詢，要么不完成 - 我不希望某些表中的行但其他表中沒有匹配的行，我總是希望我的數(shù)據(jù)庫表保持一致"，您需要包裝所有交易中的語句.這就是我在這里使用 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.
這篇關(guān)于MySQL插入多個表?(數(shù)據(jù)庫規(guī)范化?)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！