セッション単位で利用できる一時テーブルの作成方法。
セッション単位のためセッションが切れるとテーブル情報が削除されます。
リレーションさせたテーブルを一時テーブル化
CREATE TEMPORARY TABLE 一時テーブル名 as SELECT 使用するフィールド１, 使用するフィールド２ FROM テーブル名１ JOIN テーブル名２ ON 照合フィールド１=照合フィールド２;
一時テーブルを削除
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
('商品１', 100, 3),
('商品２', 200, 2),
('商品３', 300, 1);
test1テーブル表示
MariaDB [lesson]> SELECT * FROM test1; +----+--------+-------+-------+ | id | item | price | maker | +----+--------+-------+-------+ | 1 | 商品１ | 100 | 3 | | 2 | 商品２ | 200 | 2 | | 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
('メーカー１'),
('メーカー２'),
('メーカー３');
test2テーブル表示
MariaDB [lesson]> SELECT * FROM test2; +----+------------+ | id | name | +----+------------+ | 1 | メーカー１ | | 2 | メーカー２ | | 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 | 商品３ | 300 | 1 | 1 | メーカー１ | | 2 | 商品２ | 200 | 2 | 2 | メーカー２ | | 1 | 商品１ | 100 | 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 | 商品３ | 300 | 1 | 1 | メーカー１ | | 2 | 商品２ | 200 | 2 | 2 | メーカー２ | | 1 | 商品１ | 100 | 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 | 商品３ | 300 | メーカー１ | | 2 | 商品２ | 200 | メーカー２ | | 1 | 商品１ | 100 | メーカー３ | +----+--------+-------+------------+ 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
('商品１', 100, 3),
('商品２', 200, 2),
('商品３', 300, 1);
# メーカーテーブル作成
CREATE TABLE test2 (
id INT AUTO_INCREMENT PRIMARY KEY,
name CHAR(10)
);
# メーカーテーブルにレコード挿入
INSERT INTO test2 (name) VALUES
('メーカー１'),
('メーカー２'),
('メーカー３');
# 商品テーブルとメーカーテーブルをリレーションして表示
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;
