INSERT INTO...SELECT 用于所有 MySQL 列 [英] INSERT INTO...SELECT for all MySQL columns

问题描述

我正在尝试从以下位置移动旧数据:

I'm trying to move old data from:

this_table >> this_table_archive

复制所有列.我试过这个，但它不起作用:

copying all columns over. I've tried this, but it doesn't work:

INSERT INTO this_table_archive (*) VALUES (SELECT * FROM this_table WHERE entry_date < '2011-01-01 00:00:00');

注意:表是相同的，并且将 id 设置为主键.

Note: the tables are identical and have id set as a primary key.

推荐答案

手册.试试这个:

INSERT INTO this_table_archive (col1, col2, ..., coln)
SELECT col1, col2, ..., coln
FROM this_table
WHERE entry_date < '2011-01-01 00:00:00';

如果 id 列是一个自增列并且您已经在两个表中都有一些数据，那么在某些情况下，您可能希望从列列表中省略 id 并生成新的 id 来避免插入已经存在的 id在原始表中.如果您的目标表是空的，那么这将不是问题.

If the id columns is an auto-increment column and you already have some data in both tables then in some cases you may want to omit the id from the column list and generate new ids instead to avoid insert an id that already exists in the original table. If your target table is empty then this won't be an issue.

这篇关于INSERT INTO...SELECT 用于所有 MySQL 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！