はじめに
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 `複製元の一時テーブル`;
-- 使用する回数分、↑の処理を実施する
参考