ตารางชั่วคราวของ MySQL: วิธีการทำงาน, ประโยชน์, และแนวทางปฏิบัติที่ดีที่สุด

1. บทนำ

เมื่อทำงานกับ MySQL “ตารางชั่วคราว” เป็นวิธีที่มีประโยชน์ในการจัดเก็บและประมวลผลข้อมูลชั่วคราว โดยการใช้ตารางชั่วคราว คุณสามารถเก็บข้อมูลชั่วคราวเพื่อลดภาระของคำสั่งค้นหาที่ซับซ้อนและปรับปรุงประสิทธิภาพของการประมวลผลแบบแบทช์

ในบทความนี้ เราจะอธิบายว่าตารางชั่วคราวของ MySQL คืออะไร พร้อมกับกรณีการใช้งานและประโยชน์ของมันอย่างละเอียด

ตารางชั่วคราวคืออะไร?

ตารางชั่วคราวคือ ตารางที่ใช้ได้เฉพาะภายในเซสชัน
ต่างจากตารางปกติ มันไม่ถูกจัดเก็บอย่างถาวรในฐานข้อมูล และจะ ถูกลบอัตโนมัติเมื่อเซสชันสิ้นสุดลง

ลักษณะสำคัญของตารางชั่วคราวมีดังนี้:

• มีอยู่เฉพาะในแต่ละเซสชัน (ไม่สามารถเข้าถึงจากการเชื่อมต่ออื่นได้)
• ถูกลบอัตโนมัติเมื่อเซสชันสิ้นสุดลง
• สามารถใช้งานได้โดยไม่รบกวนแม้ว่าจะมีตารางปกติที่มีชื่อเดียวกันอยู่
• มักใช้เพื่อปรับปรุงประสิทธิภาพ

ตารางชั่วคราวเหมาะสำหรับการวิเคราะห์ข้อมูลและการประมวลผลข้อมูลชั่วคราว และมักใช้เป็น ตัวสนับสนุนสำหรับการประมวลผลแบบแบทช์และงานรวมข้อมูล

ประโยชน์ของการใช้ตารางชั่วคราว

การใช้ตารางชั่วคราวสามารถทำให้การประมวลผลข้อมูลมีประสิทธิภาพมากขึ้น นี่คือประโยชน์หลักสามประการ

1. ปรับปรุงประสิทธิภาพของคำสั่งค้นหา

เมื่อจัดการข้อมูลจำนวนมาก การใช้ JOIN หลายตัวและ subquery สามารถทำให้การประมวลผลซับซ้อนและเพิ่มภาระของฐานข้อมูล ด้วยตารางชั่วคราว คุณสามารถ กรองและจัดเก็บข้อมูลล่วงหน้า เพื่อเร่งการเรียกใช้คำสั่งค้นหา

2. เหมาะสำหรับการจัดเก็บข้อมูลชั่วคราว

ในการประมวลผลแบบแบทช์หรือการแปลงข้อมูล คุณอาจต้องจัดเก็บข้อมูลชั่วคราวและดำเนินการที่จำเป็น ตารางชั่วคราวช่วยให้คุณจัดเก็บข้อมูลชั่วคราวและเปิดใช้งาน การประมวลผลในหน่วยความจำอย่างรวดเร็ว

3. รักษาความปลอดภัยของข้อมูลที่มีอยู่

การจัดการข้อมูลการผลิตโดยตรงมีความเสี่ยง โดยการใช้ตารางชั่วคราว คุณสามารถ ประมวลผลข้อมูลโดยไม่เปลี่ยนแปลงข้อมูลการผลิตและลดความเสี่ยงของข้อผิดพลาด

สรุป

ตารางชั่วคราวของ MySQL เป็นเครื่องมือที่สะดวกสำหรับการจัดเก็บและประมวลผลข้อมูลชั่วคราว

• มีขอบเขตเฉพาะเซสชัน และถูกลบเมื่อเซสชันสิ้นสุดลง
• มีประโยชน์สำหรับการปรับปรุงประสิทธิภาพและการประมวลผลแบบแบทช์
• อนุญาตให้ดำเนินการอย่างปลอดภัยโดยไม่เปลี่ยนแปลงข้อมูลการผลิต

2. พื้นฐานของตารางชั่วคราว

ตารางชั่วคราวของ MySQL ใช้สำหรับจัดเก็บข้อมูลชั่วคราว ต่างจากตารางปกติ ในส่วนนี้ เราจะอธิบายแนวคิดพื้นฐานของตารางชั่วคราวอย่างละเอียด รวมถึง “ความแตกต่างจากตารางปกติ” และ “ความแตกต่างจากตารางชั่วคราวภายใน”

ความแตกต่างระหว่างตารางชั่วคราวและตารางปกติ

ตารางชั่วคราวและตารางปกติมีความแตกต่างอย่างมากใน การรักษาข้อมูลและพฤติกรรมการเข้าถึง ตารางด้านล่างสรุปความแตกต่างหลัก

