mysql创建与删除临时表sql语句

在mysql应用开发中临时表是我们一个很重要的数据处理优化东西,我们经常会处理各种大数据或其它问题利用TEMPORARY临时表来处理,下面我来介绍临时表创建与删除方法.

临时表可能是非常有用的,在某些情况下,保持临时数据,最重要的是应该知道的临时表是,他们将当前的客户端会话终止时被删除.

当你创建临时表的时候,你可以使用temporary关键字,代码如下:

1. create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)‘ 
2.  
3. //或 
4.  
5. CREATE TEMPORARY TABLE IF NOT EXISTS sp_output_tmp ENGINE = MEMORY SELECT …from … where ID=current_id; 

临时表只在当前连接可见,当这个连接关闭的时候,会自动drop,这就意味着你可以在两个不同的连接里使用相同的临时表名,并且相互不会冲突,或者使用 已经存在的表,但不是临时表的表名,当这个临时表存在的时候,存在的表被隐藏了,如果临时表被drop,存在的表就可见了,创建临时表你必须有create temporary table 权限.

下面几点是临时表的限制:

临时表只能用在 memory,myisam,merge,或者innodb

临时表不支持mysql cluster(簇)

在同一个query语句中,你只能查找一次临时表,例如,下面的就不可用,代码如下:

mysql> SELECT * FROM temp_table, temp_table AS t2;

ERROR 1137: Can't reopen table: 'temp_table'

如果在一个存储函数里,你用不同的别名查找一个临时表多次,或者在这个存储函数里用不同的语句查找,这个错误都会发生.

show tables 语句不会列举临时表,你不能用rename来重命名一个临时表,但是,你可以alter table代替,代码如下:

mysql>ALTER TABLE orig_name RENAME new_name;

临时表用完后要记得drop掉,代码如下:

DROP TEMPORARY TABLE IF EXISTS sp_output_tmp;

创建临时表,实例,下面是一个例子,使用临时表在PHP脚本中,使用mysql_query()函数,可以使用相同的代码,代码如下:

1. mysql> CREATE TEMPORARY TABLE SalesSummary ( 
2.     -> product_name VARCHAR(50) NOT NULL 
3.     -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 
4.     -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 
5.     -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 
6. ); 
7. Query OK, 0 rows affected (0.00 sec) 
8.  
9. mysql> INSERT INTO SalesSummary 
10.     -> (product_name, total_sales, avg_unit_price, total_units_sold) 
11.     -> VALUES 
12.     -> ('cucumber', 100.25, 90, 2); 
13.  
14. mysql> SELECT * FROM SalesSummary; 
15. +--------------+-------------+----------------+------------------+ 
16. | product_name | total_sales | avg_unit_price | total_units_sold | 
17. +--------------+-------------+----------------+------------------+ 
18. | cucumber     |      100.25 |          90.00 |                2 | 
19. +--------------+-------------+----------------+------------------+ 
20. 1 row in set (0.00 sec) 

当发出一个SHOW TABLES命令,那么临时表将不会被列在列表中,现在如果将MySQL的会话的注销,那么会发出SELECT命令,那么会发现没有在数据库中的数据,即使临时表也就不存在了.

删除临时表:默认情况下,所有的临时表被删除时,MySQL的数据库连接被终止,不过要删除他们之前就应该发出DROP TABLE命令.

下面的例子为删除一个临时表,代码如下:

1. mysql> CREATE TEMPORARY TABLE SalesSummary ( 
2.     -> product_name VARCHAR(50) NOT NULL 
3.     -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 
4.     -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 
5.     -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 
6. ); 
7. Query OK, 0 rows affected (0.00 sec) 
8.  
9. mysql> INSERT INTO SalesSummary 
10.     -> (product_name, total_sales, avg_unit_price, total_units_sold) 
11.     -> VALUES 
12.     -> ('cucumber', 100.25, 90, 2); 
13.  
14. mysql> SELECT * FROM SalesSummary; 
15. +--------------+-------------+----------------+------------------+ 
16. | product_name | total_sales | avg_unit_price | total_units_sold | 
17. +--------------+-------------+----------------+------------------+ 
18. | cucumber     |      100.25 |          90.00 |                2 | 
19. +--------------+-------------+----------------+------------------+ 
20. 1 row in set (0.00 sec)  //phpfensi.com 
21. mysql> DROP TABLE SalesSummary; 
22. mysql>  SELECT * FROM SalesSummary; 
23. ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist