关于mysql建立临时表的问题（新手入门）

关于 MYSQL 临时表的问题，求解答~

MySQL 需要创建隐式临时表来解决某些类型的查询。往往查询的排序阶段需要依赖临时表。例如，当您使用 GROUP BY，ORDER BY 或DISTINCT 时。这样的查询分两个阶段执行：首先是收集数据并将它们放入临时表中，然后是在临时表上执行排序。
对于某些 UNION 语句，不能合并的 VIEW，子查询时用到派生表，多表 UPDATE 以及其他一些情况，还需要使用临时表。如果临时表很小，可以到内存中创建，否则它将在磁盘上创建。MySQL 在内存中创建了一个表，如果它变得太大，就会被转换为磁盘上存储。内存临时表的最大值由 tmp_table_size 或 max_heap_table_size 值定义，以较小者为准。MySQL 5.7 中的默认大小为 16MB。如果运行查询的数据量较大，或者尚未查询优化，则可以增加该值。设置阈值时，请考虑可用的 RAM 大小以及峰值期间的并发连接数。你无法无限期地增加变量，因为在某些时候你需要让 MySQL 使用磁盘上的临时表。
注意：如果涉及的表具有 TEXT 或 BLOB 列，则即使大小小于配置的阈值，也会在磁盘上创建临时表。

就是SQL中写建表的CREATE TABLE 语句了，具体你完全可以参考ORACLE中的DDL——区别两者的语法差异。

BTW，这种方法，往往很难，本身两者有不少差别的；从一台机器搬另一台机器，也是要时间；还要费事去管理这个表中的资料（添加、修改、清空等）

1、临时表再断开于mysql的连接后系统会自动删除临时表中的数据，但是这只限于用下面语句建立的表：
1)定义字段
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)
2)直接将查询结果导入临时表
CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

2、另外mysql也允许你在内存中直接创建临时表，因为是在内存中所有速度会很快，语法如下：
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP

3、从上面的分析可以看出临时表的数据是会被清空的，你断开了连接就会被自动清空，但是你程序中不可能每发行一次sql就连接一次数据库吧(如果是这样的话，那就会出现你担心的问题，如果不是就没有问题)，因为只有断开数据库连接才会被清空数据，在一个数据库连接里面发行多次sql的话系统是不会自动清空临时表数据的。

---
以上，希望对你有所帮助。

mysql的临时表和sql server不太一样。没有create @table（）这样写法。一般的复杂query，如果mysql认为要用临时表会自动在临时目录下创建像#234.frm这样的文件。如果你要为加快查询手工创建临时表，一般都是先truncate 清空。再向其中insert 数据，最后才是select。

提到MySQL临时表，我们都很熟悉了，一般来说，分为两类：

1. MySQL 临时表引擎，名字叫做 Memory。比如

create table tmp1(id int, str1 varchar(100) ) engine = memory;

由参数max_heap_table_size 来控制，超过报错。

2. 非临时表的引擎，这里又分为两类：

• 用户自定义的临时表，比如:

• create temporary table (id int, str1 varchar(100) );



• SQL执行过程中产生的内部临时表，比如：UNION , 聚合类ORDER BY，派生表，大对象字段的查询，子查询或者半连接的固化等等场景。

那么这两种临时表的计数器通常用 show global status like '%tmp_%tables%' 来查看。比如

• mysql> show status like '%tmp_%tables%';``+-------------------------+-------+``| Variable_name           | Value |``+-------------------------+-------+``| Created_tmp_disk_tables | 0     |``| Created_tmp_tables      | 0     |``+-------------------------+-------+``2 rows in set (0.00 sec)



以上结果分别代表，只创建磁盘上的临时表计数以及临时表的总计数。这两个计数器由参数 tmp_table_size 和 max_heap_table_size 两个取最小值来控制。

那在 MySQL 5.7 之前，这个 SQL 运行中产生的临时表是 MYISAM，而且只能是 MYISAM。那 MySQL 从 5.7 开始提供了参数 Internal_tmp_mem_storage_engine 来定义内部的临时表引擎，可选值为 MYISAM 和 INNODB 。当然这里我们选择 INNODB 。并且把内部的临时表默认保存在临时表空间 ibtmp1 (可以用参数 innodb_temp_data_file_path 设置大小以及步长等)下。当然这里我们得控制下 ibtmp1 的大小，要不然一个烂SQL就把磁盘整爆了。

但是MySQL 5.7 之前都没有解决如下问题:

• VARCHAR的变长存储。那就是如果临时表的字段定义是 VARCHAR(200)，那么映射到内存里处理的字段变为CHAR(200)。假设 VARCHAR(200) 就存里一个字符 "Y", 那岂不是很大的浪费。

• 大对象的默认磁盘存储，比如 TEXT，BLOB， JSON等，不管里面存放了啥，直接转化为磁盘存储。

MySQL 8.0 开始，专门实现了一个临时表的引擎 TempTable , 解决了 VARCHAR字段的边长存储以及大对象的内存存储。由变量 interal_tmp_mem_storage_engine来控制，可选值为 TempTable（默认）和 Memory；新引擎的大小由参数temp_table_max_ram 来控制，默认为1G。超过了则存储在磁盘上（ibtmp1）。并且计数器由性能字典的表 memory_summary_global_by_event_name 来存储。

