Learning site for website creation

MySQLで一時テーブルを作成

  • 投稿日:2020年09月25日

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

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

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

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;