MySQL 两个表的 SQL INSERT 语句同时使用 INNER JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10617239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL INSERT statement for TWO TABLES at time with INNER JOIN
提问by Rafee
I have two tables helloand login_tableand below is their structure
我有两个表hello和login_table以下是它们的结构
user_info
-------
some_id | name | address
login_table
-------
id | username | password
some_idand idare autoincrement indexes.
some_id并且id是自增索引。
Now how can i use INSERTstatement with INNER JOINin SQL
现在我如何使用INSERT带有INNER JOINin 的语句SQL
at present, i want add below data with same some_idand id
目前，我想添加以下具有相同some_id和id
`name` = John
`address` = wall street
`username` = john123
`password` = passw123
below code shows, what i have tried so far.
下面的代码显示，到目前为止我已经尝试过。
insert into login_table lt
INNER JOIN user_info ui ON ui.some_id = lt.id
(ui.name, ui.address, lt.username, lt.password)
values
('John', 'wall street', 'john123', 'passw123')
And this is not the one value, i want to add more than one value at a time.. how can i achieve.
这不是一个值，我想一次添加多个值..我怎么能实现。
thanks for help.
感谢帮助。
回答by eggyal
If you need to perform the two INSERToperations atomically, use a transaction:
如果您需要以INSERT原子方式执行这两个操作，请使用事务：
START TRANSACTION;
INSERT INTO login_table (username, password) VALUES ('john123', 'passw123');
INSERT INTO user_info (name, address) VALUES ('John', 'wall street');
COMMIT;
N.B. Your storage engine must support transactions for this to work (e.g. InnoDB).
注意您的存储引擎必须支持事务才能工作（例如InnoDB）。
To insert multiple values into a table at once, use the multiple rows form of INSERT. As stated in the manual:
要一次向表中插入多个值，请使用 的多行形式INSERT。如手册中所述：
INSERTstatements that useVALUESsyntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);The values list for each row must be enclosed within parentheses. The following statement is illegal because the number of values in the list does not match the number of column names:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
VALUEis a synonym forVALUESin this context. Neither implies anything about the number of values lists, and either may be used whether there is a single values list or multiple lists.
INSERT使用VALUES语法的语句可以插入多行。为此，请包含多个列值列表，每个列表都用括号括起来并用逗号分隔。例子：INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);每行的值列表必须括在括号内。以下语句是非法的，因为列表中的值数与列名数不匹配：
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
VALUEVALUES在这种情况下是同义词。两者都没有暗示值列表的数量，无论是单个值列表还是多个列表，都可以使用两者。
回答by Adrian Serafin
Insert to two tables is impossible. The second part of your question is possible: you can insert multiple rows in one statement like this:
插入两个表是不可能的。您的问题的第二部分是可能的：您可以在一个语句中插入多行，如下所示：
insert into some_table(col1, col2) values (1,2), (3,4), (5,6);