ประเด็นสำคัญ

• ตารางชั่วคราว แยกจากกันในแต่ละเซสชัน และไม่ปรากฏให้ผู้ใช้รายอื่นเห็น
• คุณสามารถสร้างได้โดยไม่เกิดข้อผิดพลาดแม้ว่าจะมีตารางปกติที่มีชื่อเดียวกันอยู่
• สร้างอย่างชัดเจนโดยใช้ CREATE TEMPORARY TABLE และ ถูกลบอัตโนมัติเมื่อเซสชันสิ้นสุดลง

ความแตกต่างระหว่างตารางชั่วคราวและตารางชั่วคราวภายใน

นอกจาก ตารางชั่วคราวที่ผู้ใช้สร้าง MySQL ยังสร้าง ตารางชั่วคราวภายใน อัตโนมัติ พวกมันอาจฟังดูคล้ายกัน แต่จุดประสงค์และการจัดการต่างกัน

ตารางชั่วคราวภายในคืออะไร?

• MySQL อาจ สร้างตารางชั่วคราวภายใน เพื่อปรับปรุงคำสั่งค้นหาบางประเภท (เช่น GROUP BY , ORDER BY , DISTINCT )
• ผู้ใช้ปลายทางไม่สามารถจัดการโดยตรง (คุณไม่สามารถสร้างอย่างชัดเจนเหมือน CREATE TEMPORARY TABLE )
• สร้างตามความจำเป็นระหว่างการเรียกใช้คำสั่งค้นหาและ ถูกลบอัตโนมัติเมื่อคำสั่งค้นหาสิ้นสุดลง

ตัวอย่างที่อาจเรียกใช้ตารางชั่วคราวภายใน

เมื่อคุณเรียกใช้คำสั่งค้นหาเช่นด้านล่าง MySQL อาจสร้างตารางชั่วคราวภายในเพื่อประมวลผล

SELECT category, COUNT(*) 
FROM products 
GROUP BY category
ORDER BY COUNT(*) DESC;

ในกรณีนี้ MySQL อาจสร้าง ตารางชั่วคราวภายในเพื่อเก็บผลลัพธ์ของ GROUP BY ชั่วคราว, \ และจากนั้นใช้มันเพื่อคำนวณผลลัพธ์สุดท้าย.

สรุป

• ตารางชั่วคราวคือ ตารางชั่วคราวที่ผู้ใช้สร้าง ซึ่งจะถูกลบโดยอัตโนมัติเมื่อเซสชันสิ้นสุด.
• แตกต่างจากตารางปกติ, ไม่สามารถเข้าถึงจากเซสชันอื่นได้ .
• ตารางชั่วคราวภายในจะถูกสร้างและลบโดยอัตโนมัติโดย MySQL , และผู้ใช้ไม่สามารถควบคุมมันโดยตรง.

3. วิธีสร้างตารางชั่วคราว

คุณสามารถสร้างตารางชั่วคราวของ MySQL ได้โดยใช้คำสั่ง CREATE TEMPORARY TABLE ในส่วนนี้ เราจะอธิบายทุกอย่างตั้งแต่การสร้างพื้นฐานจนถึงการสร้างจากตารางที่มีอยู่แล้ว.

วิธีพื้นฐานในการสร้างตารางชั่วคราว

ใน MySQL คุณใช้ CREATE TEMPORARY TABLE เพื่อสร้างตารางชั่วคราว.

ไวยากรณ์พื้นฐาน

CREATE TEMPORARY TABLE table_name (
    column_name data_type constraints,
    column_name data_type constraints,
    ...
);

ตัวอย่างโค้ด

SQL ด้านล่างสร้างตารางชั่วคราวชื่อ users_temp ที่มีสามคอลัมน์: id (จำนวนเต็ม), name (สตริง), และ email (สตริง).

