使用LEFT JOIN和INNER JOIN进行插入

Question

大家好，我正在尝试弄清如何使用以下查询插入新记录：

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;

到目前为止，我的INSERT查询是这样的：

INSERT INTO user 
SELECT * 
FROM user 
  LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id;

然而，我不确定在使用左连接和内连接时如何执行VALUE('','','','',等等)。

因此，我要做的是：

User表：

id    | name       | username    | password                 | OptIn
--------------------------------------------------------------------
562     Bob Barker   bBarker       [e-mail]   1

还有 user_permission 表

user_id   | Permission_id
-------------------------
562         4

更新 像这样吗？

INSERT INTO user (name, username, password, email, opted_in) VALUES ('Bbarker','Bbarker','blahblahblah','[e-mail]',0);
INSERT INTO user_permission (user_id, permission_id) VALUES (LAST_INSERT_ID(),4);

- StealthRT

---

你刚从 user 中选择了值，为什么要插入到 user 中呢？你打算插入哪一行新数据？ - Michael Berkowski

@AaronJSpetner 当您提出编辑建议时，请确保它是完整的编辑，以便不需要后续编辑来修复大小写、标点和删除类似于“大家好”的无用文本。 - mickmackusa

Answer 1

在选择列时，您必须明确指定要选择的列。如果您的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

然而，在这里似乎没有任何理由加入user_permission，因为该表中没有任何列将被插入到user中。实际上，这个INSERT似乎注定会失败，因为主键冲突。

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)

或者使用触发器：

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

- Michael Berkowski

---

我需要在用户表格中插入一行，并且同时也要在用户权限表格中插入一行。我该如何做到这一点？ - StealthRT

我更新了我的原帖，以更好地解释我想要做的事情。 - StealthRT

@StealthRT，你不能在不使用多个语句或第一个表上的触发器的情况下向2个表中插入新行。 - Michael Berkowski

我更新了我的OP，以反映我认为你在帖子中的意思。如果正确，请告诉我。我需要在两个插入之间运行“update”吗？ - StealthRT

是的，就像这样 - 但你必须立即执行第二个操作，否则 LAST_INSERT_ID() 的值将变为无效。LAST_INSERT_ID() 是依赖于连接的，只要在同一个连接上按顺序执行它们，你就会得到正确的返回值。如果在它们之间插入了其他插入操作，你将得到错误的值。 - Michael Berkowski

Answer 2

在使用另一个SELECT查询插入数据时，无法使用VALUES子句。请参见INSERT SYNTAX。

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

- Omesh

Answer 3

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
)

- kajol