MYSQL：如何将整行从一个表复制到 mysql 中的另一个表，第二个表有一个额外的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1353733/
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: How to copy an entire row from one table to another in mysql with the second table having one extra column?
提问by user165242
I have two tables with identical structure except for one column... Table 2 has that additional column in which i would insert the CURRENT_DATE()
我有两个结构相同的表，除了一列......表 2 有一个额外的列，我将在其中插入 CURRENT_DATE()
I would like to copy all the values from table1 to table2.
我想将所有值从 table1 复制到 table2。
if i use
如果我使用
INSERT INTO dues_storage SELECT * FROM dues WHERE id=5;
it throws an error pointing out to the difference in the number of columns.
它抛出一个错误，指出列数的差异。
I have two questions on that:
我有两个问题：
- How do I get around this?
- and how do I add the value for the additional date column (CURRENT_DATE()) in table2 within this same statement?
- 我该如何解决这个问题？
- 以及如何在同一语句中为 table2 中的附加日期列 (CURRENT_DATE()) 添加值？
回答by crunchdog
To refine the answer from Zed, and to answer your comment:
要完善 Zed 的答案，并回答您的评论：
INSERT INTO dues_storage
SELECT d.*, CURRENT_DATE()
FROM dues d
WHERE id = 5;
See T.J. Crowder's comment
见 TJ Crowder 的评论
回答by paxdiablo
The safest way to do it is to fully specify the columns both for insertion and extraction. There's no guarantee (to the application) that either of these will be the order you think they may be.
最安全的方法是完全指定用于插入和提取的列。不能保证（对应用程序）其中任何一个将是您认为它们可能的顺序。
insert into dues_storage (f1, f2, f3, cd)
select f1, f2, f3, current_date() from dues where id = 5;
If you're worried about having to change many multiple PHP pages that do this (as you seem to indicate in the comment to another answer), this is ripe for a stored procedure. That way, all your PHP pages simply call the stored procedure with (for example) just the ID to copy and it controls the actual copy process. That way, there's only one place where you need to maintain the code, and, in my opinion, the DBMS is the right place to do it.
如果您担心必须更改许多执行此操作的多个 PHP 页面（正如您似乎在对另一个答案的评论中指出的那样），那么这对于存储过程来说已经成熟了。这样，您的所有 PHP 页面只需使用（例如）要复制的 ID 调用存储过程，并控制实际的复制过程。这样，只有一个地方需要维护代码，而且在我看来，DBMS 是正确的地方。
回答by Zed
INSERT INTO dues_storage
SELECT field1, field2, ..., fieldN, CURRENT_DATE()
FROM dues
WHERE id = 5;
回答by Josh Strike
Hope this will help someone... Here's a little PHP script I wrote in case you need to copy some columns but not others, and/or the columns are not in the same order on both tables. As long as the columns are named the same, this will work. So if table A has [userid, handle, something] and tableB has [userID, handle, timestamp], then you'd "SELECT userID, handle, NOW() as timestamp FROM tableA", then get the result of that, and pass the result as the first parameter to this function ($z). $toTable is a string name for the table you're copying to, and $link_identifier is the db you're copying to. This is relatively fast for small sets of data. Not suggested that you try to move more than a few thousand rows at a time this way in a production setting. I use this primarily to back up data collected during a session when a user logs out, and then immediately clear the data from the live db to keep it slim.
希望这会对某人有所帮助...这是我编写的一个小 PHP 脚本，以防您需要复制某些列而不是其他列，和/或两个表上的列顺序不同。只要列的名称相同，这将起作用。因此，如果表 A 具有 [userid, handle, something] 并且 tableB 具有 [userID, handle, timestamp]，那么您将“SELECT userID, handle, NOW() as timestamp FROM tableA”，然后得到结果，并且将结果作为第一个参数传递给此函数 ($z)。$toTable 是您要复制到的表的字符串名称，而 $link_identifier 是您要复制到的数据库。这对于小数据集来说相对较快。不建议您尝试在生产环境中以这种方式一次移动超过几千行。
function mysql_multirow_copy($z,$toTable,$link_identifier) {
$fields = "";
for ($i=0;$i<mysql_num_fields($z);$i++) {
if ($i>0) {
$fields .= ",";
}
$fields .= mysql_field_name($z,$i);
}
$q = "INSERT INTO $toTable ($fields) VALUES";
$c = 0;
mysql_data_seek($z,0); //critical reset in case $z has been parsed beforehand. !
while ($a = mysql_fetch_assoc($z)) {
foreach ($a as $key=>$as) {
$a[$key] = addslashes($as);
next ($a);
}
if ($c>0) {
$q .= ",";
}
$q .= "('".implode(array_values($a),"','")."')";
$c++;
}
$q .= ";";
$z = mysql_query($q,$link_identifier);
return ($q);
}
回答by Prasheel
Alternatively, you can use Inner Queries to do so.
或者，您可以使用内部查询来执行此操作。
SQL> INSERT INTO <NEW_TABLE> SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM <OLD_TABLE>);
Hope this helps!
希望这可以帮助！
回答by Joomsavvy
SET @sql =
CONCAT( 'INSERT INTO <table_name> (',
(
SELECT GROUP_CONCAT( CONCAT('`',COLUMN_NAME,'`') )
FROM information_schema.columns
WHERE table_schema = <database_name>
AND table_name = <table_name>
AND column_name NOT IN ('id')
), ') SELECT ',
(
SELECT GROUP_CONCAT(CONCAT('`',COLUMN_NAME,'`'))
FROM information_schema.columns
WHERE table_schema = <database_name>
AND table_name = <table_source_name>
AND column_name NOT IN ('id')
),' from <table_source_name> WHERE <testcolumn> = <testvalue>' );
PREPARE stmt1 FROM @sql;
execute stmt1;
Of course replace <> values with real values, and watch your quotes.
当然用实际值替换 <> 值，并注意您的报价。
回答by Kevin F
Just wanted to add this little snippet which works beautifully for me.
只是想添加这个对我来说非常有用的小片段。
INSERT INTO your_target_table SELECT * FROM your_rescource_table WHERE id = 18;
INSERT INTO your_target_table SELECT * FROM your_resource_table WHERE id = 18;
And while I'm at it give a big shout out to Sequel Pro, if you're not using it I highly recommend downloading it...makes life so much easier
当我在它的时候向 Sequel Pro 大喊大叫，如果你不使用它，我强烈建议你下载它......让生活变得更轻松