CREATE TEMPORARY TABLE users_temp (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

เนื่องจากตารางนี้จะถูกลบโดยอัตโนมัติเมื่อเซสชันสิ้นสุด, มันไม่ส่งผลต่อฐานข้อมูลถาวร.

สร้างตารางชั่วคราวจากตารางที่มีอยู่แล้ว

แทนการสร้างตารางชั่วคราวจากศูนย์, คุณยังสามารถ คัดลอกโครงสร้างของตารางที่มีอยู่ เพื่อสร้างได้.

การใช้ CREATE TEMPORARY TABLE ... SELECT

ใน MySQL คุณสามารถสร้างตารางชั่วคราวจากผลลัพธ์ของคำสั่ง SELECT.

ไวยากรณ์พื้นฐาน

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM existing_table_name;

ตัวอย่างโค้ด

ตัวอย่างเช่น เพื่อคัดลอกโครงสร้างข้อมูลของตาราง users และสร้างตารางชั่วคราวใหม่ users_temp คุณสามารถเขียนได้ดังนี้:

CREATE TEMPORARY TABLE users_temp
SELECT * FROM users;

ด้วยวิธีนี้ โครงสร้างคอลัมน์ของ users จะถูกคัดลอกไปยัง users_temp แต่ ข้อจำกัดเช่น PRIMARY KEY และ INDEX จะไม่ถูกคัดลอก.

หากคุณต้องการคัดลอกเฉพาะโครงสร้างตารางโดยไม่รวมข้อมูล, ให้เพิ่ม WHERE 1=0.

CREATE TEMPORARY TABLE users_temp
SELECT * FROM users WHERE 1=0;

ด้วย SQL นี้ คำจำกัดความของคอลัมน์ใน users จะถูกคัดลอก, แต่ไม่มีข้อมูลใดๆ ถูกนำเข้ามา.

หมายเหตุเมื่อสร้างตารางชั่วคราว

1. ตารางชั่วคราวมีขอบเขตตามเซสชัน

• ตารางชั่วคราวมีผลเฉพาะภายในเซสชันที่สร้างมัน.
• ไม่สามารถเข้าถึงจากการเชื่อมต่ออื่นหรือโดยผู้ใช้คนอื่น.

2. คุณสามารถสร้างได้แม้ว่าจะมีตารางปกติที่มีชื่อเดียวกันอยู่

• ตัวอย่างเช่น แม้ว่าจะมีตารางปกติชื่อ users อยู่ในฐานข้อมูล, คุณก็สามารถสร้างตารางชั่วคราวชื่อ users ได้.
• ในเซสชันนั้น ตารางชั่วคราวจะมีลำดับความสำคัญเหนือและตารางปกติจะถูกซ่อน.

3. ผลกระทบของ storage engine

• โดยค่าเริ่มต้น ตารางชั่วคราวใช้ engine MEMORY, แต่หากขนาดข้อมูลใหญ่, อาจถูกเก็บในพื้นที่ชั่วคราวของ InnoDB.
• หากต้องการระบุ engine MEMORY อย่างชัดเจน, เขียนดังนี้: CREATE TEMPORARY TABLE users_temp ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ) ENGINE=MEMORY;
• engine MEMORY มีความเร็วแต่มีขีดจำกัดขนาดข้อมูล. สำหรับชุดข้อมูลขนาดใหญ่, ควรพิจารณาใช้ InnoDB.

สรุป

• สร้างตารางชั่วคราวโดยใช้ CREATE TEMPORARY TABLE .
• คุณยังสามารถสร้างโดยคัดลอกตารางที่มีอยู่ ( SELECT * FROM ).
• engine MEMORY สามารถทำงานได้เร็ว, แต่ InnoDB มักจะดีกว่าสำหรับชุดข้อมูลขนาดใหญ่.
• ตารางชั่วคราวจัดการต่อเซสชันและ จะถูกลบโดยอัตโนมัติเมื่อเซสชันสิ้นสุด .

4. วิธีใช้ตารางชั่วคราว

MySQL temporary tables can be operated like regular tables, including INSERT, UPDATE, DELETE, and SELECT. ในส่วนนี้ เราจะอธิบายแต่ละการดำเนินการโดยละเอียด。

การแทรกข้อมูล

เพื่อเพิ่มข้อมูลลงในตารางชั่วคราว ใช้คำสั่ง INSERT INTO ปกติ。

ไวยากรณ์พื้นฐาน

INSERT INTO temp_table_name (column1, column2, ...)
VALUES (value1, value2, ...);

ตัวอย่างโค้ด

SQL ต่อไปนี้แทรกข้อมูลลงในตารางชั่วคราวชื่อ users_temp。

