MySQL 使用 LEFT JOIN 和 INNER JOIN 插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12073513/
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
Insert using LEFT JOIN and INNER JOIN
提问by StealthRT
Hey all i am trying to figure out how to go about inserting a new record using the following query:
大家好，我想弄清楚如何使用以下查询插入新记录：
SELECT user.id, user.name, user.username, user.email,
IF(user.opted_in = 0, 'NO', 'YES') AS optedIn
FROM
user
LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id
ORDER BY user.id;
My INSERTquery so far is this:
INSERT到目前为止，我的查询是这样的：
INSERT INTO user
SELECT *
FROM user
LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id;
However, i am not sure how to do VALUE('','','','', etc etc)when using left and inner joins.
但是，我不确定VALUE('','','','', etc etc)在使用左联接和内联接时该怎么做。
So what i am looking to do is this:
所以我要做的是：
Usertable:
User桌子：
id | name | username | password | OptIn
--------------------------------------------------------------------
562 Bob Barker bBarker [email protected] 1
And also the user_permissiontable
还有user_permission桌子
user_id | Permission_id
-------------------------
562 4
UPDATESo like this?
更新像这样吗？
INSERT INTO user (name, username, password, email, opted_in) VALUES ('Bbarker','Bbarker','blahblahblah','[email protected]',0);
INSERT INTO user_permission (user_id, permission_id) VALUES (LAST_INSERT_ID(),4);
回答by Michael Berkowski
You have to be specific about the columns you are selecting. If your usertable had four columns id, name, username, opted_inyou must select exactly those four columns from the query. The syntax looks like:
您必须具体说明您选择的列。如果您的user表有四列，id, name, username, opted_in您必须从查询中准确选择这四列。语法如下：
INSERT INTO user (id, name, username, opted_in)
SELECT id, name, username, opted_in
FROM user LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id
However, there does not appear to be any reason to join against user_permissionhere, since none of the columns from that table would be inserted into user. In fact, this INSERTseems bound to fail with primary key uniqueness violations.
但是，似乎没有任何理由在user_permission这里加入，因为该表中的任何列都不会插入到user. 事实上，这INSERT似乎必然会因违反主键唯一性而失败。
MySQL does not support inserts into multiple tables at the same time. You either need to perform two INSERTstatements in your code, using the last insert id from the first query, or create an AFTER INSERTtrigger on the primary table.
MySQL 不支持同时插入多个表。您需要INSERT在代码中执行两个语句，使用第一个查询中的最后一个插入 ID，或者AFTER INSERT在主表上创建一个触发器。
INSERT INTO user (name, username, email, opted_in) VALUES ('a','b','c',0);
/* Gets the id of the new row and inserts into the other table */
INSERT INTO user_permission (user_id, permission_id) VALUES (LAST_INSERT_ID(), 4)
Or using a trigger:
或者使用触发器：
CREATE TRIGGER creat_perms AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
INSERT INTO user_permission (user_id, permission_id) VALUES (NEW.id, 4)
END
回答by Omesh
you can't use VALUESclause when inserting data using another SELECTquery. see INSERT SYNTAX
VALUES使用另一个SELECT查询插入数据时不能使用子句。见插入语法
INSERT INTO user
(
id, name, username, email, opted_in
)
(
SELECT id, name, username, email, opted_in
FROM user
LEFT JOIN user_permission AS userPerm
ON user.id = userPerm.user_id
);
回答by kajol
INSERT INTO Test([col1],[col2]) (SELECT a.Name AS [col1],b.sub AS [col2] FROM IdTable b INNER JOIN Nametable a ON b.no = a.no)
