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

目录

MySQL select into临时表

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

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

select into 临时表

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

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方式创建表,后续使用数据后再删除这个表,逻辑上这就成了一个临时表。

大致的写法如下:

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)可以使用:

create table tmp (select ...)

举例:

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

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)创建临时表的另一种方式举例:

存储过程中:

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种方法

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

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

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

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

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。

原文地址:https://blog.csdn.net/u800820/article/details/121322445
15人参与, 0条评论 登录后显示评论回复

你需要登录后才能评论 登录/ 注册