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

select * into temp_test from user where id=007;

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;

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_单个工位检修结果表（检查报告）上部;

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

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

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