关于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)定义字段
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,派生表,大对象字段的查询,子查询或者半连接的固化等等场景。
- 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)
VARCHAR的变长存储。那就是如果临时表的字段定义是 VARCHAR(200),那么映射到内存里处理的字段变为CHAR(200)。假设 VARCHAR(200) 就存里一个字符 "Y", 那岂不是很大的浪费。
大对象的默认磁盘存储,比如 TEXT,BLOB, JSON等,不管里面存放了啥,直接转化为磁盘存储。
- 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)
默认内部临时表引擎。
支持变长字符类型的实际存储。
设置变量 temp_table_max_ram 来控制实际存储内存区域大小。
那么这两种临时表的计数器通常用 show global status like '%tmp_%tables%' 来查看。比如
以上结果分别代表,只创建磁盘上的临时表计数以及临时表的总计数。这两个计数器由参数 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 之前都没有解决如下问题:
MySQL 8.0 开始,专门实现了一个临时表的引擎 TempTable , 解决了 VARCHAR字段的边长存储以及大对象的内存存储。由变量 interal_tmp_mem_storage_engine来控制,可选值为 TempTable(默认)和 Memory;新引擎的大小由参数temp_table_max_ram 来控制,默认为1G。超过了则存储在磁盘上(ibtmp1)。并且计数器由性能字典的表 memory_summary_global_by_event_name 来存储。
以上 memory/temptable/physical_disk 代表放入磁盘上的临时表计数情况。
memory/temptable/physical_ram 代表放入内存的临时表计数情况。
那总结下MySQL 8.0 引入的 TempTable 引擎:
MySQL临时表的用法和创建方式mysql临时表
例如,以下是创建一个名为temp_customer的局部临时表的语法:CREATE TEMPORARY TABLE #temp_customer (id INT,name VARCHAR(50));全局临时表(Global temporary table)在数据库的所有会话之间都可用,并在所有会话结束时自动删除。全局临时表的名字以两个井号(##)开头。例如,以下是创建一个名为##te...
MySQL中临时表的特点简介mysql中临时表的特点
一、创建临时表 MySQL创建临时表的方法与普通表一样,只不过在表名前要添加前缀“#”或“tmp_”。例如,我们要建立一个临时表保存商品信息:CREATE TEMPORARY TABLE #tmp_product (prod_id INT,prod_name VARCHAR(50),prod_price DECIMAL(8,2));二、使用临时表 使用临时表的方式与普通的表并没有...
Mysql中的临时表使用方法讲解
创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字:CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL)临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。DROP TABLE tmp_...
关于mysql建立临时表的问题(新手入门)
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、另外...
win10系统搭建本地mysql数据库 与sql常用指令!
首先,从官方网站下载MySQL的Windows压缩包。解压后,由于默认没有配置文件,需要创建一个名为my.ini的文本文件,并输入必要的配置信息。接着,设置环境变量,右键点击桌面的"此电脑",进入系统属性,找到MySQL的bin文件夹并添加至系统路径。从第四步开始,确保以管理员权限运行cmd。依次执行以下命令:mysql...
mysql使用oci8在Linux上搭建专业的mysql数据库oci8linux
最近,随着云计算的发展,企业级的MySQL数据库日渐成为主流。大多数企业都使用OCI8在Linux上搭建非常专业的MySQL数据库。如何在Linux上使用OCI8搭建MySQL数据库,将成为企业建立专业的MySQL数据库的重要前提。首先,我们需要安装OCI(Oracle Client Instant Client)和MySQL服务器,在安装MySQL服务器之前,必须检查...
mysql中如何在存储过中立创建临时表
如果临时表很小,可以到内存中创建,否则它将在磁盘上创建。MySQL 在内存中创建了一个表,如果它变得太大,就会被转换为磁盘上存储。内存临时表的最大值由 tmp_table_size 或 max_heap_table_size 值定义,以较小者为准。MySQL 5.7 中的默认大小为 16MB。如果运行查询的数据量较大,或者尚未查询...
MySQL如何使用临时表
解决办法:当临时表不存在时,将查询结果保存在临时表中:CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name;我的具体代码是 CREATE temporary table tmp (SELECT t1.pid a, t1.pname b,t2.pid c, t2.pdescribe d FROM (SELECT pid,pname FROM admin_advert_place WHERE pid IN (...
如何正确合理的建立MYSQL数据库索引
username=‘admin‘;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。MySQL索引类型包括:(1)...
MySQL中关于临时表的一些基本使用方法
实例 下面是一个例子,使用临时表在PHP脚本中,使用mysql_query()函数,可以使用相同的代码。mysql> CREATE TEMPORARY TABLE SalesSummary (-> product_name VARCHAR(50)NOT NULL -> ,total_sales DECIMAL(12,2)NOT NULL DEFAULT 0.00 -> ,avg_unit_price DECIMAL(7,2)NOT NULL DEFAULT 0.00 -...
藏傅黄连: 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);