テーブルのレコードをコピーしたい、だけどちょっとカラムの定義や値を変えたいケースがあります。
例として、以下の要件を満たしつつ、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;