MySQL 在从另一个表中选择时自动插入到表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17773832/
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
MySQL insert into table with auto-increment while selecting from another table
提问by CodeKingPlusPlus
I have a table with an auto-increment primary key:
我有一个带有自动递增主键的表：
create table rt_table
(
rtID int PRIMARY KEY AUTO_INCREMENT,
rt_user_id BIGINT, /*user being retweeted*/
rt_user_name varchar(70), /*user name of rt_user_id*/
source_user_id BIGINT, /*user tweeting rt_user_id*/
source_user_name varchar(70), /*user name of source_user_id*/
tweet_id BIGINT, /*fk to table tweets*/
FOREIGN KEY (tweet_id) references tweets(tweet_id)
);
I wish to populate this table from parts of another table:
我希望从另一个表的部分填充此表：
insert into rt_table
select rt_user_id, (select user_name from users u where u.user_id = t.rt_user_id),
source_user_id, (select user_name from users u where u.user_id = t.source_user_id),
tweet_id
from tweets t
where rt_user_id != -1;
I get an error which says the number of columns do not match up, which is because of the primary key (which is an auto-incremented value and thus does not need to be set). How do I get around this?
我收到一个错误，说列数不匹配，这是因为主键（它是一个自动递增的值，因此不需要设置）。我该如何解决这个问题？
回答by Gordon Linoff
You need to explicitly list the columns in the insertstatement:
您需要明确列出insert语句中的列：
insert into rt_table (rt_user_id, rt_user_name, source_user_id, source_user_name, tweet_id)
select rt_user_id, (select user_name from users u where u.user_id = t.rt_user_id),
source_user_id, (select user_name from users u where u.user_id = t.source_user_id),
tweet_id
from tweets t
where rt_user_id != -1;
Also, I think it is better form to use explicit joins, rather than nested selects:
另外，我认为使用显式连接而不是嵌套选择是更好的形式：
insert into rt_table (rt_user_id, rt_user_name, source_user_id, source_user_name, tweet_id)
select t.rt_user_id, u.user_name, t.source_user_id, su.user_name, t.tweet_id
from tweets t left outer join
users u
on t.rt_user_id = u.user_id left outer join
users su
on t.source_user_id = su.user_id
where rt_user_id != -1;
This often (but not always) helps the optimizer find the best query plan.
这通常（但不总是）帮助优化器找到最佳查询计划。
回答by Styphon
You simply set the primary key to NULLduring the insert.
您只需NULL在插入期间将主键设置为。
INSERT INTO rt_table
SELECT
NULL,
rt_user_id,
(SELECT
user_name
FROM
users u
WHERE u.user_id = t.rt_user_id),
source_user_id,
(SELECT
user_name
FROM
users u
WHERE u.user_id = t.source_user_id),
tweet_id
FROM
tweets t
WHERE rt_user_id != - 1 ;
