MySQL臨時表的使用方法詳解

更新時間：2022年09月18日 16:53:50 作者：麥田里走一夜

在寫查詢時我們會經(jīng)常用到臨時表來存儲數(shù)據(jù),下面這篇文章主要給大家介紹了關于MySQL臨時表的使用方法,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下

1. 寫在前面的話

在開發(fā)數(shù)據(jù)庫時，特別是寫存儲過程，遇到比較復雜的需求，使用臨時表可以簡化很多邏輯。曾經(jīng)在一家互聯(lián)網(wǎng)金融公司供職，公司數(shù)據(jù)組團隊做數(shù)據(jù)清洗，寫SQL腳本時，一個查詢語句可以套到數(shù)層查詢，甚至十幾層?？雌饋韼装傩猩锨械哪_本，其實他只是一個查詢，就是說是一個select基于另一個select的結(jié)果。這樣層層疊疊，同時還包含了聚合、排序、關聯(lián)、聯(lián)合等，看起來是相當?shù)馁M勁，頭疼。

就如下列樣式：

SELECT
	T1.A1,
	T1.A2,
	T1.A3...
FROM
	(
		SELECT
			T2.B1,
			T2.B2,
			T2.B3...
		FROM
			(
				SELECT
					...
				UNION ALL
					SELECT
						...)
					WHERE
						...
					GROUP BY
						...) T1,
						(
							SELECT
								T3.C1,
								T3.C2,
								T3.C3...
							FROM
								(
									SELECT
										...)
									WHERE
										...
									GROUP BY
										...) T3
									WHERE
										T1.A1 = T3.C1...

如果這里能使用臨時表，那么就會使邏輯清晰很多，查詢效率也會得到提升。比如在多處使用到同一個查詢的結(jié)果時，就可以只執(zhí)行一次查詢，將結(jié)果保存為臨時表，在查詢過程中每次使用到時，直接從臨時表查就可以了，不用每次使用都再去查詢一遍原始數(shù)據(jù)，尤其是對于復雜關聯(lián)查詢結(jié)果。

當時數(shù)據(jù)是來源于呼叫中心，每天都會產(chǎn)生幾百萬行數(shù)據(jù)，一個月就上億行的數(shù)據(jù)量，就當時的機器性能基礎上，在這個數(shù)據(jù)量來做統(tǒng)計操作，如果沒有高效的腳本，是非常耗時間的。同時，數(shù)據(jù)組職員并沒有專業(yè)數(shù)據(jù)庫設計能力。

本文以數(shù)據(jù)庫World為例進行說明。

2. 臨時表的使用

World數(shù)據(jù)庫中數(shù)據(jù)表country放著全世界國家的相關信息。

2.1 創(chuàng)建一個只存放亞洲國家信息的臨時表

2.1.1 創(chuàng)建臨時表

創(chuàng)建一個只存放亞洲國家信息的臨時表，命名為TempAsiaCountries。
這里臨時只取四個字段Code,Name,SufaceArea,Population，那么創(chuàng)建臨時表的腳本如下：

# 創(chuàng)建臨時表
CREATE TEMPORARY TABLE TempAsiaCountries (
	`Code` VARCHAR (10),
	`name` VARCHAR (30),
	`SufaceArea` NUMERIC,
	`Population` NUMERIC
);

創(chuàng)建完之后，會發(fā)現(xiàn)在數(shù)據(jù)庫的表目錄下并沒有這個表，因為表是臨時的，所以不會出現(xiàn)在表目錄里。

2.1.2 向臨時表里寫數(shù)據(jù)

如同寫實體表數(shù)據(jù)一樣，可以通過INSERT INTO 關鍵字進行表插入數(shù)據(jù)操作。

# 寫數(shù)據(jù)到臨時表
INSERT INTO TempAsiaCountries
SELECT
	`Code`,
	`Name`,
	`SurfaceArea`,
	`Population`
FROM
	country
WHERE
	`Continent` = 'Asia';

此時，可以通過SELECT操作查詢臨時表里的數(shù)據(jù)。

2.2 在查詢過程中直接創(chuàng)建臨時表

在實際使用臨時表時，可以更快速便捷的創(chuàng)建臨時表，如下腳本，直接以查詢結(jié)果創(chuàng)建臨時表的方法。

CREATE TEMPORARY TABLE TempAsiaCountries 
SELECT
	`Code`,
	`Name`,
	`SurfaceArea`,
	`Population`
FROM
	country
WHERE
	`Continent` = 'Asia';

2.3 查詢臨時表中的數(shù)據(jù)

對臨時表的查詢操作與實體表一樣，如下是查詢前文中所創(chuàng)建的臨時表中的數(shù)據(jù)

# 查詢臨時表
SELECT *
FROM TempAsiaCountries;

可以看到查詢結(jié)果

2.4 刪除臨時表

# 刪除臨時表
DROP TABLE TempAsiaCountries;

此時再查，就會返回表不存在的結(jié)果

3. 以上操作的全部代碼

# 創(chuàng)建臨時表
CREATE TEMPORARY TABLE TempAsiaCountries (
	`Code` VARCHAR (10),
	`name` VARCHAR (30),
	`SufaceArea` NUMERIC,
	`Population` NUMERIC
);

# 第一種
# 寫數(shù)據(jù)到臨時表
INSERT INTO TempAsiaCountries
SELECT
	`Code`,
	`Name`,
	`SurfaceArea`,
	`Population`
FROM
	country
WHERE
	`Continent` = 'Asia';

# 查詢臨時表
SELECT *
FROM TempAsiaCountries;

# 第二種
# 查詢數(shù)據(jù)并寫到臨時表中
CREATE TEMPORARY TABLE TempAsiaCountries 
SELECT
	`Code`,
	`Name`,
	`SurfaceArea`,
	`Population`
FROM
	country
WHERE
	`Continent` = 'Asia';

# 刪除臨時表
DROP TABLE TempAsiaCountries;