INSERT INTO users_temp (id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com');

คุณยังสามารถคัดลอกและแทรกข้อมูลจากตารางที่มีอยู่。

INSERT INTO users_temp (id, name, email)
SELECT id, name, email FROM users WHERE age >= 18;

SQL นี้แทรกข้อมูลสำหรับ ผู้ใช้ที่มีอายุ 18 ปีขึ้นไป จากตาราง users ลงในตารางชั่วคราว。

การอัปเดตข้อมูล

เพื่อแก้ไขข้อมูลในตารางชั่วคราว ใช้คำสั่ง UPDATE ปกติ。

ไวยากรณ์พื้นฐาน

UPDATE temp_table_name
SET column_name = new_value
WHERE condition;

ตัวอย่างโค้ด

ตัวอย่างเช่น เพื่อเปลี่ยนชื่อของผู้ใช้ที่มี id=1 ในตาราง users_temp:

UPDATE users_temp
SET name = 'Ichiro Sato'
WHERE id = 1;

การลบข้อมูล

เพื่อลบข้อมูลที่ไม่จำเป็น ใช้คำสั่ง DELETE。

ไวยากรณ์พื้นฐาน

DELETE FROM temp_table_name WHERE condition;

ตัวอย่างโค้ด

ตัวอย่างเช่น เพื่อลบแถวที่มี id=1 จาก users_temp:

DELETE FROM users_temp WHERE id = 1;

เพื่อลบ ข้อมูลทั้งหมด ในตาราง ให้ละเว้นเงื่อนไข WHERE。

DELETE FROM users_temp;

โปรดทราบว่าการใช้ DELETE จะไม่ลบตารางเอง แต่จะลบเฉพาะข้อมูล。

การเลือกข้อมูล

เพื่อดึงข้อมูลที่เก็บไว้ในตารางชั่วคราว ใช้คำสั่ง SELECT。

ไวยากรณ์พื้นฐาน

SELECT column_name FROM temp_table_name WHERE condition;

ตัวอย่างโค้ด

ตัวอย่างเช่น เพื่อดึงข้อมูลทั้งหมดจาก users_temp:

SELECT * FROM users_temp;

เพื่อดึงข้อมูลที่ตรงกับเงื่อนไขเฉพาะ ใช้เงื่อนไข WHERE。

SELECT * FROM users_temp WHERE email LIKE '%@example.com';

SQL นี้ดึงเฉพาะแถวที่ที่อยู่อีเมลมี @example.com。

ข้อควรระวังเมื่อใช้ตารางชั่วคราว

1. ข้อมูลจะถูกลบเมื่อเซสชันสิ้นสุด

• ตารางชั่วคราวถูกจัดการ ตามเซสชัน และ ข้อมูลของมันจะถูกลบเมื่อเซสชันสิ้นสุด ด้วย
• สำหรับการประมวลผลที่ใช้เวลานาน แนะนำให้สำรองข้อมูลเป็นระยะ

2. การสร้างตารางชั่วคราวที่มีชื่อเดียวกันจะทำให้เกิดข้อผิดพลาด

• หากพยายามสร้างตารางชั่วคราวที่มีชื่อเดียวกันโดยใช้ CREATE TEMPORARY TABLE จะเกิดข้อผิดพลาด
• ในฐานะ แนวทางหลีกเลี่ยงข้อผิดพลาด ให้รัน DROP TEMPORARY TABLE IF EXISTS ก่อน DROP TEMPORARY TABLE IF EXISTS users_temp; CREATE TEMPORARY TABLE users_temp (...);

3. ข้อจำกัดของ storage engine

• ตารางชั่วคราวใช้ MEMORY engine เป็นค่าเริ่มต้น แต่ชุดข้อมูลขนาดใหญ่อาจถูกเก็บในพื้นที่ชั่วคราวของ InnoDB โดยอัตโนมัติ
• สำหรับชุดข้อมูลขนาดใหญ่ แนะนำให้ใช้ตารางชั่วคราว InnoDB

สรุป

• ตารางชั่วคราวสามารถดำเนินการ INSERT, UPDATE, DELETE, และ SELECT ได้เหมือนตารางปกติ
• เมื่อเซสชันสิ้นสุด ข้อมูลในตารางชั่วคราวจะถูกลบโดยอัตโนมัติ ด้วย
• การรัน DROP TEMPORARY TABLE IF EXISTS ก่อนช่วยหลีกเลี่ยงข้อผิดพลาดจากชื่อที่ซ้ำกัน
• สำหรับชุดข้อมูลขนาดใหญ่ แนะนำให้ใช้ตารางชั่วคราว InnoDB

5. การจัดการและลบตารางชั่วคราว

ตารางชั่วคราวของ MySQL จะถูกลบโดยอัตโนมัติเมื่อเซสชันสิ้นสุด อย่างไรก็ตาม ในบางกรณี คุณอาจจำเป็นต้องลบ它们อย่างชัดเจน ในส่วนนี้ เราจะอธิบายวิธีการจัดการและลบตารางชั่วคราว。

วิธีการลบตารางชั่วคราว

เพื่อลบตารางชั่วคราวอย่างชัดเจน ใช้คำสั่ง DROP TEMPORARY TABLE。

ไวยากรณ์พื้นฐาน

DROP TEMPORARY TABLE table_name;

ตัวอย่างโค้ด

ตัวอย่างเช่น เพื่อลบตารางชั่วคราวที่ชื่อ users_temp ให้รัน:

DROP TEMPORARY TABLE users_temp;

หลังจากรัน SQL นี้ ตาราง users_temp จะถูกลบและไม่สามารถใช้ได้อีกในเซสชัน

การลบอัตโนมัติเมื่อเซสชันสิ้นสุด

ตารางชั่วคราวจะถูกลบโดยอัตโนมัติเมื่อเซสชันสิ้นสุด

วิธีการทำงานของการลบอัตโนมัติ

1. สร้างตารางชั่วคราวด้วย CREATE TEMPORARY TABLE
2. ดำเนินการกับข้อมูลของมันขณะที่เซสชันยังทำงานอยู่
3. เมื่อเซสชัน (การเชื่อมต่อ) ปิดลง ตารางชั่วคราวจะถูกลบโดยอัตโนมัติ

อย่างไรก็ตาม ควรระมัดระวังในกรณีต่อไปนี้:

• เมื่อเซสชันเปิดค้างเป็นเวลานาน → ตารางชั่วคราวที่ไม่จำเป็นอาจใช้หน่วยความจำ ดังนั้นจึงแนะนำให้รัน DROP TEMPORARY TABLE ตามความต้องการ
• เมื่อจัดการข้อมูลจำนวนมาก → เพื่อหลีกเลี่ยงความกดดันของพื้นที่จัดเก็บ จำเป็นต้องลบตารางอย่างเหมาะสม

การใช้ DROP TEMPORARY TABLE IF EXISTS

เพื่อหลีกเลี่ยงข้อผิดพลาดเมื่อพยายามลบตารางที่อาจไม่มีอยู่ คุณสามารถใช้ IF EXISTS

ไวยากรณ์พื้นฐาน

DROP TEMPORARY TABLE IF EXISTS table_name;

ตัวอย่างโค้ด

DROP TEMPORARY TABLE IF EXISTS users_temp;

SQL นี้จะลบ users_temp หากมีอยู่; หากไม่มีอยู่จะไม่เกิดข้อผิดพลาด.

ข้อผิดพลาดทั่วไปและวิธีแก้

ข้อผิดพลาด 1: “ไม่พบตาราง”

เมื่อเกิดขึ้น:

• เมื่อคุณพยายามลบตารางที่ไม่มีอยู่โดยใช้ DROP TEMPORARY TABLE
• เนื่องจากตารางชั่วคราวมีขอบเขตของเซสชัน, คุณไม่สามารถลบมันจากเซสชันอื่นได้

วิธีแก้:

• เพิ่ม IF EXISTS เพื่อหลีกเลี่ยงข้อผิดพลาด

  DROP TEMPORARY TABLE IF EXISTS users_temp;
  

• ลบมันภายในเซสชันที่ถูกต้อง

ข้อผิดพลาด 2: “ตารางมีอยู่แล้ว”

เมื่อเกิดขึ้น:

• เมื่อคุณพยายามสร้างตารางชั่วคราวด้วยชื่อที่มีอยู่แล้ว

วิธีแก้:

• รัน DROP TEMPORARY TABLE IF EXISTS ล่วงหน้า

  DROP TEMPORARY TABLE IF EXISTS users_temp;
  CREATE TEMPORARY TABLE users_temp (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      email VARCHAR(100)
  );
  

แนวทางปฏิบัติที่ดีที่สุดสำหรับการจัดการตารางชั่วคราว

1. ลบตารางเหล่านั้นอย่างชัดเจนเมื่อคุณไม่ต้องการใช้แล้ว

• รัน DROP TEMPORARY TABLE ตามความจำเป็นเพื่อปลดปล่อยตารางที่ไม่จำเป็น.

1. ใช้ IF EXISTS เพื่อหลีกเลี่ยงข้อผิดพลาด

• DROP TEMPORARY TABLE IF EXISTS ป้องกันข้อผิดพลาดเมื่อพยายามลบตารางที่ไม่มีอยู่.

1. ระมัดระวังการจัดการเซสชัน

• เซสชันที่เปิดค้างเป็นเวลานานอาจทำให้ตารางชั่วคราวใช้หน่วยความจำ ดังนั้นควรลบตามความเหมาะสม.

1. เข้าใจผลกระทบของ storage engine

• engine MEMORY มีความเร็วสูงแต่มีขีดจำกัดขนาดข้อมูล.
• หากคุณใช้ InnoDB จำเป็นต้องคำนึงถึงการใช้พื้นที่ดิสก์.

สรุป

• คุณสามารถลบตารางชั่วคราวอย่างชัดเจนโดยใช้ DROP TEMPORARY TABLE .
• พวกมันจะถูกลบโดยอัตโนมัติเมื่อเซสชันสิ้นสุด แต่สำหรับเซสชันที่เปิดค้างเป็นเวลานาน แนะนำให้ทำความสะอาดด้วยตนเอง.
• DROP TEMPORARY TABLE IF EXISTS ช่วยป้องกันข้อผิดพลาดเมื่อทำการลบ.
• การรู้วิธีจัดการกับข้อผิดพลาด “ไม่พบตาราง” และ “ตารางมีอยู่แล้ว” เป็นประโยชน์.

6. กรณีการใช้งานจริงของตารางชั่วคราว

ตารางชั่วคราวของ MySQL ใช้เพื่อทำให้การจัดเก็บและประมวลผลข้อมูลชั่วคราวมีประสิทธิภาพมากขึ้น ในส่วนนี้ เราจะแนะนำสถานการณ์ทั่วไปที่ตารางชั่วคราวมีประโยชน์และอธิบายรายละเอียดการใช้งาน

1. ใช้เป็นตารางกลางสำหรับการสรุปผล

ในการวิเคราะห์ข้อมูลและการสร้างรายงาน การประมวลผลชุดข้อมูลขนาดใหญ่โดยตรงอาจทำให้การดำเนินการคิวรีช้าลง โดยการใช้ตารางชั่วคราว คุณสามารถจัดระเบียบข้อมูลก่อนแล้วจึงประมวลผล ซึ่งช่วยเพิ่มประสิทธิภาพ

สถานการณ์

• ตาราง sales มีข้อมูลการขายของหนึ่งปี
• คุณต้องการคำนวณยอดขายรวมต่อเดือนและทำการวิเคราะห์ต่อไป

ตัวอย่างการนำไปใช้

CREATE TEMPORARY TABLE monthly_sales (
    month_year DATE,
    total_sales DECIMAL(10,2)
);

INSERT INTO monthly_sales (month_year, total_sales)
SELECT DATE_FORMAT(sale_date, '%Y-%m-01') AS month_year, SUM(amount) 
FROM sales
GROUP BY month_year;

SELECT * FROM monthly_sales;

2. Keeping temporary data for batch processing

Temporary tables are also useful for batch processing (bulk operations). For example, you can filter data by certain conditions and store only the target data in a temporary table to operate efficiently.

Scenario

• From the users table, you want to email only users who have logged in within the last year .
• You store the target data in a temporary table first, then process it sequentially.

Example implementation

CREATE TEMPORARY TABLE active_users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

INSERT INTO active_users
SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL 1 YEAR;

SELECT * FROM active_users;

3. Simplifying complex queries

Running complex queries directly can reduce performance and hurt readability. By using temporary tables, you can reduce subqueries and keep SQL simpler.

Scenario

• You want to get the top 10 best-selling products from the orders table.
• You want to avoid using subqueries by leveraging a temporary table.

Example implementation

CREATE TEMPORARY TABLE top_products AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

SELECT * FROM top_products;

4. Temporary operations without needing rollback

Temporary tables are managed per session and are not affected by transactions. This makes them suitable for managing temporary data where rollback is not desired.

Scenario

• During a transaction, you want to keep temporary calculation results .
• But you want to avoid temporary data being rolled back on errors.

Example implementation

START TRANSACTION;

CREATE TEMPORARY TABLE temp_results (
    user_id INT,
    score INT
);

INSERT INTO temp_results
SELECT user_id, SUM(points) FROM game_scores GROUP BY user_id;

-- Commit the transaction
COMMIT;

SELECT * FROM temp_results;

Summary

• Temporary tables can be used for aggregation, batch processing, and simplifying queries across many scenarios.
• Using them as an intermediate table can improve performance and help organize data.
• For batch processing , pre-extracting only target data helps avoid unnecessary work.
• For simplifying complex queries , reducing subqueries improves readability.
• Because they are not affected by transactions , they can be used for temporary data where rollback is not needed.

7. Alternatives and Limitations of Temporary Tables

MySQL temporary tables are useful, but they have some limitations. In some cases, using alternatives such as views or subqueries can provide more efficient data processing. In this section, we explain the main limitations of temporary tables and alternative approaches to work around them.

Main limitations of temporary tables

Temporary tables have several limitations that regular tables do not. Understanding these helps you choose appropriate use cases.

1. Session-scoped

• A temporary table is valid only within the session where it was created, and cannot be accessed by other connections or users .
• Even if a regular table with the same name exists, the temporary table takes precedence within the session (the regular table is not accessible).

2. The schema is not retained

• Regular tables can be inspected with SHOW CREATE TABLE , but a temporary table disappears when the session ends , so its schema is not retained.

3. Index limitations

• หากคุณไม่ได้ระบุ PRIMARY KEY หรือ INDEX ใน CREATE TEMPORARY TABLE พวกมันจะไม่ถูกสร้างโดยอัตโนมัติ
• หากคุณต้องการดัชนีบนตารางชั่วคราว คุณต้องสร้างมันด้วยตนเอง

4. เอนจินจัดเก็บข้อมูลเริ่มต้นคือ MEMORY

• ด้วยเอนจิน MEMORY ขนาดข้อมูลขนาดใหญ่อาจทำให้สลับไปยังดิสก์และลดประสิทธิภาพ
• หากคุณระบุ InnoDB มันสามารถจัดการข้อมูลขนาดใหญ่ได้ แต่การใช้ดิสก์จะเพิ่มขึ้น

5. ไม่ได้รับผลกระทบจากการทำธุรกรรม

• ตารางชั่วคราว ไม่ได้รับผลกระทบจาก ROLLBACK
• ดังนั้นจึงไม่เหมาะสำหรับการประมวลผลที่ต้องการความสอดคล้องของธุรกรรมอย่างเคร่งครัด

ทางเลือกแทนตารางชั่วคราว

เพื่อหลีกเลี่ยงข้อจำกัดเหล่านี้ คุณสามารถใช้ view หรือ subquery แทนตารางชั่วคราวเพื่อการจัดการข้อมูลที่ยืดหยุ่นมากขึ้น

1. ใช้ view

View สามารถใช้ได้คล้ายกับตารางชั่วคราวสำหรับการอ้างอิงข้อมูลชั่วคราว View ทำหน้าที่เป็น ตารางเสมือนและไม่ต้องการการจัดเก็บข้อมูลชั่วคราว ซึ่งช่วยหลีกเลี่ยงข้อจำกัดด้านการจัดเก็บ

สร้าง view

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL 1 YEAR;

ใช้ view

SELECT * FROM active_users;

ข้อดีของการใช้ view

✅ ไม่มีการใช้พื้นที่จัดเก็บ (ข้อมูลถูกอ้างอิงโดยตรง ไม่จำเป็นต้องจัดเก็บชั่วคราว)
✅ ไม่ขึ้นกับเซสชัน (พร้อมใช้งานสำหรับผู้ใช้และการเชื่อมต่ออื่น ๆ)
✅ สามารถเก็บสคีม่าไว้ได้ (คุณสามารถตรวจสอบคำนิยามด้วย SHOW CREATE VIEW)

ข้อเสียของการใช้ view

❌ อัปเดตยาก (การ INSERT หรือ UPDATE โดยตรงบน view ถูกจำกัด)
❌ ประสิทธิภาพอาจลดลงสำหรับชุดข้อมูลขนาดใหญ่

2. ใช้ subquery

คุณยังสามารถใช้ subquery เพื่อประมวลผลข้อมูลชั่วคราวโดยไม่ต้องสร้างตารางชั่วคราว

การใช้ตารางชั่วคราว

CREATE TEMPORARY TABLE top_products AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

SELECT * FROM top_products;

การใช้ subquery

SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

ข้อดีของการใช้ subquery

✅ ประสิทธิภาพดีกว่าเพราะคุณไม่ต้องสร้างตารางชั่วคราว
✅ ไม่มีการใช้พื้นที่จัดเก็บ
✅ ไม่ขึ้นกับเซสชันและสามารถดำเนินการได้ทุกเวลา

ข้อเสียของการใช้ subquery

❌ ความอ่านง่ายอาจลดลงสำหรับคิวรีที่ซับซ้อน
❌ ยากต่อการนำข้อมูลกลับมาใช้ใหม่ (คุณอาจต้องอ้างอิงข้อมูลเดียวกันหลายครั้ง)

3. ใช้ CTE (คลอส WITH)

ใน MySQL 8.0 และรุ่นต่อไป คุณสามารถใช้ CTE (Common Table Expression) เพื่อจัดการข้อมูลชั่วคราวโดยไม่ต้องสร้างตารางชั่วคราว

ตัวอย่าง CTE

WITH top_products AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
    ORDER BY total_sales DESC
    LIMIT 10
)
SELECT * FROM top_products;

