使用MySQL實現select?into臨時表的功能
MySQL select into臨時表
最近在編寫sql語句時,遇到兩次將數據放temp表,然后將兩次的temp表進行inner join,再供后續(xù)insert數據時使用的場景。
寫完后發(fā)現執(zhí)行耗時較長,需要優(yōu)化,于是將一條長長的sql語句拆分成一個sql腳本,用臨時表去暫存數據后再進行inner join。
select into 臨時表
首先想到的是使用select into這個寫法:
select * into temp_test from user where id=007;
寫完在Navicat執(zhí)行報錯,發(fā)現MySQL居然是不支持select into這種寫法的,沒辦法,只能轉換思路。
這個時候我又想起來有一個create table as select * from old_table的用法,想著是不是可以通過select出來的數據直接創(chuàng)建一張臨時表。
寫完去Navicat執(zhí)行,這次又報錯了:
Statement violates GTID consistency: CREATE TABLE ... SELECT.
搜索資料發(fā)現,由于MySQL在5.6及更高的版本添加了enforce_gtid_consistency這個參數,默認設置為true, 只允許保證事務安全的語句被執(zhí)行。
沒招兒,還得用原始方法去實現。
create 臨時表
由于供后續(xù)使用的字段不超過十個,不算多,于是通過create方式創(chuàng)建表,后續(xù)使用數據后再刪除這個表,邏輯上這就成了一個臨時表。
大致的寫法如下:
USE database; -- 設置變量 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臨時表(可以將查詢結果存在臨時表中)
創(chuàng)建臨時表可以將查詢結果寄存
報表制作的查詢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 '檢修持續(xù)時長' ? from maintenanceprocess as m LEFT JOIN user u ON m.jobNumber = u.jobNumber where m.jobNumber = [$檢修人員編號] and loginTime = [$檢修開始時間]);#創(chuàng)建臨時表 ? select * from tmp_單個工位檢修結果表(檢查報告)上部;
備注:[$檢修開始時間]是可輸入查詢的值
(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結果保存為臨時表,有2種方法
第一種,建立正式的表,此表可供你反復查詢
drop table if exists a_temp; create table a_temp as select 表字段名稱 from 表名稱
或者,建立臨時表,此表可供你當次鏈接的操作里查詢.
create temporary table 臨時表名稱 select 表字段名稱 from 表名稱
以上為個人經驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關文章
MySQL按年/月/周/日/小時分組查詢、排序、limit及判空用法實例
我們在用Mysql抽取數據時候,經常需要按照天、周、月等不同的粒度對數據進行分組統(tǒng)計,下面這篇文章主要給大家介紹了關于MySQL按年/月/周/日/小時分組查詢、排序、limit及判空用法的相關資料,需要的朋友可以參考下2023-03-03