嗨,我正在尝试找出如何使用以下查询插入新记录的方法:
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
INSERT INTO user SELECT * FROM user LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id;
但是,我不确定VALUE('','','','', etc etc)使用左联接和内联接时该怎么做。
VALUE('','','','', etc etc)
所以我想做的是这样的:
User 表:
User
id | name | username | password | OptIn -------------------------------------------------------------------- 562 Bob Barker bBarker BBarker@priceisright.com 1
还有user_permission桌子
user_permission
user_id | Permission_id ------------------------- 562 4
更新 是这样的吗?
INSERT INTO user (name, username, password, email, opted_in) VALUES ('Bbarker','Bbarker','blahblahblah','Bbarker@priceisright.com',0); INSERT INTO user_permission (user_id, permission_id) VALUES (LAST_INSERT_ID(),4);
您必须具体说明要选择的列。如果user表有四列id, name, username, opted_in,则必须从查询中准确选择这四列。语法如下:
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在主表上创建触发器。
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