ข้อดีของการใช้ CTE

✅ เพิ่มความอ่านง่าย (มักอ่านง่ายกว่าการใช้ subquery)
✅ สามารถเพิ่มประสิทธิภาพ (การประมวลผลแบบชั่วคราวโดยไม่ต้องสร้างตารางชั่วคราว)

ข้อเสียของการใช้ CTE

❌ ไม่รองรับใน MySQL 5.x (รองรับเฉพาะ MySQL 8.0 ขึ้นไป)

สรุป

8. คำถามที่พบบ่อย

ต่อไปนี้เป็นคำถามที่พบบ่อยเกี่ยวกับตารางชั่วคราวของ MySQL เราหวังว่าข้อมูลนี้จะช่วยทำให้คุณเข้าใจพฤติกรรมและข้อจำกัดของมันได้ชัดเจนขึ้น

1. ตารางชั่วคราวสามารถอ้างอิงจากเซสชันอื่นได้หรือไม่?

ไม่ได้
ตารางชั่วคราว ใช้ได้เฉพาะภายในเซสชันที่สร้างขึ้น เซสชันอื่นไม่สามารถเข้าถึงได้ แม้ว่าผู้ใช้คนอื่นจะสร้างตารางชั่วคราวที่มีชื่อเดียวกัน แต่แต่ละเซสชันจะถือว่าเป็นตารางที่แยกจากกัน

