MySQL 一時テーブル完全ガイド｜作成・管理・活用法を徹底解説

1. はじめに

一時テーブルとは？

• セッション単位で存在する（別の接続からはアクセス不可）
• テーブル作成後のセッションが終了すると、自動削除される
• 同名の通常テーブルが存在しても、干渉せずに使用できる
• パフォーマンス改善のために使用されることが多い

一時テーブルを使用するメリット

1. クエリのパフォーマンスを向上させる

2. データの一時的な保存に最適

3. 既存のデータを安全に保つ

まとめ

• セッション単位で有効 であり、セッション終了時に削除される
• パフォーマンス改善やバッチ処理に有効
• 本番データを変更せずに安全にデータを操作できる

2. 一時テーブルの基本

一時テーブルと通常のテーブルの違い

ポイント

• 一時テーブルは セッションごとに独立 しており、他のユーザーからは見えない。
• 同じ名前の通常テーブルがあっても、エラーにならずに作成できる。
• CREATE TEMPORARY TABLE を使って明示的に作成し、セッションが終了すると自動的に削除 される。

一時テーブルと内部一時テーブルの違い

内部一時テーブルとは？

• MySQLは、一部のクエリ（GROUP BY、ORDER BY、DISTINCT など）を最適化するために、内部的に一時テーブルを作成 する。
• エンドユーザーが直接管理することはできない（CREATE TEMPORARY TABLE のように明示的に作成はできない）。
• クエリの実行時に必要に応じて作成され、クエリが完了すると自動的に削除 される。

内部一時テーブルの発生例

SELECT category, COUNT(*) 
FROM products 
GROUP BY category
ORDER BY COUNT(*) DESC;

まとめ

• 一時テーブルは、ユーザーが明示的に作成する一時的なテーブル であり、セッションが終了すると自動的に削除される。
• 通常のテーブルとは異なり、他のセッションからアクセスできない。
• 内部一時テーブルは、MySQLが自動的に作成・削除する一時的なテーブル で、ユーザーが直接操作することはできない。

3. 一時テーブルの作成方法

一時テーブルの基本的な作成方法

基本構文

CREATE TEMPORARY TABLE テーブル名 (
    カラム名 データ型 制約,
    カラム名 データ型 制約,
    ...
);

サンプルコード

CREATE TEMPORARY TABLE users_temp (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

既存テーブルを基にした一時テーブルの作成

CREATE TEMPORARY TABLE ... SELECT を使用する

基本構文

CREATE TEMPORARY TABLE 一時テーブル名
SELECT * FROM 既存テーブル名;

サンプルコード

CREATE TEMPORARY TABLE users_temp
SELECT * FROM users;

CREATE TEMPORARY TABLE users_temp
SELECT * FROM users WHERE 1=0;

一時テーブルを作成する際の注意点

1. 一時テーブルはセッション単位で有効

• 一時テーブルは、作成したセッション内でのみ有効です。
• 他の接続や別のユーザーからはアクセスできません。

2. 同じ名前の通常テーブルが存在していても問題なく作成可能

• 例えば、データベースに users という通常のテーブルがあっても、同じ名前の users という一時テーブルを作成できます。
• この場合、セッション内では一時テーブルが優先され、通常テーブルは見えなくなります。

3. ストレージエンジンの影響

• 一時テーブルは、デフォルトで MEMORY エンジンが使用されますが、データ量が大きい場合は InnoDB の一時領域に保存されることがあります。
• MEMORY エンジンを明示的に指定する場合は、以下のように記述します。 CREATE TEMPORARY TABLE users_temp ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ) ENGINE=MEMORY;
• MEMORY エンジンは、高速ですがデータサイズの制限があるため、大量のデータを扱う場合は InnoDB の使用を検討しましょう。

まとめ

• 一時テーブルは CREATE TEMPORARY TABLE を使って作成する。
• 既存のテーブルをコピーして一時テーブルを作成することも可能（SELECT * FROM を使用）。
• MEMORY エンジンを使用すると高速処理が可能だが、大量データには InnoDB が適している。
• 一時テーブルはセッション単位で管理され、セッション終了時に自動的に削除される。

