MySQL: カラムをちょっと変えてテーブルをコピーしたい

テーブルのレコードをコピーしたい、だけどちょっとカラムの定義や値を変えたいケースがあります。

例として、以下の要件を満たしつつ、postsからcopied_postsへコピーしたいとします。

  • コピーした日時 (copied_at) を追加したい
  • emailをNULLにしたい
mysql> create table copied_posts (
    ->     id int unsigned not null,
    ->     title varchar(256) not null,
    ->     body text,
    ->     email varchar(256),
    ->     created_at datetime not null,
    ->     copied_at datetime not null,
    ->     primary key (id)
    -> );

mysql> insert into posts (title, body, email, created_at) values ('title1', 'body1', 'hoge@fuga.com', now());
mysql> insert into posts (title, body, email, created_at) values ('title2', 'body2', 'hoge@fuga.com', now());
mysql> insert into posts (title, body, email, created_at) values ('title3', 'body3', 'hoge@fuga.com', now());

mysql> select * from posts;
+----+--------+-------+---------------+---------------------+
| id | title  | body  | email         | created_at          |
+----+--------+-------+---------------+---------------------+
|  1 | title1 | body1 | hoge@fuga.com | 2020-09-05 11:36:07 |
|  2 | title2 | body2 | hoge@fuga.com | 2020-09-05 11:36:33 |
|  3 | title3 | body3 | hoge@fuga.com | 2020-09-05 11:36:37 |
+----+--------+-------+---------------+---------------------+

mysql> create table copied_posts (
    ->     id int unsigned not null,
    ->     title varchar(256) not null,
    ->     body text,
    ->     email varchar(256),
    ->     created_at datetime not null,
    ->     copied_at datetime not null,
    ->     primary key (id)
    -> );

postsとcopied_postsのカラムの定義が異なるので、直接insertしようとすると当然エラーになります。

mysql> insert into copied_posts select * from posts;
ERROR 1136 (21S01): Column count doesn't match value count at row 1

コピーするだけなのにアプリケーションを組むのもハイコストですので、できればSQLで完結させたいところです。

最初にtemporary table (ここではtmp_posts) を作って、そのテーブルに対してalterやupdateを実行し、最後に目的テーブルにinsertすると楽にできます。

  • temporary tableはセッションが終了したら自動的にdropされます (参考)
mysql> create temporary table tmp_posts select * from posts;
mysql> alter table tmp_posts add column copied_at datetime;
mysql> update tmp_posts set copied_at = now();
mysql> update tmp_posts set email = NULL;
mysql> insert into copied_posts select * from tmp_posts;