2. สิทธิ์ใดที่จำเป็นสำหรับการสร้างตารางชั่วคราว?

เพื่อสร้างตารางชั่วคราว คุณต้องมีสิทธิ์ CREATE TEMPORARY TABLES บนฐานข้อมูล.
เพื่อมอบสิทธิ์นี้ให้ผู้ใช้ ให้รัน SQL ต่อไปนี้:

GRANT CREATE TEMPORARY TABLES ON database_name.* TO 'user_name'@'host';

คุณยังสามารถตรวจสอบสิทธิ์ปัจจุบันโดยใช้ SHOW GRANTS.

SHOW GRANTS FOR 'user_name'@'host';

3. ตารางชั่วคราวมีผลต่อการใช้ดิสก์หรือไม่?

ใช่, พวกมันสามารถทำได้.
โดยค่าเริ่มต้น ตารางชั่วคราวของ MySQL จะใช้เอนจิน MEMORY แต่ เมื่อขนาดข้อมูลเกินเกณฑ์ที่กำหนด พวกมันจะถูกเก็บในพื้นที่ชั่วคราวของ InnoDB.

เมื่อทำงานกับชุดข้อมูลขนาดใหญ่ ตารางชั่วคราวอาจใช้พื้นที่ดิสก์ ดังนั้น จึงแนะนำให้ลบตารางเหล่านี้อย่างชัดเจนเมื่อไม่ต้องการใช้งานต่อ.