4. 一時テーブルの使用方法

データの挿入

基本構文

INSERT INTO 一時テーブル名 (カラム名1, カラム名2, ...)
VALUES (値1, 値2, ...);

サンプルコード

INSERT INTO users_temp (id, name, email)
VALUES (1, '山田 太郎', 'taro@example.com');

INSERT INTO users_temp (id, name, email)
SELECT id, name, email FROM users WHERE age >= 18;

データの更新

基本構文

UPDATE 一時テーブル名
SET カラム名 = 新しい値
WHERE 条件;

サンプルコード

UPDATE users_temp
SET name = '佐藤 一郎'
WHERE id = 1;

データの削除

基本構文

DELETE FROM 一時テーブル名 WHERE 条件;

サンプルコード

DELETE FROM users_temp WHERE id = 1;

DELETE FROM users_temp;

データの参照

基本構文

SELECT カラム名 FROM 一時テーブル名 WHERE 条件;

サンプルコード

SELECT * FROM users_temp;

SELECT * FROM users_temp WHERE email LIKE '%@example.com';

一時テーブルの使用時の注意点

1. セッションが終了するとデータも削除される

• 一時テーブルは セッション単位 で管理されており、セッションが終了するとデータも削除される。
• 長時間の処理を行う場合は、定期的にデータをバックアップすることが推奨される。

2. 同じ名前の一時テーブルが既に存在するとエラーが発生する

• CREATE TEMPORARY TABLE で 同じ名前の一時テーブルを作成しようとするとエラー になる。
• エラー回避策 として、DROP TEMPORARY TABLE IF EXISTS を事前に実行するとよい。 DROP TEMPORARY TABLE IF EXISTS users_temp; CREATE TEMPORARY TABLE users_temp (...);

3. ストレージエンジンによる制約がある

• 一時テーブルは MEMORY エンジンがデフォルト だが、データ量が多いと自動的に InnoDB の一時領域に保存される。
• 大量データを扱う場合は、InnoDB の一時テーブルを利用するのが推奨される。

まとめ

• 一時テーブルは通常のテーブルと同じように データの挿入・更新・削除・参照 を行うことができる。
• セッションが終了すると、一時テーブルのデータも自動的に削除される。
• DROP TEMPORARY TABLE IF EXISTS を事前に実行することで、同名のテーブル作成エラーを回避できる。
• 大量データを扱う場合は InnoDB の一時テーブルを利用するのが推奨される。

5. 一時テーブルの管理と削除

一時テーブルの削除方法

基本構文

DROP TEMPORARY TABLE テーブル名;

サンプルコード

DROP TEMPORARY TABLE users_temp;

セッション終了時の自動削除

自動削除の仕組み

1. CREATE TEMPORARY TABLE で一時テーブルを作成
2. セッションが維持されている間はデータを操作可能
3. セッション（接続）が切断されると、一時テーブルは自動的に削除

• セッションが長時間維持される場合 → 不要な一時テーブルがメモリを占有する可能性があるため、適宜 DROP TEMPORARY TABLE を実行するのが推奨される。
• 大量のデータを扱う場合 → ストレージの圧迫を避けるため、適切にテーブルを削除することが重要。

DROP TEMPORARY TABLE IF EXISTS の活用

基本構文

DROP TEMPORARY TABLE IF EXISTS テーブル名;

サンプルコード

DROP TEMPORARY TABLE IF EXISTS users_temp;

よくあるエラーと対処法

エラー1：「Table not found」

• DROP TEMPORARY TABLE で、存在しないテーブルを削除しようとした場合
• 一時テーブルはセッション単位で管理されるため、別のセッションからは削除できない

• IF EXISTS を追加することでエラーを防ぐ

  DROP TEMPORARY TABLE IF EXISTS users_temp;

• 正しいセッション内で削除を実行する

エラー2：「Table already exists」

