内存表:
session 1 $ mysql -uroot root@(none) 10:05:06>use test database changed root@test 10:06:06>create table tmp_memory (i int) engine = memory; query ok, 0 rows affected (0.00 sec) root@test 10:08:46>insert into tmp_memory values (1); query ok, 1 row affected (0.00 sec) root@test 10:08:46> session2 $ mysql -uroot test root@test 10:05:12>create table tmp_memory (i int) engine = memory; error 1050 (42s01): table 'tmp_memory' already exists root@test 10:16:27>select * from tmp_memory; +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec)
1. 多个session,创建表的名字不能一样
2. 一个session创建会话后,对其他session也是可见的
3. data目录下只有tmp_memory.frm ,表结构放在磁盘上,数据放在内存中
4. mysql 重启或者关闭后内存表里的数据会丢失,但是表结构仍然存在
5. 可以创建索引,删除索引,支持唯一索引
6. 不影响主备,主库上插入的数据,备库也可以查到
7. show tables 看得到表
临时表:
session1 $ mysql -uroot test root@test 10:30:18>create temporary table tmp_table (name varchar(10) not null,value integer not null); query ok, 0 rows affected (0.05 sec) root@test 10:31:54>select * from tmp_table; +--------+-------+ | name | value | +--------+-------+ | aaaaaa | 10 | +--------+-------+ 1 row in set (0.00 sec) session2 root@test 10:20:13> create temporary table tmp_table (name varchar(10) not null,value integer not null); query ok, 0 rows affected (0.02 sec) root@test 10:30:39>insert into tmp_table values ('bbbbbbb',10); query ok, 1 row affected (0.01 sec) root@test 10:31:33>select * from tmp_table; +---------+-------+ | name | value | +---------+-------+ | bbbbbbb | 10 | +---------+-------+ 1 row in set (0.00 sec) root@test 10:31:43>exit bye [1 single:ms-master db152011.sqa.cm6: mysql ~ ] $ mysql -uroot test root@test 10:32:17>select * from tmp_table; error 1146 (42s02): table 'test.tmp_table' doesn't exist root@test 10:32:22> root@test 10:32:23>
1. 创建的表的名字可以一样
2. 表结构和数据都放在内存中
3. 会话消失表结构和数据都消失
4. 可以创建索引,删除索引
5. 主库创建的表,备库查不到,
6. show tables 看不到表
使用内存表需要注意的事项
1. 内存表需要自己delete数据或者drop表;需要drop权限,这点比较危险
2. 内存表的表结构是保存在磁盘上的,如果多个session使用同一个表名,会存在冲突;如果不需要使用表名,如果使用一次都需要创建表结构,到时候会有很多小文件存在,不利于db的维护,dba清理表也有风险;
基于以上不适合用内存表
1. 临时表是会话级别的,即使多个session创建的表名一样,都相互不影响
2. 会话消失,所有的都消失,这点很不利于应用排查问题
另外这两个都需要消耗额外的内存空间,虽然db端可以忍受,但是不太可控;db端还有这个参数:
max_tmp_tables 一个客户能同时保持打开的临时表的最大数量,这个值默认32,可以根据需要调整此值
补充:mysql创建临时表,将查询结果插入已有表中
今天遇到一个很棘手的问题,想临时存起来一部分数据,然后再读取。我记得学数据库理论课老师说可以创建临时表,不知道mysql有没有这样的功能呢?临时表在内存之中,读取速度应该比视图快一些。然后还需要将查询的结果存储到临时表中。
下面是创建临时表以及插入数据的例子,以供大家参考。
a、临时表再断开于mysql的连接后系统会自动删除临时表中的数据,但是这只限于用下面语句建立的表:
1)定义字段
create temporary table tmp_table ( name varchar(10) not null, time date not null )
更高级点就是:
create temporary table `temtable` ( `jws` varchar(100) character set utf8 collate utf8_bin not null, `tzlb` varchar(100) character set utf8 collate utf8_bin not null, `uptime` date not null ) engine=innodb default charset=latin1″
连编码方式都规定了。。呵呵,以防乱码啊。
2)直接将查询结果导入临时表
create temporary table tmp_table select * from table_name
b、另外mysql也允许你在内存中直接创建临时表,因为是在内存中所有速度会很快,语法如下:
create temporary table tmp_table ( name varchar(10) not null, value integer not null ) type = heap
那如何将查询的结果存入已有的表呢?
1、可以使用a中第二个方法
2、使用insert into temtable (select a,b,c,d from tablea)”;
以上为个人经验,希望能给大家一个参考,也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方,望不吝赐教。