DROP TEMPORARY TABLE IF EXISTS table_name;

เพื่อลดผลกระทบต่อดิสก์ หากคุณคาดว่าจะมีข้อมูลจำนวนมาก ควรพิจารณาสร้างตารางชั่วคราวด้วย InnoDB แทน MEMORY.

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type
) ENGINE=InnoDB;

4. ความแตกต่างระหว่างตารางชั่วคราวกับตารางชั่วคราวภายในคืออะไร?

5. ตารางชั่วคราวสามารถแชร์ระหว่างเธรดได้หรือไม่?

ไม่, พวกมันไม่สามารถทำได้.
ตารางชั่วคราว ใช้ได้เฉพาะภายในเธรด (เซสชัน) ที่สร้างมัน และไม่สามารถเข้าถึงจากเธรดหรือกระบวนการอื่นได้.

หากคุณต้องการแชร์ข้อมูลระหว่างเซสชัน/เธรด คุณต้องสร้างตารางปกติแทน.

CREATE TABLE shared_temp_table (
    id INT PRIMARY KEY,
    data VARCHAR(255)
);

6. ตารางชั่วคราวสามารถทำให้ประสิทธิภาพลดลงได้หรือไม่?

ใช่, ในบางกรณี.
โดยเฉพาะอย่างยิ่ง ให้ระวังในสถานการณ์ต่อไปนี้:

