セッション単位で利用できる一時テーブルの作成方法。
セッション単位のためセッションが切れるとテーブル情報が削除されます。
リレーションさせたテーブルを一時テーブル化
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;
