下面是对MYSQL 临时表的理解
1、局部临时表(#开头)只对当前连接有效,当前连接断开时自动删除。
2、全局临时表(##开头)对其它连接也有效,在当前连接和其他访问过它的连接都断开时自动删除。
3、不管局部临时表还是全局临时表,只要连接有访问权限,都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。
临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。
内存表:
1.缺省存储引擎为MEMORY,表建在磁盘里,数据在内存里
2.可以通过参数max_heap_table_size来设定内存表大小
3.到达max_heap_table_size设定的内存上限后将报错
4.表定义保存在磁盘上,数据和索引保存在内存中
5.不能包含TEXT、BLOB等字段
临时表:
1.缺省存储引擎为MySQL服务器默认引擎,引擎类型只能是:memory(heap)、myisam、merge、innodb(memory临时表由于表的增大可能会转变为myisam临时表,表建在内存里,数据在内存里
2.可以通过参数 tmp_table_size 来设定临时表大小。
3.到达tmp_table_size设定的内存上限后将在磁盘上创建临时文件
4.表定义和数据都保存在内存中
5.可以包含TEXT, BLOB等字段
临时表一般比较少用,通常是在应用程序中动态创建或者由MySQL内部根据SQL执行计划需要自己创建。
内存表则大多作为Cache来使用,特别在没有第三方cache使用时。如今随着memcache、NoSQL的流行,越来越少选择使用内存表。
MySQL服务器使用内部临时表
在某些情况下,mysql服务器会自动创建内部临时表。查看查询语句的执行计划,如果extra列显示“using temporary”即使用了内部临时表。内部临时表的创建条件:
* group by 和 order by中的列不相同
* order by的列不是引用from 表列表中 的第一表
* group by的列不是引用from 表列表中 的第一表
* 使用了sql_small_result选项
* 含有distinct 的 order by语句
初始创建内部myisam临时表的条件:
* 表中存在text、blob列
* 在group by中的 列 有超过512字节
* 在distinct查询中的 列 有超过512字节
* 在union、union all联合查询中,select 列 列表中的 列 有超过512字节的
重启MySQL 数据库后,内存中的数据全部丢失。内存表的功能有部分的限制,有些属性不能像正常表一样使用。
创建一个临时表,但是到相应的数据目录下则找不到.frm文件的。
其中包括2个重要的参数
[mysqld]
# 内存表容量
max_heap_table_size=1024M
# 临时表容量
tmp_table_size=1024M
SQL 语法:
1)创建临时表:
mysql> CREATE TEMPORARY TABLE tmp_table (
-> name VARCHAR(10) NOT NULL,
-> value INTEGER NOT NULL
-> ) ;
Query OK, 0 rows affected (0.38 sec)
查看表状态
mysql> show CREATE TABLE tmp_table \G
*************************** 1. row ***************************
Table: tmp_table
Create Table: CREATE TEMPORARY TABLE `tmp_table` (
`name` varchar(10) NOT NULL,
`value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
它和正常表没有什么区别,但是数据是存在内存中的,你在响应的数据目录下找不到数据文件。
一旦断开连接你再查该表就会消失。
临时表使用有一些限制条件:
* 临时表在 memory、myisam、merge或者innodb上使用,并且不支持mysql cluster簇);
show tables语句不会列出临时表,在information_schema中也不存在临时表信息;show create table可以查看临时表;
* 不能使用rename来重命名临时表。但是可以alter table rename代替:
mysql>ALTER TABLE orig_name RENAME new_name;
* 可以复制临时表得到一个新的临时表,如:
mysql>create temporary table new_table select * from old_table;
* 但在同一个query语句中,相同的临时表只能出现一次。如:
可以使用:mysql> select * from temp_tb;
但不能使用:mysql> select * from temp_tb, temp_tb as t;
错误信息: ERROR 1137 (HY000): Can't reopen table: 'temp_tb'
同样相同临时表不能在存储函数中出现多次,如果在一个存储函数里,用不同的别名查找一个临时表多次,或者在这个存储函数里用不同的语句查找,都会出现这个错误。
* 但不同的临时表可以出现在同一个query语句中,如临时表temp_tb1, temp_tb2:
Mysql> select * from temp_tb1, temp_tb2;
临时表可以手动删除:
DROP TEMPORARY TABLE IF EXISTS temp_tb;
临时表主要用于对大数据量的表上作一个子集,提高查询效率。
2)创建内存表:
和临时表有点相似的是内存表,有的也称堆表。
mysql> CREATE TABLE mem_table (
-> name VARCHAR(10) NOT NULL,
-> value INTEGER NOT NULL
-> ) TYPE = HEAP;
Query OK, 0 rows affected, 1 warning (0.01 sec)
查看表状态
mysql> show CREATE TABLE mem_table \G
*************************** 1. row ***************************
Table: mem_table
Create Table: CREATE TEMPORARY TABLE `mem_table` (
`name` varchar(10) NOT NULL,
`value` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
你在数据目录下回发现一个mem_table.frm文件,所以内存表的数据是放在磁盘上的,但是插入数据后发现磁盘上没有数据文件,所以数据是在内存中的,因为采用的是memory 引擎。一旦Mysql shutdown了,则该表的数据将不存在。
该表因为数据是在内存中,所以数据会很快。缺点是一旦数据库shutdown,则数据会丢失。