• 同じ名前の一時テーブルを作成しようとしたが、既に存在している

• 事前に DROP TEMPORARY TABLE IF EXISTS を実行する

  DROP TEMPORARY TABLE IF EXISTS users_temp;
  CREATE TEMPORARY TABLE users_temp (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      email VARCHAR(100)
  );

一時テーブル管理のベストプラクティス

1. 不要になったら明示的に削除する

• DROP TEMPORARY TABLE を適宜実行し、不要なテーブルを解放する。

1. エラー回避のために IF EXISTS を活用する

• DROP TEMPORARY TABLE IF EXISTS を使うことで、存在しないテーブルの削除時にエラーを防ぐ。

1. セッションの管理を意識する

• セッションが長時間維持される場合、一時テーブルがメモリを圧迫する可能性があるため、適宜削除する。

1. ストレージエンジンの影響を理解する

• MEMORY エンジンを使用すると高速だが、データサイズに制限がある。
• InnoDB を利用する場合は、ディスクスペースを考慮する必要がある。

まとめ

• 一時テーブルは DROP TEMPORARY TABLE で明示的に削除できる。
• セッション終了時には、自動的に削除されるが、長時間のセッションでは手動削除が推奨される。
• DROP TEMPORARY TABLE IF EXISTS を使うことで、削除時のエラーを防げる。
• 「Table not found」エラーや「Table already exists」エラーに対処する方法を知っておくと便利。

6. 一時テーブルの活用例

1. データ集計時の中間テーブルとしての利用

シナリオ

• sales テーブルには1年間の販売データが格納されている。
• 月ごとの売上合計を計算し、さらに詳細な分析を行いたい。

実装例

CREATE TEMPORARY TABLE monthly_sales (
    month_year DATE,
    total_sales DECIMAL(10,2)
);

INSERT INTO monthly_sales (month_year, total_sales)
SELECT DATE_FORMAT(sale_date, '%Y-%m-01') AS month_year, SUM(amount) 
FROM sales
GROUP BY month_year;

SELECT * FROM monthly_sales;

2. バッチ処理における一時データの保持

シナリオ

• users テーブルの中から、過去1年間にログインしたユーザー のみを対象にメールを送信する。
• 事前に対象データを一時テーブルに保存し、順次処理を行う。

実装例

CREATE TEMPORARY TABLE active_users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

INSERT INTO active_users
SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL 1 YEAR;

SELECT * FROM active_users;

3. 複雑なクエリの簡略化

シナリオ

• orders テーブルの中から、売上トップ10の商品 を取得する。
• サブクエリを使わずに一時テーブルを活用する。

実装例

CREATE TEMPORARY TABLE top_products AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

SELECT * FROM top_products;

4. ロールバック不要な一時的なテーブル操作

シナリオ

• トランザクション処理を行う際に、一時的な計算結果を保持したい。
• しかし、エラー時に一時データがロールバックされるのは避けたい。

実装例

START TRANSACTION;

CREATE TEMPORARY TABLE temp_results (
    user_id INT,
    score INT
);

INSERT INTO temp_results
SELECT user_id, SUM(points) FROM game_scores GROUP BY user_id;

-- トランザクションをコミットする
COMMIT;

SELECT * FROM temp_results;

まとめ

• 一時テーブルは、データ集計・バッチ処理・クエリの簡略化 など、さまざまな用途で活用できる。
• 中間テーブルとしての利用 では、パフォーマンス向上やデータ整理に役立つ。
• バッチ処理 では、事前に対象データを抽出し、不要なデータの処理を省略できる。
• 複雑なクエリの簡略化 では、サブクエリを減らし、SQLの可読性を向上できる。
• トランザクションの影響を受けない ため、ロールバック不要なデータ処理にも適している。

7. 一時テーブルの代替手段と制約

一時テーブルの主な制約

1. セッション単位で管理される

• 一時テーブルは作成されたセッション内でのみ有効であり、他の接続やユーザーからはアクセスできない。
• 同じ名前の通常テーブルが存在しても、同じセッション内では一時テーブルが優先される（通常テーブルにはアクセスできない）。