• mysql> SELECT * FROM performance_schema. memory_summary_global_by_event_name WHERE event_name like '%temptable%'G*************************** 1. row ***************************                  EVENT_NAME: **memory/temptable/physical_disk**                 COUNT_ALLOC: 0                  COUNT_FREE: 0   SUM_NUMBER_OF_BYTES_ALLOC: 0    SUM_NUMBER_OF_BYTES_FREE: 0              LOW_COUNT_USED: 0          CURRENT_COUNT_USED: 0             HIGH_COUNT_USED: 0    LOW_NUMBER_OF_BYTES_USED: 0CURRENT_NUMBER_OF_BYTES_USED: 0   HIGH_NUMBER_OF_BYTES_USED: 0*************************** 2. row ***************************                  EVENT_NAME: **memory/temptable/physical_ram**                 COUNT_ALLOC: 1                  COUNT_FREE: 0   SUM_NUMBER_OF_BYTES_ALLOC: 1048576    SUM_NUMBER_OF_BYTES_FREE: 0              LOW_COUNT_USED: 0          CURRENT_COUNT_USED: 1             HIGH_COUNT_USED: 1    LOW_NUMBER_OF_BYTES_USED: 0CURRENT_NUMBER_OF_BYTES_USED: 1048576   HIGH_NUMBER_OF_BYTES_USED: 10485762 rows in set (0.03 sec)



以上 memory/temptable/physical_disk 代表放入磁盘上的临时表计数情况。

memory/temptable/physical_ram 代表放入内存的临时表计数情况。

那总结下MySQL 8.0 引入的 TempTable 引擎：

• 默认内部临时表引擎。

• 支持变长字符类型的实际存储。

• 设置变量 temp_table_max_ram 来控制实际存储内存区域大小。

---

运城市15118661506： 关于mysql建立临时表的问题(新手入门) - ？
藏傅黄连： 1、临时表再断开于mysql的连接后系统会自动删除临时表中的数据,但是这只限于用下面语句建立的表: 1)定义字段 CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL) 2)直接将查询结...

运城市15118661506： mysql数据库怎么把查询出来的数据生成临时表 - ？
藏傅黄连： 创建临时表 方法一: create table #临时表名(字段1 约束条件, 字段2 约束条件, .....) create table ##临时表名(字段1 约束条件, 字段2 约束条件, .....) 方法二: select * into #临时表名 from 你的表; select * into ##临时表名 from 你的表; 注:以上的#代表局部临时表,##代表全局临时表查询临时表 select * from #临时表名; select * from ##临时表名;删除临时表 drop table #临时表名; drop table ##临时表名;

运城市15118661506： MySQL如何声明一个临时表? - ？
藏傅黄连： CREATE TEMPORARY TABLE tmp_table ( name VARCHAR(10) NOT NULL, value INTEGER NOT NULL ) 类似如上.或者:CREATE TEMPORARY TABLE tmp_table SELECT * FROM tables .

运城市15118661506： mysql 临时表的建立和用途 - ？
藏傅黄连： 可以把一些经常访问的数据放到临时表中,这样访问时会快一些 因为数据是在服务器内存中 另外每次查询的时候,数据库都需要生成一些临时数据在临时表里

运城市15118661506： sql中临时表创建与使用? - ？
藏傅黄连： 临时表有两种类型:本地临时表 以一个井号 (#) 开头的那些表名.只有在创建本地临时表的连接上才能看到这些表,链接断开时临时表即被删除(本地临时表为创建它的该链接的会话所独享)或者这样说局部临时表是有当前用户创建的,并...

运城市15118661506： 为什么在mysql存储过程中创建的临时表不能 - ？
藏傅黄连： 临时表 可以创建本地临时表和全局临时表.本地临时表仅在当前会话中可见,而全局临时表在所有会话中都可见.临时表不能分区.本地临时表的名称前面有一个数字符号 (#table_name),而全局临时表的名称前面有两个数字符号 (##table_name).除非使用 DROP TABLE 显式删除临时表,否则临时表将在退出其作用域时由系统自动删除:当存储过程完成时,将自动删除在存储过程中创建的本地临时表.由创建表的存储过程执行的所有嵌套存储过程都可以引用此表.但调用创建此表的存储过程的进程无法引用此表.

运城市15118661506： mysql怎么将查询结果导入临时表 - ？
藏傅黄连： 2种方法 第一种,建立正式的表,此表可供你反复查询 drop table if exists a_temp; create table a_temp as select 表字段名称 from 表名称 第二种,建立临时表,此表可供你当次链接的操作里查询.create temporary table 临时表名称 select 表字段名称 from 表名称

运城市15118661506： mysql临时表有哪些优缺点 - ？
藏傅黄连： 你这么想就对了:客户端新建了一个会话,这个会话只是服务器与客户端1对1的关系,客户端可能在服务端建立一个临时表,满足客户端处理某些事务的需求,当客户端退出会话后,这个临时表自动drop,没有任何数据信息占用数据库空间.这是第一个优点:省空间.客户端建立临时表只为特定的事务服务,这个表具有专用和私有性,不需要共享给其他人.所以就具备第二个优点:私密性.客户端建立的临时表具有单独操作和读写性能,因此速度上和处理效率上更高,这就具备第三个优点:高效率.类似的情况根据客户端的需求不同而不同,可能具备非常多的可能.

运城市15118661506： mysql中如何在存储过中立创建临时表 - ？
藏傅黄连： insert into tmp_table21 select name from cadre 这样行吗

运城市15118661506： mysql - query 不能创建临时表 - ？
藏傅黄连： 你是根据什么判断出不能创建临时表的//你的代码是这样写的吗 mysql_query(＂create temporary table aaa (id int not null)＂); mysql_query(＂insert into aaa values(12)＂);$r = mysql_query(＂select * from aaa＂);$row = mysql_fetch_row($r); var_dump($row);