セッション単位で利用できる一時テーブルの作成方法。
セッション単位のためセッションが切れるとテーブル情報が削除されます。
リレーションさせたテーブルを一時テーブル化
CREATE TEMPORARY TABLE 一時テーブル名 as SELECT 使用するフィールド1, 使用するフィールド2 FROM テーブル名1 JOIN テーブル名2 ON 照合フィールド1=照合フィールド2;
一時テーブルを削除
DROP TEMPORARY TABLE 一時テーブル名;
一時テーブルを参照
SELECT * FROM 一時テーブル名;
テスト用テーブル作成
商品テーブル
test1テーブル作成
CREATE TABLE test1 ( id INT AUTO_INCREMENT PRIMARY KEY, item CHAR(10), price INT, maker INT );
test1テーブルにレコード挿入
INSERT INTO test1 (item, price, maker) VALUES ('商品1', 100, 3), ('商品2', 200, 2), ('商品3', 300, 1);
test1テーブル表示
MariaDB [lesson]> SELECT * FROM test1; +----+--------+-------+-------+ | id | item | price | maker | +----+--------+-------+-------+ | 1 | 商品1 | 100 | 3 | | 2 | 商品2 | 200 | 2 | | 3 | 商品3 | 300 | 1 | +----+--------+-------+-------+ 3 rows in set (0.001 sec)
メーカーテーブル
test2テーブル作成
CREATE TABLE test2 ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(10) );
test2テーブルにレコード挿入
INSERT INTO test2 (name) VALUES ('メーカー1'), ('メーカー2'), ('メーカー3');
test2テーブル表示
MariaDB [lesson]> SELECT * FROM test2; +----+------------+ | id | name | +----+------------+ | 1 | メーカー1 | | 2 | メーカー2 | | 3 | メーカー3 | +----+------------+ 3 rows in set (0.000 sec)
商品テーブルとメーカーテーブルをリレーション
リレーションさせたテーブルを表示
MariaDB [lesson]> SELECT * FROM test1 JOIN test2 ON test1.maker=test2.id; +----+--------+-------+-------+----+------------+ | id | item | price | maker | id | name | +----+--------+-------+-------+----+------------+ | 3 | 商品3 | 300 | 1 | 1 | メーカー1 | | 2 | 商品2 | 200 | 2 | 2 | メーカー2 | | 1 | 商品1 | 100 | 3 | 3 | メーカー3 | +----+--------+-------+-------+----+------------+ 3 rows in set (0.001 sec)
リレーションさせたテーブル一時テーブル化
CREATE TEMPORARY TABLE tmp1 as SELECT * FROM test1 JOIN test2 ON test1.maker=test2.id;
実行結果
下記命令で一時テーブル化しようとしてもフィールド名「id」がtest1テーブルにもtest2テーブルにも存在するため「Duplicate column name 'id'」エラーが表示されます。
MariaDB [lesson]> CREATE TEMPORARY TABLE tmp1 as -> SELECT * FROM test1 JOIN test2 ON test1.maker=test2.id; ERROR 1060 (42S21): Duplicate column name 'id'
同名フィールドをasで別名にして一時テーブル化
SELECT文の表示フィールド指定を「*」から「個別フィールド名」に変更し、同じ名前のフィールドをasで変更しています。
変更したフィールド名
「test1.id」「test2.id as m_id」
CREATE TEMPORARY TABLE tmp1 as SELECT test1.id, item, price, maker, test2.id as m_id, name FROM test1 JOIN test2 ON test1.maker=test2.id;
実行結果
MariaDB [lesson]> CREATE TEMPORARY TABLE tmp1 as -> SELECT test1.id, item, price, maker, test2.id as m_id, name -> FROM test1 JOIN test2 -> ON test1.maker=test2.id; Query OK, 3 rows affected (0.054 sec) Records: 3 Duplicates: 0 Warnings: 0
一時テーブルを表示
一時テーブル名でいつでも呼び出すことができます。
SELECT * FROM tmp1;
実行結果
MariaDB [lesson]> SELECT * FROM tmp1; +----+--------+-------+-------+------+------------+ | id | item | price | maker | m_id | name | +----+--------+-------+-------+------+------------+ | 3 | 商品3 | 300 | 1 | 1 | メーカー1 | | 2 | 商品2 | 200 | 2 | 2 | メーカー2 | | 1 | 商品1 | 100 | 3 | 3 | メーカー3 | +----+--------+-------+-------+------+------------+ 3 rows in set (0.000 sec)
不要なフィールドを指定せずに一時テーブル化
CREATE TEMPORARY TABLE tmp2 as SELECT test1.id, item, price, name FROM test1 JOIN test2 ON test1.maker=test2.id;
実行結果
MariaDB [lesson]> CREATE TEMPORARY TABLE tmp2 as -> SELECT test1.id, item, price, name -> FROM test1 JOIN test2 -> ON test1.maker=test2.id; Query OK, 3 rows affected (0.035 sec) Records: 3 Duplicates: 0 Warnings: 0
一時テーブルを表示
一時テーブル名でいつでも呼び出すことができます。
SELECT * FROM tmp2;
実行結果
MariaDB [lesson]> SELECT * FROM tmp2; +----+--------+-------+------------+ | id | item | price | name | +----+--------+-------+------------+ | 3 | 商品3 | 300 | メーカー1 | | 2 | 商品2 | 200 | メーカー2 | | 1 | 商品1 | 100 | メーカー3 | +----+--------+-------+------------+ 3 rows in set (0.000 sec)
今回使用したコード
# 商品テーブル作成 CREATE TABLE test1 ( id INT AUTO_INCREMENT PRIMARY KEY, item CHAR(10), price INT, maker INT ); # 商品テーブルにレコード挿入 INSERT INTO test1 (item, price, maker) VALUES ('商品1', 100, 3), ('商品2', 200, 2), ('商品3', 300, 1); # メーカーテーブル作成 CREATE TABLE test2 ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(10) ); # メーカーテーブルにレコード挿入 INSERT INTO test2 (name) VALUES ('メーカー1'), ('メーカー2'), ('メーカー3'); # 商品テーブルとメーカーテーブルをリレーションして表示 SELECT * FROM test1 JOIN test2 ON test1.maker=test2.id; # リレーションしたテーブルを一時テーブルとして保存:フィールド名かぶりでエラー CREATE TEMPORARY TABLE tmp1 as SELECT * FROM test1 JOIN test2 ON test1.maker=test2.id; # リレーションしたテーブルを一時テーブルとして保存:フィールド名を指定してエラー回避 CREATE TEMPORARY TABLE tmp1 as SELECT test1.id, item, price, maker, test2.id as m_id, name FROM test1 JOIN test2 ON test1.maker=test2.id; # 一時テーブルを表示 SELECT * FROM tmp1; # リレーションしたテーブルを一時テーブルとして保存:必要なフィールド名のみ指定 CREATE TEMPORARY TABLE tmp2 as SELECT test1.id, item, price, name FROM test1 JOIN test2 ON test1.maker=test2.id; # 一時テーブルを表示 SELECT * FROM tmp2; # 一時テーブルの削除 DROP TEMPORARY TABLE tmp1; DROP TEMPORARY TABLE tmp2;