2. 一時テーブルのスキーマは保存されない

• 通常のテーブルは SHOW CREATE TABLE でスキーマを取得できるが、一時テーブルは セッション終了とともに消滅 するため、スキーマ情報が保持されない。

3. インデックスの制限

• CREATE TEMPORARY TABLE で PRIMARY KEY や INDEX を指定しないと、自動的には作成されない。
• 一時テーブルにインデックスを付与する場合は、手動で作成する必要がある。

4. デフォルトのストレージエンジンは MEMORY

• MEMORY エンジンでは データ量が多くなると、ディスクへのスワップが発生し、パフォーマンスが低下する。
• InnoDB を指定すれば大容量データに対応できるが、その分ディスクの使用量が増加する。

5. トランザクションの影響を受けない

• 一時テーブルは ROLLBACK しても影響を受けない。
• そのため、トランザクションの一貫性を保つ必要がある処理には適していない。

一時テーブルの代替手段

1. ビュー（View）を利用する

ビューの作成

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL 1 YEAR;

ビューの利用

SELECT * FROM active_users;

ビューを使うメリット

ビューのデメリット

2. サブクエリを利用する

一時テーブルを使う場合

CREATE TEMPORARY TABLE top_products AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

SELECT * FROM top_products;

サブクエリを使う場合

SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

サブクエリを使うメリット

サブクエリのデメリット

3. 共通テーブル式（WITH句）を利用する

CTEを使った例

WITH top_products AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
    ORDER BY total_sales DESC
    LIMIT 10
)
SELECT * FROM top_products;

CTEを使うメリット

CTEのデメリット

まとめ

8. FAQ（よくある質問）

1. 一時テーブルは他のセッションから参照できますか？

2. 一時テーブルの作成に必要な権限は？

GRANT CREATE TEMPORARY TABLES ON データベース名.* TO 'ユーザー名'@'ホスト';

SHOW GRANTS FOR 'ユーザー名'@'ホスト';

3. 一時テーブルはディスク容量に影響しますか？

DROP TEMPORARY TABLE IF EXISTS テーブル名;

CREATE TEMPORARY TABLE テーブル名 (
    カラム1 データ型,
    カラム2 データ型
) ENGINE=InnoDB;

4. 一時テーブルと内部一時テーブルの違いは？

5. 一時テーブルはスレッド間で共有できますか？

CREATE TABLE shared_temp_table (
    id INT PRIMARY KEY,
    data VARCHAR(255)
);

6. 一時テーブルを使うとパフォーマンスが落ちることはありますか？

• データ量が多すぎる場合
• MEMORY エンジンはデータサイズの制限があり、一定以上になると InnoDB にスワップされ、パフォーマンスが低下 します。
• 対策: MEMORY エンジンの制限を超える場合は、最初から InnoDB で作成する。
• 適切なインデックスが設定されていない場合
• CREATE TEMPORARY TABLE ... SELECT で作成したテーブルには インデックスがコピーされない ため、検索が遅くなることがあります。
• 対策: 必要に応じて ALTER TABLE でインデックスを追加する。

  ALTER TABLE 一時テーブル名 ADD INDEX (カラム名);

7. 一時テーブルのパフォーマンスを改善する方法は？

CREATE TEMPORARY TABLE テーブル名 (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;

CREATE TEMPORARY TABLE users_temp AS
SELECT id, name FROM users;

ALTER TABLE users_temp ADD INDEX (name);

DROP TEMPORARY TABLE IF EXISTS users_temp;

まとめ

• 一時テーブルは他のセッションやスレッドからは参照できない
• 作成には CREATE TEMPORARY TABLES 権限が必要
• データが多すぎると MEMORY から InnoDB にスワップされ、パフォーマンスが低下する
• 適切なインデックスを設定することでクエリの高速化が可能
• 不要な一時テーブルは DROP TEMPORARY TABLE で削除するのが推奨