はじめに

Mysqlでストアドプロシージャを作成している際に、以下のようなエラーを見たことはありますでしょうか。
少々パワープレイにはなりますが、今回紹介する方法で解決は出来たので、その回避策を3つ紹介します。
それぞれメリット/デメリットを記載しているので、参考にしてください。

ERROR 1137: Can't reopen table: 'table_name'

エラーが起こる理由

Mysqlの公式で以下のように記載されています。

同じクエリで TEMPORARY テーブルを複数回参照することはできません。

ストアドプロシージャでは、全体が1つのクエリとして扱われる為、1つのストアドプロシージャ中では、一時テーブルは1度しか参照出来ないことになります。
FETCH CURSORなどループ中で繰り返し参照する場合については、1回とカウントされるようです。

一時テーブルとは何か

CREATE TEMPORARYで作成されるテーブルのことを一時テーブルと呼びます。
この一時テーブルは、コネクションのセッション毎に保持され、基本的にクエリが終了すれば自動的に削除されます。
また、セッション毎に保持している為、たとえ異なるユーザー間で同時に同名の一時テーブルを作成したとして、別物として扱われます。

回避策

回避策1. 一時テーブルを永続テーブルで置き換える

同一クエリ内で複数回参照出来ないのは一時テーブル固有の仕様の為、いっそのこと永続テーブルに変えてしまうというものです。
これによってエラー自体は発生しなくなりますが、以下のようなメリット/デメリットがあります。

メリット

今回紹介する中では一番簡潔に書ける

デメリット

ゴミデータが残らない様にDELETEやトランザクション制御をする必要がある
複数ユーザーの同時使用を想定する場合、操作するレコードが被らない様にレコードにユーザーを特定するカラムを増やすなどする必要がある

CREATE TEMPORARY TABLE `table_name` ();

-- ↓

CREATE TABLE `table_name` ();

回避策2. 一時テーブルをサブクエリに置き換える

こちらは、一時テーブルを作成することなく、参照の度にサブクエリを書いてしまおうというものです。
この方法でもエラー自体は発生しなくなりますが、以下のようなメリット/デメリットがあります。

メリット

一時テーブルを使わなくて済む

デメリット

呼び出し回数が多ければ多いほどパフォーマンスが悪化する

CREATE TEMPORARY TABLE `tmp_table` ();

INSERT INTO `tmp_table` VALUES(....);

SELECT * FROM `tmp_table`;

-- ↓

-- 一時テーブルを経由せずに、サブクエリで直接取得
SELECT * FROM (
  SELECT * FROM `sub_query`
) AS tmp_table;

回避策3. 一時テーブルの複製

こちらは使用する箇所の分、一時テーブルを複製するというものです。

メリット

今回紹介する中では、一番パフォーマンスが良い

デメリット

使用回数が多ければ多いほど、複製個数も増え、SQLが汚くなる

-- テーブル定義を複製 ※この時点ではレコードは複製されていません
CREATE TEMPORARY TABLE `table_name` LIKE `複製元の一時テーブル`;

-- レコードの複製
INSERT INTO `table_name` SELECT * FROM `複製元の一時テーブル`;

-- 使用する回数分、↑の処理を実施する

参考