使用MySQL實現(xiàn)select?into臨時表的功能
MySQL select into臨時表
最近在編寫sql語句時，遇到兩次將數(shù)據(jù)放temp表，然后將兩次的temp表進(jìn)行inner join，再供后續(xù)insert數(shù)據(jù)時使用的場景。
寫完后發(fā)現(xiàn)執(zhí)行耗時較長，需要優(yōu)化，于是將一條長長的sql語句拆分成一個sql腳本，用臨時表去暫存數(shù)據(jù)后再進(jìn)行inner join。
select into 臨時表
首先想到的是使用select into這個寫法：
select * into temp_test from user where id=007;
寫完在Navicat執(zhí)行報錯，發(fā)現(xiàn)MySQL居然是不支持select into這種寫法的，沒辦法，只能轉(zhuǎn)換思路。
這個時候我又想起來有一個create table as select * from old_table的用法，想著是不是可以通過select出來的數(shù)據(jù)直接創(chuàng)建一張臨時表。
寫完去Navicat執(zhí)行，這次又報錯了：
Statement violates GTID consistency: CREATE TABLE ... SELECT.
搜索資料發(fā)現(xiàn)，由于MySQL在5.6及更高的版本添加了enforce_gtid_consistency這個參數(shù)，默認(rèn)設(shè)置為true, 只允許保證事務(wù)安全的語句被執(zhí)行。
沒招兒，還得用原始方法去實現(xiàn)。
create 臨時表
由于供后續(xù)使用的字段不超過十個，不算多，于是通過create方式創(chuàng)建表，后續(xù)使用數(shù)據(jù)后再刪除這個表，邏輯上這就成了一個臨時表。
大致的寫法如下：
USE database;
-- 設(shè)置變量
SET @testCode='T001';
-- 創(chuàng)建臨時表
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臨時表（可以將查詢結(jié)果存在臨時表中）
創(chuàng)建臨時表可以將查詢結(jié)果寄存
報表制作的查詢sql中可以用到。
（1）關(guān)于寄存方式，mysql不支持：select * into tmp from maintenanceprocess
（2）可以使用：
create table tmp (select ...)
舉例：
#單個工位檢修結(jié)果表上部
drop table if EXISTS tmp_單個工位檢修結(jié)果表（檢查報告）上部; ? create table tmp_單個工位檢修結(jié)果表（檢查報告）上部 (select workAreaName as '機器號',m.jobNumber as '檢修人員編號',u.userName as '檢修人員姓名',loginTime as '檢修開始時間', ? CONCAT(FLOOR((TIME_TO_SEC(exitTime) - TIME_TO_SEC(loginTime))/60),'分鐘') as '檢修持續(xù)時長' ? from maintenanceprocess as m LEFT JOIN user u ON m.jobNumber = u.jobNumber where m.jobNumber = [$檢修人員編號] and loginTime = [$檢修開始時間]);#創(chuàng)建臨時表 ? select * from tmp_單個工位檢修結(jié)果表（檢查報告）上部;
備注：[$檢修開始時間]是可輸入查詢的值
（3）創(chuàng)建臨時表的另一種方式舉例：
存儲過程中：
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結(jié)果保存為臨時表,有2種方法
第一種，建立正式的表，此表可供你反復(fù)查詢
drop table if exists a_temp; create table a_temp as select 表字段名稱 from 表名稱
或者，建立臨時表，此表可供你當(dāng)次鏈接的操作里查詢.
create temporary table 臨時表名稱 select 表字段名稱 from 表名稱
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了MySQL全文索引應(yīng)用簡明教程,需要的朋友可以參考下2016-10-10
這篇文章主要介紹了mysql-8.0.17-winx64 部署方法,非常不錯，具有一定的參考借鑒價值，需要的朋友可以參考下2019-08-08
MySQL數(shù)據(jù)庫下用戶及用戶權(quán)限配置
這篇文章主要介紹了MySQL數(shù)據(jù)庫下用戶及用戶權(quán)限配置的相關(guān)資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-08-08
MySQL?UPDATE多表關(guān)聯(lián)更新的實現(xiàn)示例
MySQL按年/月/周/日/小時分組查詢、排序、limit及判空用法實例