• เมื่อปริมาณข้อมูลมีขนาดใหญ่เกินไป
• เอนจิน MEMORY มีขีดจำกัดขนาด; หากเกินขีดจำกัด ข้อมูลอาจสลับไปยัง InnoDB ซึ่งอาจ ทำให้ประสิทธิภาพลดลง .
• การบรรเทา: หากคาดว่าจะเกินขีดจำกัดของ MEMORY ให้สร้างตารางด้วย InnoDB ตั้งแต่แรก.
• เมื่อไม่ได้ตั้งดัชนีที่เหมาะสม
• ตารางที่สร้างด้วย CREATE TEMPORARY TABLE ... SELECT จะไม่คัดลอกดัชนี ทำให้การค้นหาช้าลง.
• การบรรเทา: เพิ่มดัชนีตามต้องการโดยใช้ ALTER TABLE .

  ALTER TABLE temp_table_name ADD INDEX (column_name);
  

7. ฉันจะปรับปรุงประสิทธิภาพของตารางชั่วคราวได้อย่างไร?

เพื่อปรับปรุงประสิทธิภาพของตารางชั่วคราว วิธีต่อไปนี้มีประสิทธิผล:

✅ ใช้เอนจิน MEMORY (เร็วสำหรับชุดข้อมูลขนาดเล็ก)

CREATE TEMPORARY TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;

✅ เลือกเฉพาะคอลัมน์ที่ต้องการ (ละเว้นคอลัมน์ที่ไม่จำเป็น)

CREATE TEMPORARY TABLE users_temp AS
SELECT id, name FROM users;

✅ เพิ่มดัชนีที่เหมาะสม (เร่งการค้นหา)

ALTER TABLE users_temp ADD INDEX (name);

✅ ลบตารางทันทีเมื่อไม่ต้องการใช้งานต่อ (ปลดปล่อยหน่วยความจำ)

DROP TEMPORARY TABLE IF EXISTS users_temp;

สรุป

• ตารางชั่วคราวไม่สามารถอ้างอิงจากเซสชันหรือเธรดอื่นได้
• คุณต้องมีสิทธิ์ CREATE TEMPORARY TABLES เพื่อสร้างตารางเหล่านี้
• หากข้อมูลมีขนาดใหญ่เกินไป MySQL อาจสลับจาก MEMORY ไปเป็น InnoDB ซึ่งอาจทำให้ประสิทธิภาพลดลง
• การเพิ่มดัชนีที่เหมาะสมสามารถเร่งการสืบค้น
• แนะนำให้ลบตารางชั่วคราวด้วย DROP TEMPORARY TABLE เมื่อไม่ต้องการใช้งานต่อ

นี่คือคำอธิบายโดยละเอียดของตารางชั่วคราวใน MySQL ตั้งแต่แนวคิดพื้นฐานจนถึงการใช้งาน, ข้อจำกัด, ทางเลือก, และคำถามที่พบบ่อย
โดยการใช้ตารางชั่วคราวอย่างเหมาะสม คุณสามารถปรับปรุงประสิทธิภาพของการประมวลผลข้อมูลได้อย่างมีนัยสำคัญ