mysql -> 插入到 tbl（从另一个表中选择）和一些默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5907206/
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 tbl (select from another table) and some default values
提问by wannabeprogrammer
As the title says, I am trying to insert into one table selecting values from another table and some default values.
正如标题所说，我试图将另一个表中的值和一些默认值插入一个表中。
INSERT INTO def (catid, title, page, publish)
(SELECT catid, title from abc),'page','yes')
INSERT INTO def (catid, title, page, publish)
VALUES
((SELECT catid, title from abc),'page','yes'))
The first query gives a mysql error and the second one gives column count does not match.
第一个查询给出了一个 mysql 错误，第二个给出了列数不匹配。
What do I need to do?
我需要做什么？
回答by Nican
You simply have to do:
你只需要做：
INSERT INTO def (catid, title, page, publish)
SELECT catid, title, 'page','yes' from `abc`
回答by kanishka vatsa
If you want to insert all the columns then
如果要插入所有列，则
insert into def select * from abc;
here the number of columns in def should be equal to abc.
这里 def 中的列数应该等于 abc。
if you want to insert the subsets of columns then
如果要插入列的子集，则
insert into def (col1,col2, col3 ) select scol1,scol2,scol3 from abc;
if you want to insert some hardcorded values then
如果你想插入一些硬编码的值，那么
insert into def (col1, col2,col3) select 'hardcoded value',scol2, scol3 from abc;
回答by Frankie
INSERT INTO def (field_1, field_2, field3)
VALUES
('$field_1', (SELECT id_user from user_table where name = 'jhon'), '$field3')
回答by Redips77
If you you want to copy a sub-set of the source table you can do:
如果要复制源表的子集，可以执行以下操作：
INSERT INTO def (field_1, field_2, field3)
SELECT other_field_1, other_field_2, other_field_3 from `abc`
or to copy ALL fieldsfrom the source table to destination table you can do more simply:
或者要将源表中的所有字段复制到目标表，您可以更简单地执行以下操作：
INSERT INTO def
SELECT * from `abc`
回答by crmpicco
With MySQL if you are inserting into a table that has a auto increment primary key and you want to use a built-in MySQL function such as NOW()then you can do something like this:
使用 MySQL，如果您要插入具有自动增量主键的表，并且您想使用内置的 MySQL 函数，NOW()那么您可以执行以下操作：
INSERT INTO course_payment
SELECT NULL, order_id, payment_gateway, total_amt, charge_amt, refund_amt, NOW()
FROM orders ORDER BY order_id DESC LIMIT 10;
