【MySQL】カラム数の多いテーブルのレコードをスマートにコピーしたい

例えばこのようなテーブル

fruitsテーブル

id	memo1	memo2	memo3	memo4	memo5	memo6	memo7	memo8
1	りんご1	りんご2	りんご3	りんご4	りんご5	りんご6	りんご7	りんご8
2	メロン1	メロン2	メロン3	メロン4	メロン5	メロン6	メロン7	メロン8
3	いちご1	いちご2	いちご3	いちご4	いちご5	いちご6	いちご7	いちご8

このidが２のデータをコピーして、

id	memo1	memo2	memo3	memo4	memo5	memo6	memo7	memo8
1	りんご1	りんご2	りんご3	りんご4	りんご5	りんご6	りんご7	りんご8
2	メロン1	メロン2	メロン3	メロン4	メロン5	メロン6	メロン7	メロン8
3	いちご1	いちご2	いちご3	いちご4	いちご5	いちご6	いちご7	いちご8
4	メロン1	メロン2	メロン3	メロン4	メロン5	メロン6	メロン7	メロン8

このように追加したいときどうしますか？

idカラム には自動採番される、オートインクリメント(AUTO_INCREMENT)制約が付いています。

一般的な例

方法は多々あるかと思いますが、

例１

INSERT INTO `fruits`
    (`memo1`,`memo2`,`memo3`,`memo4`,`memo5`,`memo6`,`memo7`,`memo8`)
SELECT
    `memo1`,`memo2`,`memo3`,`memo4`,`memo5`,`memo6`,`memo7`,`memo8`
FROM
    `fruits`
WHERE
    `id` = 2;

例２

INSERT INTO `fruits`
SELECT
    0, `memo1`,`memo2`,`memo3`,`memo4`,`memo5`,`memo6`,`memo7`,`memo8`
FROM
    `fruits`
WHERE
    `id` = 2;

これらはいずれも、fruitsテーブル自身をSELECTした結果をINSERTしています。

しかし…カラムが多いですよね。
これがもっとカラムが多かったら大変です（実務ではより多くのカラムがありました）。

正直これはめんどうです。
カラムの記載漏れがあると、列の数が値の数と一致せずエラーになってしまいます。
それに、idカラムだけは、現状の採番順序に沿った値を入れたいので、除いて書いています。

工夫した例

そこでこれです

CREATE TEMPORARY TABLE `temp` AS SELECT * FROM `fruits` WHERE `id`= 2;
ALTER TABLE `temp` DROP `id`;
INSERT INTO `fruits` SELECT 0, `temp`.* FROM `temp`;
DROP TEMPORARY TABLE `temp`;

コピーしたいレコードを一時テーブルに一旦保存し、それをfruitsテーブルに挿入するという形をとることで、いちいちカラムを列挙することもない対応を可能にしています。

解説

-- 1行目
CREATE TEMPORARY TABLE `temp` AS SELECT * FROM `fruits` WHERE `id`= 2;

fruitsテーブルのid=2のレコードを入れた一時テーブルを作成します。

一時テーブルは、CREATE TEMPORARY TABLE構文を使って作成します。TEMPORARY句を付けること以外は通常のテーブルと同じ作り方になります。

一時テーブルは利用しているセッション内だけで有効なテーブルを作成し、セッションが閉じたときに自動的にテーブルが削除される構文になります。

そのため4行目にDROP TEMPORARY TABLE構文で削除を入れていますが、入れなくても基本は自動的に削除されます。

-- 2,3行目
ALTER TABLE `temp` DROP `id`;
INSERT INTO `fruits` SELECT 0, `temp`.* FROM `temp`;

一時テーブルのidカラムを削除し、その情報をfruitsテーブルに挿入しています。

idはオートインクリメントになっていますので、insertのselectに0を設定することで、fruitsテーブルのidに入る番号は、自動的に連番の値が入るようにしています。

-- 4行目
DROP TEMPORARY TABLE `temp`;

入れていなくても基本は自動的に一時テーブルは削除されますが、念のため入れております。

参考文献