勉強したことのメモ

Webエンジニア / プログラマが勉強したことのメモ。

MySQLでwhere句を使ってカラム指定でデータをコピーする方法

   2024/02/22  MySQL

MySQLで大量のデータを保存しているテーブルがあり、そのデータを絞った上で尚且つカラムを指定して他のテーブルにコピーしたかった。ダンプしてインサートしようかと思って少し試したところデータ量が多いからか時間がかかりすぎた。何か方法は無いか探したところ簡単にコピーできる方法があった。

 

SQL文

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
INSERT INTO
コピー先のテーブル名
(カラム, カラム)
SELECT
(カラム, カラム)
FROM
コピー元のテーブル名
WHERE
WHERE区を適当に
INSERT INTO コピー先のテーブル名 (カラム, カラム) SELECT (カラム, カラム) FROM コピー元のテーブル名 WHERE WHERE区を適当に
INSERT INTO 
    コピー先のテーブル名 
    (カラム, カラム) 
SELECT 
    (カラム, カラム) 
FROM 
    コピー元のテーブル名 
WHERE 
    WHERE区を適当に

例えば以下条件の場合は、

  • テーブルAからテーブルBにコピー
  • カラムはidとnameを入れたい
  • テーブルBには新カラムのコピー日時をregist_dateカラムとして入れたい
  • WHERE句はテーブルAのdateカラムで2016-01-01から2016-01-31までを指定

以下のような形になる。

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
INSERT INTO
table_b
(id, name, regist_date)
SELECT
(id, name, NOW())
FROM
table_a
WHERE
date BETWEEN '2016-01-01' AND '2016-01-31'
INSERT INTO table_b (id, name, regist_date) SELECT (id, name, NOW()) FROM table_a WHERE date BETWEEN '2016-01-01' AND '2016-01-31'
INSERT INTO 
    table_b 
    (id, name, regist_date)
SELECT 
    (id, name, NOW()) 
FROM 
    table_a 
WHERE 
    date BETWEEN '2016-01-01' AND '2016-01-31'

WHERE句はさらにANDとかで絞込み検索が可能。

 

その他

絞込み検索をダンプしてインポートする方法だと100万件ほどのデータで1時間ほどかかったが、この方法だと5分もかからずに処理できた。

 - MySQL

  関連記事

MySQLでスレッドの実行状況を表示しスレッドの経過時間チェックや特定スレッドを強制終了する方法

データベースサーバが重たく、原因を調査する機会があった。サーバ自体のメモリやCP ...

MySQLで同一サーバーの異なるDB内のテーブルをJOINする

同一のMySQLサーバーでdb1データベースのtable1テーブルと、db2デー ...

MySQLのTEMPORARY TABLE(テンポラリテーブル)で高速化を図る方法

MySQLで5000万件ぐらいデータが入っているテーブルから日本語検索しないとい ...

MySQLのREGEXPで半角記号を検索したい場合の対応方法

MySQLのREGEXPで半角記号を検索したい場合\\を付けてエスケープしないと ...

MySQLにてSELECT時にdate型のカラムから和暦を計算し表示する方法

MySQLにdate型のカラムに誕生年月日が格納されており、そこから和暦を計算し ...

S