mysql temporary table

MySQL  에서도 임시 테이블의 사용이 가능 합니다.

임시테이블은 해당 세션에서만 존재하므로, 통계작성등 복잡한 쿼리를 한방에 못하는 경우 중간에 끼어넣어 임시로 사용하면 유용할 듯 합니다.

테이블 만들기 :

CREATE TEMPORARY TABLE IF NOT EXISTS TEMP_TABLE (

       VALKEY CHAR(20) NOT NULL,

       VALNAME CHAR(100) NULL 

);

아래와 같이 SELECT 결과로 테이블을 만드는 방법도 효율적입니다.

CREATE TEMPORARY TABLE top10customers

SELECT p.customerNumber,

       c.customerName,

       FORMAT(SUM(p.amount),2) total

FROM payments p

INNER JOIN customers c ON c.customerNumber = p.customerNumber

GROUP BY p.customerNumber

ORDER BY total DESC

LIMIT 10

임시테이블은 세션을 종료하면 자동 DROP 됩니다.

사용예제 입니다.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create temporary table t (idx int, val int);
Query OK, 0 rows affected (0.14 sec)

mysql>  insert into t values(1, 100), (2, 120), (3, 95), (4, 200);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> set @v = 0; select val - @v delta, @v := val val from t order by idx;
Query OK, 0 rows affected (0.00 sec)

+-------+------+
| delta | val  |
+-------+------+
|   100 |  100 |
|    20 |  120 |
|   -25 |   95 |
|   105 |  200 |
+-------+------+
4 rows in set (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| DEPT           |
| EMP            |
| actor          |
| casting        |
| movie          |
| sample_t       |
+----------------+
6 rows in set (0.03 sec)

mysql> select * from t order by idx;
+------+------+
| idx  | val  |
+------+------+
|    1 |  100 |
|    2 |  120 |
|    3 |   95 |
|    4 |  200 |
+------+------+
4 rows in set (0.03 sec)

mysql> 

mysql> exit
Bye
phps$ sh gomysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66718
Server version: 5.5.28-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t order by idx;
ERROR 1146 (42S02): Table 'test.t' doesn't exist
mysql>