MySQL INSERT INTO ... SELECT 没有详细说明所有列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8297484/
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 INTO ... SELECT without detailing all columns
提问by Jonathan
How do you insert selected rows from table_sourceto table_targetusing SQL in MySQL where:
如何从插入选定行table_source到table_targetMySQL中，其中使用SQL：
- Both tables have the same schema
- All columns should transfer except for the auto-increment
id - Without explicitly writing all the column names, as that would be tedious
- 两个表具有相同的架构
- 除自动增量外，所有列都应传输
id - 没有明确写出所有的列名，因为那会很乏味
The trivial INSERT INTO table_target SELECT * FROM table_sourcefails on duplicate entries for primary key.
INSERT INTO table_target SELECT * FROM table_source对于主键的重复条目，微不足道的失败。
采纳答案by Marc B
Either you list all of the fields you want in the insert...select, or you use something else externally to build the list for you.
要么在 insert...select 中列出所需的所有字段，要么在外部使用其他内容为您构建列表。
SQL does not have something like SELECT * except somefield FROM, so you'll have to bite the bullet and write out the field names.
SQL 没有类似的东西SELECT * except somefield FROM，所以你必须硬着头皮写出字段名称。
回答by Devart
Column names have to be specified -
必须指定列名 -
INSERT INTO table_target SELECT NULL, column_name1, column_name2, column_name3, ...
FROM table_source;
Just pass NULL as a value for the auto-increment id field.
只需将 NULL 作为自动递增 id 字段的值传递。
回答by stivlo
Of course, primary key must be unique. It depends on what you want to achieve, but you could exclude rows with a primary key that already exists.
当然，主键必须是唯一的。这取决于您想要实现的目标，但您可以排除主键已存在的行。
INSERT INTO table_target SELECT * FROM table_source
WHERE table_source.id NOT IN (SELECT id FROM table_target)
UPDATE: since you also need the extra rows, you should resolve the conflict first, does table_source have relationships? If not you could change those keys:
更新：既然你还需要额外的行，你应该先解决冲突，table_source 有关系吗？如果没有，您可以更改这些键：
UPDATE table_source SET id = id + 1000
WHERE id IN (SELECT id FROM table_target)
Where 1000, is a constant, big enough so they go after the end of your table.
其中 1000 是一个常数，足够大，因此它们会在您的表末尾之后。
回答by Larry Lustig
Tedious but safe and correct.
乏味但安全和正确。
Writing INSERT statements without providing a list of columns leads to code that's hard to debug and, more importantly, very fragile code that will break if the definition of the table is changed.
在不提供列列表的情况下编写 INSERT 语句会导致代码难以调试，更重要的是，非常脆弱的代码会在表的定义发生更改时中断。
If you absolutely can't write the column names out yourself then it's relatively easy to build a tool into your code that will create the comma-separated list for you.
如果您绝对不能自己写出列名，那么在您的代码中构建一个工具来为您创建逗号分隔的列表相对容易。
回答by Polarfrosch100
This is my final solution to mass update with 'replace insert' command.
这是我使用“替换插入”命令进行大规模更新的最终解决方案。
SET @@session.group_concat_max_len = @@global.max_allowed_packet;
SET @schema_db = 'db';
SET @tabl = 'table';
SET @cols = (SELECT CONCAT('`',GROUP_CONCAT(COLUMN_NAME SEPARATOR '`, `'), '`') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema_db AND TABLE_NAME = @tabl GROUP BY TABLE_NAME);
SET @Querystr = CONCAT('REPLACE INTO ',@schema_db,'.',@tabl,' SELECT ', @cols,' FROM import.tbl_', @tabl);
PREPARE stmt FROM @Querystr;
EXECUTE stmt;
回答by Sudhir Bastakoti
I think you could use syntax like:
我认为您可以使用以下语法：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
REF: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.htmlHope it helps
REF：http: //dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html希望它有所帮助
回答by Moshe L
INSERT IGNOREjust "bypass" the duplicate rows.
INSERT IGNORE只是“绕过”重复的行。
回答by georgepsarakis
You can probably do it with prepared statements.
您可能可以使用准备好的语句来做到这一点。
PREPARE table_target_insert FROM 'INSERT INTO table_target SELECT ? FROM table_source';
SET @cols:='';
SELECT @cols:=GROUP_CONCAT(IF(column_name = 'id','NULL',column_name) separator ",") FROM information_schema.columns WHERE table_name='table_source';
EXECUTE table_target_insert USING @cols;
回答by Polarfrosch100
It seems as if columns can not be given as a place holder in a MySQL Prepared Statement. I have compiled the following solution for testing:
似乎列不能作为 MySQL Prepared Statement 中的占位符给出。我编译了以下解决方案进行测试：
SET @schema_db = 'DB';
SET @table = 'table';
SET @cols = (SELECT CONCAT(GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '), "\n") FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema_db AND TABLE_NAME = @table GROUP BY TABLE_NAME);
SET @Querystr = CONCAT('SELECT',' ', @cols,' ','FROM',' ',@schema_db,'.',@table,' ', 'Limit 5');
PREPARE stmt FROM @Querystr;
EXECUTE stmt;
回答by Ziad Eldada
The easiest way to do it is to use phpmyadmin to write the list of columns, then to change it as needed, in the example below I want to duplicate row with id=1078 and in this table I have id unique auto increment and alias unique.therefore I created my query as follow, with id & alias replaced by a desired value. and it worked like a charm.
最简单的方法是使用 phpmyadmin 编写列列表，然后根据需要进行更改，在下面的示例中，我想复制 id=1078 的行，在此表中，我有 id unique auto increment 和 alias unique .因此我按如下方式创建了我的查询，id & alias 替换为所需的值。它就像一个魅力。
INSERT INTO sy3_menuselect 1079, menutype, title, "alias", note, path, link, type, published, parent_id, level, component_id, checked_out, checked_out_time, browserNav, access, img, template_style_id, params, lft, rgt, home, language, client_idfrom sy3_menuwhere id=1078
INSERT INTOsy3_menu选择1079， ，menutype，title“ ”，alias， ，note，path，link，type，published，parent_id，level，component_id，checked_out，checked_out_time，browserNav，access，img，template_style_id，params，，lft，从 其中id = 1078rgthomelanguageclient_idsy3_menu
Alternatively, to auto increment id, use the following Join statement:
INSERT INTO sy3_menuselect *
from (SELECT MAX(id+1 )from sy3_menu)a
join (select menutype, title, "alias", note, path, link, type, published, parent_id, level, component_id, checked_out, checked_out_time, browserNav, access, img, template_style_id, params, lft, rgt, home, language, client_idfrom sy3_menuwhere id=1079)b
或者，要自动增加 id，请使用以下 Join 语句： INSERT INTO sy3_menuselect * from (SELECT MAX( id+1 )from sy3_menu)a join (select menutype, title, " alias", note, path, link, type, published, parent_id, level, component_id, checked_out, checked_out_time, browserNav, access, img, template_style_id, params, lft, rgt, home, language,client_id从 sy3_menu哪里 id=1079)b
