MySQL 内存表和临时表的用法详解_Mysql

内存表：

									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)直接将查询结果导入临时表

1	`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)”;

MySQL 数据库之内存临时表参数

一、内部内存临时表的最大大小

该变量不适用于用户创建的MEMORY表。临时表会在一些较复杂查询中创建。实际限制由tmp_table_size和max_heap_table_size中较小的值确定。
tmp-table-size参数Default 16777216

1.1、内存临时表和硬盘临时表
如果内存中的临时表超出限制，MySQL会自动将其转换为磁盘上的临时表。
从MySQL 5.7.5开始，internal_tmp_disk_storage_engine选项定义用于磁盘临时表的存储引擎。
在MySQL 5.7.5之前，使用MyISAM存储引擎。
创建的内存临时表的数量：Created_tmp_tables
创建的硬盘临时表的数量：Created_tmp_disk_tables
补充：Created_tmp_files

1.2、设置参考
在设置tmp-table-size时，请参考硬盘临时表和内存临时表的数值。
一般的比例关系是:Created_tmp_disk_tables/Created_tmp_tables<5%
如果您执行了许多高级的GROUP BY查询并且您拥有大量内存，请增加tmp_table_size的值（如有必要，请增大max_heap_table_size）。

二、MEMORY表最大SIZE

该变量设置用户创建的MEMORY表允许增长的最大大小。他的变量值是用来计算MEMORY表的MAX_ROWS值的。
max_heap_table_size参数default 16777216

2.1、使用方式
设置此变量对任何现有的MEMORY表都没有影响，除非使用CREATE TABLE等语句重新创建表或使用ALTER TABLE或TRUNCATE TABLE进行更改。
服务器重启也将现有MEMORY表的最大大小设置为全局max_heap_table_size值。
该变量还与tmp_table_size一起使用，以限制内部内存表的大小。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。

原文链接：https://www.cnblogs.com/sunss/p/3191137.html