使用MySQL实现select into临时表的功能

MySQL select into临时表

最近在编写sql语句时，遇到两次将数据放temp表，然后将两次的temp表进行inner join，再供后续insert数据时使用的场景。

写完后发现执行耗时较长，需要优化，于是将一条长长的sql语句拆分成一个sql脚本，用临时表去暂存数据后再进行inner join。

select into 临时表

首先想到的是使用select into这个写法：

?

1	select * into temp_test from user where id=007;

写完在Navicat执行报错，发现MySQL居然是不支持select into这种写法的，没办法，只能转换思路。

这个时候我又想起来有一个create table as select * from old_table的用法，想着是不是可以通过select出来的数据直接创建一张临时表。

写完去Navicat执行，这次又报错了：

Statement violates GTID consistency: CREATE TABLE ... SELECT.

搜索资料发现，由于MySQL在5.6及更高的版本添加了enforce_gtid_consistency这个参数，默认设置为true, 只允许保证事务安全的语句被执行。

没招儿，还得用原始方法去实现。

create 临时表

由于供后续使用的字段不超过十个，不算多，于是通过create方式创建表，后续使用数据后再删除这个表，逻辑上这就成了一个临时表。

大致的写法如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

USE database ; -- 设置变量 SET @testCode= 'T001' ; -- 创建临时表 DROP TABLE IF EXISTS temp_test; CREATE TABLE IF NOT EXISTS `temp_test`( ` name ` VARCHAR (255), `caption` VARCHAR (255), ` order ` INT (11), ... `entityId` BIGINT (20) ); INSERT INTO temp_test select item. name ,item.caption,item. order ,item.id from item item inner join base base on base.id=item.baseid where base.num= 'test01' and base.id= 'T01' select id into @itemid from temp_test; update user set systemid=@itemid where `code`=@testCode; ... INSERT INTO `base` (`userId`,`entityId`,` name `,`caption`, ...) SELECT tpitem.entityId,tpitem.CONCAT( 'pre_' ,tpitem. name ),tpitem.caption,tpitem. order ,... from ( select * from temp_test test inner join temp_test2 test2 on test.entityid=test2.entityid ) tpitem WHERE NOT EXISTS ( SELECT 1 FROM item WHERE `code`=@testCode limit 1); -- 删除临时表 DROP TABLE temp_test;

mysql临时表（可以将查询结果存在临时表中）

创建临时表可以将查询结果寄存

报表制作的查询sql中可以用到。

（1）关于寄存方式，mysql不支持：select * into tmp from maintenanceprocess

（2）可以使用：

?

1	create table tmp ( select ...)

举例：

#单个工位检修结果表上部

?

1 2 3 4 5 6 7 8 9

drop table if EXISTS tmp_单个工位检修结果表（检查报告）上部;   create table tmp_单个工位检修结果表（检查报告）上部 ( select workAreaName as '机器号' ,m.jobNumber as '检修人员编号' ,u.userName as '检修人员姓名' ,loginTime as '检修开始时间' ,   CONCAT(FLOOR((TIME_TO_SEC(exitTime) - TIME_TO_SEC(loginTime))/60), '分钟' ) as '检修持续时长'   from maintenanceprocess as m LEFT JOIN user u ON m.jobNumber = u.jobNumber where m.jobNumber = [$检修人员编号] and loginTime = [$检修开始时间]);#创建临时表   select * from tmp_单个工位检修结果表（检查报告）上部;

备注：[$检修开始时间]是可输入查询的值

（3）创建临时表的另一种方式举例：

存储过程中：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

BEGIN   #Routine body goes here...   declare cnt int default 0;      declare i int default 0;      set cnt = func_get_splitStringTotal(f_string,f_delimiter);      DROP TABLE IF EXISTS `tmp_split`;      create temporary table `tmp_split` (`val_` varchar (128) not null ) DEFAULT CHARSET=utf8;      while i < cnt      do      set i = i + 1;      insert into tmp_split(`val_`) values (func_splitString(f_string,f_delimiter,i));      end while;   END

mysql把select结果保存为临时表,有2种方法

第一种，建立正式的表，此表可供你反复查询

?

1 2 3

drop table if exists a_temp; create table a_temp as select 表字段名称 from 表名称

或者，建立临时表，此表可供你当次链接的操作里查询.

?

1 2	create temporary table 临时表名称 select 表字段名称 from 表名称

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/u800820/article/details/121322445