使用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全文索引應用簡明教程,需要的朋友可以參考下2016-10-10
這篇文章主要介紹了mysql-8.0.17-winx64 部署方法,非常不錯，具有一定的參考借鑒價值，需要的朋友可以參考下2019-08-08
這篇文章主要介紹了MySQL數據庫下用戶及用戶權限配置的相關資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-08-08
相信大家應該都知道MySQL的慢查詢日志是MySQL提供的一種日志記錄,它用來記錄在MySQL中響應時間超過閥值的語句,下面這篇文章主要給大家介紹了關于MySQL優(yōu)化方案之開啟慢查詢日志的相關資料,需要的朋友可以參考下2021-12-12
這篇文章主要介紹了MySQL操作之JSON數據類型操作詳解，內容較為詳細，具有收藏價值，需要的朋友可以參考。2017-10-10
這篇文章主要介紹了詳解Mysql主從同步實戰(zhàn)，實例分析了Mysql主從同步的原理和實現，非常具有實用價值，需要的朋友可以參考下。2017-03-03
MySQL可以基于多表查詢更新數據，本文主要介紹了MySQL?UPDATE多表關聯更新的實現示例，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-08-08
使用虛擬主機空間上的phpmyadmin操作數據庫的時候，如果看到phpmyadmin首頁上顯示的MySQL 字符集為cp1252 West European (latin1)，當我們導入數據時就會出現亂碼2012-02-02
MySQL按年/月/周/日/小時分組查詢、排序、limit及判空用法實例
我們在用Mysql抽取數據時候,經常需要按照天、周、月等不同的粒度對數據進行分組統(tǒng)計,下面這篇文章主要給大家介紹了關于MySQL按年/月/周/日/小時分組查詢、排序、limit及判空用法的相關資料,需要的朋友可以參考下2023-03-03
因為 MySQL 是關系型數據庫，數據是拆分重組在多個數據表里面的。所以我們勢必要從多個數據表中提取數據，通過 SQL 語句的內連接與外連接就能夠實現多表查詢了，本文就來講講MySQL的外連接2022-08-08