MySQLで一時テーブルを作成

公開日:2020年09月25日

MySQL

セッション単位で利用できる一時テーブルの作成方法。

セッション単位のためセッションが切れるとテーブル情報が削除されます。

リレーションさせたテーブルを一時テーブル化

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;

コンテンツ一覧

同じカテゴリーのコンテンツ