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