Bảng tạm MySQL: Tạo, Sử dụng, Xóa + Các thực tiễn tốt nhất (kèm ví dụ SQL)

1. (Thân thiện với người mới) Bảng tạm thời MySQL là gì? Sự khác biệt so với bảng thường

Giới thiệu

Khi quản lý dữ liệu với MySQL, đôi khi bạn cần lưu trữ dữ liệu tạm thời. Ví dụ, khi xử lý một bộ dữ liệu lớn, bạn có thể muốn lưu các kết quả trung gian trong quá trình làm việc. Trong những trường hợp đó, một bảng tạm thời (Temporary Table) rất hữu ích.

Trong bài viết này, chúng tôi sẽ giải thích cơ chế cơ bản của các bảng tạm thời MySQL và cách chúng khác biệt so với các bảng thường.

1-1. Bảng tạm thời là gì?

Bảng tạm thời là một bảng đặc biệt chỉ tồn tại trong suốt một phiên làm việc của cơ sở dữ liệu (kết nối).
Không giống như bảng thường, nó tự động bị xóa khi phiên kết thúc, làm cho nó trở thành lựa chọn lý tưởng để lưu trữ dữ liệu tạm thời.

Các tính năng chính của bảng tạm thời

• Cô lập theo phiên Một bảng tạm thời chỉ có thể được truy cập trong phiên mà nó được tạo ra. Các phiên khác không thể tham chiếu tới nó.
• Tự động bị xóa khi phiên kết thúc Các bảng tạm thời tự động biến mất khi phiên kết thúc, ngay cả khi bạn không thực hiện lệnh DROP một cách rõ ràng.
• Bạn có thể tạo bảng tạm thời với cùng một tên Không giống như bảng thường, có thể tạo các bảng tạm thời có cùng tên trong các phiên khác nhau .

1-2. Sự khác biệt so với bảng thường

Bảng tạm thời và bảng thường khác nhau ở các khía cạnh sau.

Bạn nên sử dụng cái nào?

• Nếu bạn chỉ cần dữ liệu tạm thời và có thể loại bỏ sau khi xử lý → Bảng tạm thời
• Nếu bạn muốn giữ dữ liệu lâu dài và tái sử dụng sau này → Bảng thường

Ví dụ, các bảng tạm thời rất hữu ích cho các nhiệm vụ như phân tích dữ liệu quy mô lớn hoặc tổng hợp tạm thời.

1-3. Khi nào bạn cần một bảng tạm thời

Các bảng tạm thời MySQL đặc biệt hữu ích trong các tình huống sau.

1) Cải thiện hiệu suất truy vấn

Ví dụ, khi thực hiện các phép JOIN phức tạp, bạn có thể giảm thời gian xử lý bằng cách tạo một bảng tạm thời để lưu trữ dữ liệu trung gian trước.

Ví dụ: Giảm chi phí JOIN

CREATE TEMPORARY TABLE temp_users AS
SELECT id, name FROM users WHERE status = 'active';

Bằng cách lưu dữ liệu mục tiêu vào một bảng tạm thời trước và sau đó thực hiện JOIN, hiệu suất có thể được cải thiện.

2) Lưu trữ dữ liệu tạm thời

Các bảng tạm thời cũng hữu ích khi một ứng dụng cần quản lý dữ liệu tạm thời.
Ví dụ, bạn có thể lưu dữ liệu người dùng đã tìm kiếm vào một bảng tạm thời và để nó bị xóa sau khi phiên kết thúc.

3) Bảng trung gian cho xử lý batch

Khi xử lý một lượng lớn dữ liệu, việc sử dụng các bảng tạm thời làm bảng trung gian có thể cải thiện độ ổn định của quá trình.

1-4. Những hạn chế của bảng tạm thời

Bảng tạm thời rất tiện lợi, nhưng cũng có một số hạn chế.

1) Tự động bị xóa khi phiên kết thúc

Vì các bảng tạm thời tự động bị xóa khi phiên kết thúc, chúng không phù hợp để lưu trữ dữ liệu lâu dài.

2) Không thể truy cập từ các phiên khác

Các bảng tạm thời chỉ có thể được sử dụng trong phiên mà chúng được tạo ra, do đó không thể chia sẻ với người dùng hoặc quy trình khác.

3) Xung đột tiềm năng với bảng thường có cùng tên

Nếu một bảng thường cùng tên đã tồn tại, việc tạo một bảng tạm thời với tên đó sẽ làm cho bảng thường tạm thời không hiển thị, vì vậy hãy cẩn thận.

CREATE TEMPORARY TABLE users (id INT, name VARCHAR(255));
SELECT * FROM users; -- This query references the temporary table data

Như đã chỉ ra ở trên, một khi bảng tạm thời được tạo, bạn không thể truy cập vào bảng thường cùng tên cho đến khi bảng tạm thời bị xóa. Hãy chọn tên bảng một cách cẩn thận.

Tóm tắt

Các bảng tạm thời MySQL là một tính năng tiện lợi cho việc lưu trữ dữ liệu tạm thời và tối ưu hoá truy vấn.
Bằng cách hiểu cách chúng khác biệt so với các bảng thường và sử dụng chúng một cách thích hợp, bạn có thể xử lý dữ liệu hiệu quả hơn.

✔ Tóm tắt nhanh

• Bảng tạm thời được tự động xóa khi phiên làm việc kết thúc
• Khác với bảng thường, chúng được cô lập theo từng phiên
• Lý tưởng cho việc lưu trữ tạm thời và cải thiện hiệu năng truy vấn
• Không phù hợp cho lưu trữ lâu dài vì dữ liệu sẽ biến mất khi phiên kết thúc
• Không thể truy cập từ các phiên khác, và có thể gây xung đột với các bảng thường có cùng tên

2. (Với Mã Mẫu) Cách Tạo Bảng Tạm Thời trong MySQL

Giới thiệu

Trong phần trước, chúng tôi đã giải thích khái niệm cơ bản của bảng tạm thời và cách chúng khác với bảng thường.
Trong phần này, chúng tôi sẽ hướng dẫn cách tạo một bảng tạm thời và cách làm việc với dữ liệu trong đó.

Việc tạo một bảng tạm thời rất đơn giản, nhưng nếu bạn không sử dụng cú pháp đúng, nó có thể không hoạt động như mong đợi. Phần này giải thích chi tiết cú pháp cơ bản, tạo từ một bảng hiện có, và cách xác nhận các bảng tạm thời.

2-1. Cú pháp Cơ bản cho Bảng Tạm Thời

Để tạo một bảng tạm thời, sử dụng câu lệnh CREATE TEMPORARY TABLE.

Cú pháp cơ bản

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

Cú pháp gần như giống với CREATE TABLE, nhưng thêm TEMPORARY sẽ biến nó thành một bảng tạm thời.

Ví dụ: Lưu thông tin người dùng trong một bảng tạm thời

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Bảng temp_users này chỉ hợp lệ trong phiên hiện tại và sẽ tự động bị xóa khi phiên kết thúc.

2-2. Tạo Bảng Tạm Thời Dựa trên Dữ liệu Từ Bảng Hiện Có

Bạn cũng có thể tạo một bảng tạm thời dựa trên dữ liệu từ một bảng hiện có.

Cú pháp

CREATE TEMPORARY TABLE temp_table_name AS
SELECT * FROM existing_table WHERE condition;

Ví dụ: Lưu chỉ những người dùng đang hoạt động trong một bảng tạm thời

CREATE TEMPORARY TABLE active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

Phương pháp này trích xuất chỉ những người dùng có status = 'active' từ bảng users và lưu chúng vào một bảng tạm thời mới có tên active_users.

Các điểm chính

• Sao chép dữ liệu từ một bảng hiện có nguyên trạng
• Kiểu dữ liệu của các cột được đặt tự động
• Các chỉ mục không được sao chép, vì vậy nếu cần hãy thêm chúng một cách rõ ràng

2-3. Cách Kiểm Tra Dữ Liệu Bảng Tạm Thời

Liệt kê các bảng

SHOW TABLES;

Tuy nhiên, một bảng tạm thời sẽ không xuất hiện trong danh sách khi sử dụng SHOW TABLES thông thường.

Kiểm tra cấu trúc của một bảng tạm thời

DESC temp_users;

hoặc

SHOW CREATE TABLE temp_users;

Điều này cho phép bạn kiểm tra cấu trúc cột và các ràng buộc của bảng tạm thời.

2-4. Chèn Dữ Liệu vào Bảng Tạm Thời

Chèn dữ liệu vào một bảng tạm thời cũng giống như với bảng thường.

Chèn dữ liệu

INSERT INTO temp_users (name, email) VALUES 
('Taro Tanaka', 'tanaka@example.com'),
('Hanako Sato', 'sato@example.com');

Kiểm tra dữ liệu

SELECT * FROM temp_users;

Điều này xác nhận rằng dữ liệu đã được lưu trong bảng tạm thời.

2-5. Lưu Ý Khi Tạo Bảng Tạm Thời

1) Cẩn thận với xung đột tên bảng

Nếu bạn tạo một bảng tạm thời có cùng tên với một bảng thường, bảng tạm thời sẽ được ưu tiên, và bảng thường sẽ tạm thời không thể truy cập.

CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50));
SELECT * FROM users; -- This returns data from the temporary table

Vì lý do này, nên sử dụng tiền tố như “temp_” cho tên các bảng tạm thời.

2) Các chỉ mục không được kế thừa tự động

Khi bạn sao chép dữ liệu từ một bảng hiện có, các chỉ mục sẽ không được áp dụng tự động.
Nếu cần, bạn phải thêm các chỉ mục một cách rõ ràng.

ALTER TABLE temp_users ADD INDEX (email);

3) Bạn cần quyền để tạo bảng tạm

Để tạo một bảng tạm, bạn cần quyền CREATE TEMPORARY TABLES.

GRANT CREATE TEMPORARY TABLES ON database_name.* TO 'user'@'localhost';

Nếu không có quyền này, bạn không thể tạo bảng tạm.

Tóm tắt

Trong phần này, chúng tôi đã giải thích cách tạo bảng tạm.

✔ Tóm tắt nhanh

• Tạo một bảng tạm bằng CREATE TEMPORARY TABLE
• Bạn cũng có thể tạo một bảng tạm bằng cách sao chép dữ liệu từ một bảng hiện có
• Tự động bị xóa khi phiên kết thúc
• Các chỉ mục không được áp dụng tự động—hãy cẩn thận
• Sử dụng tiền tố như “temp_” để tránh xung đột tên
• Bạn cần quyền thích hợp (CREATE TEMPORARY TABLES)

3. Cách thao tác dữ liệu trong bảng tạm MySQL (INSERT, UPDATE, DELETE)

Giới thiệu

Trong phần trước, chúng tôi đã giải thích cách tạo một bảng tạm trong MySQL.
Trong phần này, chúng tôi sẽ giải thích cách chèn, cập nhật và xóa dữ liệu trong bảng tạm bằng các lệnh SQL cụ thể.

Bảng tạm hỗ trợ các thao tác dữ liệu giống như các bảng thường,
nhưng có một số lưu ý quan trọng cần ghi nhớ, chúng tôi cũng sẽ đề cập tới.

3-1. Chèn dữ liệu vào bảng tạm (INSERT)

Để thêm dữ liệu vào bảng tạm, sử dụng câu lệnh INSERT INTO, giống như với bảng thường.

Cú pháp cơ bản

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

Ví dụ: Thêm thông tin người dùng

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO temp_users (name, email) 
VALUES 
('Taro Tanaka', 'tanaka@example.com'),
('Hanako Sato', 'sato@example.com');

Chèn dữ liệu hiện có bằng INSERT…SELECT

Bạn cũng có thể lấy dữ liệu từ một bảng hiện có và chèn nó vào bảng tạm.

INSERT INTO temp_users (id, name, email)
SELECT id, name, email FROM users WHERE status = 'active';

Phương pháp này cho phép bạn lưu chỉ những người dùng đang hoạt động trong một bảng tạm.

3-2. Cập nhật dữ liệu trong bảng tạm (UPDATE)

Để thay đổi dữ liệu trong bảng tạm, sử dụng câu lệnh UPDATE.

Cú pháp cơ bản

UPDATE temp_table_name 
SET column_name = value
WHERE condition;

Ví dụ: Cập nhật tên người dùng

UPDATE temp_users 
SET name = 'Ichiro Tanaka'
WHERE email = 'tanaka@example.com';

Cập nhật hàng loạt cho các hàng thỏa mãn điều kiện

Ví dụ, nếu bạn muốn thay đổi địa chỉ email dưới một miền cụ thể thành example.jp, bạn có thể viết:

UPDATE temp_users 
SET email = REPLACE(email, 'example.com', 'example.jp')
WHERE email LIKE '%@example.com';

3-3. Xóa dữ liệu khỏi bảng tạm (DELETE)

Để xóa dữ liệu, sử dụng câu lệnh DELETE.

Cú pháp cơ bản

DELETE FROM temp_table_name WHERE condition;

Ví dụ: Xóa dữ liệu của một người dùng cụ thể

DELETE FROM temp_users WHERE email = 'tanaka@example.com';

Xóa tất cả các hàng (khác với TRUNCATE)

Nếu bạn muốn xóa tất cả các hàng, bạn có thể viết:

DELETE FROM temp_users;

Ngược lại, đối với các bảng thường, bạn thường có thể xóa tất cả các hàng nhanh hơn bằng cách sử dụng TRUNCATE TABLE. Tuy nhiên, bạn không thể sử dụng TRUNCATE trên một bảng tạm trong MySQL.

TRUNCATE TABLE temp_users; -- Error (cannot be used on temporary tables in MySQL)

Do đó, để xóa tất cả các hàng khỏi một bảng tạm, bạn phải sử dụng DELETE.

3-4. Lưu ý khi thao tác dữ liệu trong bảng tạm

1) Dữ liệu biến mất khi phiên kết thúc

Một bảng tạm tự động bị xóa khi phiên (kết nối) kết thúc,
do đó không phù hợp cho các trường hợp cần lưu trữ dữ liệu lâu dài.

2) Không thể truy cập từ các phiên khác

Một bảng tạm chỉ có hiệu lực trong phiên đã tạo ra nó và không thể được truy cập từ các phiên khác.

SELECT * FROM temp_users;

Nếu bạn chạy câu lệnh SQL này trong một phiên khác, bạn sẽ nhận được lỗi “Table ‘temp_users’ doesn’t exist”.

3) Các chỉ mục trên bảng tạm không được áp dụng tự động

Nếu bạn tạo một bảng bằng cách sử dụng CREATE TEMPORARY TABLE ... AS SELECT ...,
các chỉ mục từ bảng gốc sẽ không được kế thừa. Nếu cần, hãy thêm các chỉ mục một cách thủ công bằng cách sử dụng ALTER TABLE.

ALTER TABLE temp_users ADD INDEX (email);

Tóm tắt

Trong phần này, chúng tôi đã đề cập đến việc thao tác dữ liệu (INSERT, UPDATE, DELETE) cho các bảng tạm.

✔ Tóm tắt nhanh

• Sử dụng INSERT để thêm dữ liệu ( INSERT INTO ... VALUES / INSERT INTO ... SELECT )
• Sử dụng UPDATE để sửa đổi dữ liệu (cập nhật có điều kiện và sử dụng REPLACE() )
• Sử dụng DELETE để xóa dữ liệu ( DELETE FROM ... WHERE ; TRUNCATE không được phép)
• Bảng tạm sẽ bị xóa khi phiên kết thúc
• Không thể truy cập từ các phiên khác
• Các chỉ mục không được kế thừa tự động; hãy thêm chúng một cách thủ công nếu cần

4. Các bảng tạm MySQL có tự động bị xóa không? Cách xóa chúng thủ công

Giới thiệu

Khác với các bảng thường, một bảng tạm MySQL (Temporary Table) được tự động xóa khi phiên kết thúc. Tuy nhiên, có những trường hợp bạn có thể cần phải xóa nó thủ công.

Trong phần này, chúng tôi giải thích cách hoạt động của việc tự động xóa và cách xóa các bảng tạm thủ công một cách chi tiết.

4-1. Cách hoạt động của việc tự động xóa cho các bảng tạm

1) Tự động bị xóa khi phiên kết thúc

Một bảng tạm MySQL sẽ tự động bị xóa khi phiên (kết nối cơ sở dữ liệu) tạo ra nó kết thúc.
Vì vậy, bạn thường không cần phải xóa nó thủ công.

Ví dụ: Tự động xóa khi phiên kết thúc

-- Create a temporary table in a new session
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- Insert data
INSERT INTO temp_users (name, email) VALUES ('Taro Tanaka', 'tanaka@example.com');

-- End the session (disconnect the MySQL client)
EXIT;

Tại thời điểm này, bảng tạm temp_users đã được tự động xóa.

2) Bảng tạm vẫn tồn tại cho đến khi phiên kết thúc

Vì các bảng tạm được quản lý theo từng phiên, chúng sẽ không bị xóa miễn là phiên vẫn mở.

SELECT * FROM temp_users; -- Data can be retrieved if the session is still active

Nói cách khác, bảng tạm sẽ ở trong bộ nhớ cho đến khi bạn đóng client MySQL (hoặc chương trình ngắt kết nối).

4-2. Cách xóa một bảng tạm thủ công

Bạn cũng có thể xóa các bảng tạm thủ công.
Trong MySQL, sử dụng DROP TEMPORARY TABLE để xóa một bảng tạm.

1) Sử dụng DROP TEMPORARY TABLE

DROP TEMPORARY TABLE temp_users;

Điều này sẽ ngay lập tức xóa bảng tạm temp_users.

2) Thêm IF EXISTS để tránh lỗi

Nếu bảng không tồn tại, bạn có thể sử dụng IF EXISTS để tránh lỗi.

DROP TEMPORARY TABLE IF EXISTS temp_users;

Cú pháp này ngăn ngừa lỗi ngay cả khi bảng không tồn tại.

3) Khác với DROP TABLE thông thường

Nếu bạn cố gắng xóa một bảng tạm bằng cách sử dụng DROP TABLE thông thường, bạn có thể gặp lỗi như sau:

DROP TABLE temp_users;

Lỗi:

ERROR 1051 (42S02): Unknown table 'temp_users'

Vì MySQL quản lý các bảng thường và bảng tạm riêng biệt, bạn phải sử dụng DROP TEMPORARY TABLE khi xóa một bảng tạm.

4-3. Cách xác nhận một bảng tạm đã bị xóa

1) Bạn không thể xác nhận bằng SHOW TABLES

Một bảng tạm sẽ không xuất hiện trong kết quả của SHOW TABLES.

SHOW TABLES;

→ Bảng tạm không được liệt kê

2) Xác nhận bằng INFORMATION_SCHEMA

Bạn có thể kiểm tra xem một bảng tạm có tồn tại hay không bằng cách truy vấn INFORMATION_SCHEMA.

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'temp_users';

Nếu truy vấn này trả về kết quả, nó cho thấy bảng tạm tồn tại.

4-4. Ghi chú khi xóa bảng tạm

1) Bảng tạm khác nhau theo phiên làm việc

Bạn có thể tạo các bảng tạm có cùng tên trong nhiều phiên làm việc.
Bạn không thể xóa một bảng tạm được tạo bởi phiên khác.

Ví dụ

-- Created in session A
CREATE TEMPORARY TABLE temp_data (id INT);

-- Attempt to drop in session B
DROP TEMPORARY TABLE temp_data;

Xảy ra lỗi:

ERROR 1051 (42S02): Unknown table 'temp_data'

Một bảng tạm chỉ có thể được xóa trong phiên đã tạo nó.

2) Xung đột tiềm năng với bảng thường có cùng tên

Nếu một bảng tạm có cùng tên với một bảng thường,
bảng tạm sẽ được ưu tiên, và bảng thường sẽ trở nên ẩn.

Ví dụ

-- A regular table (users) exists
SELECT * FROM users;

-- Create a temporary table with the same name
CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50));

-- This now references the temporary table users
SELECT * FROM users;

Giải pháp:

• Sử dụng tiền tố như temp_ cho các bảng tạm để tránh xung đột tên.

Tóm tắt

Trong phần này, chúng tôi đã giải thích cách hoạt động của việc xóa bảng tạm và cách xóa chúng.

✔ Tóm tắt nhanh

• Bảng tạm được tự động xóa khi phiên làm việc kết thúc
• Chúng tồn tại cho đến khi phiên làm việc kết thúc
• Để xóa thủ công, sử dụng DROP TEMPORARY TABLE
• Thêm IF EXISTS để tránh lỗi
• Bạn không thể xác nhận bảng tạm bằng SHOW TABLES
• Bạn chỉ có thể xóa một bảng tạm trong phiên đã tạo nó
• Sử dụng tiền tố để tránh xung đột với các bảng thường có cùng tên

5. 5 Trường hợp sử dụng thực tế cho Bảng tạm MySQL (Bao gồm Tối ưu hiệu năng)

Giới thiệu

Bảng tạm MySQL cho phép bạn lưu trữ dữ liệu trung gian và đơn giản hoá các truy vấn phức tạp, giúp cải thiện hiệu năng cơ sở dữ liệu.

Trong phần này, chúng tôi giới thiệu năm trường hợp sử dụng thực tế cho bảng tạm.
Chúng tôi giải thích cách chúng có thể được áp dụng trong các tình huống thực tế, kèm theo ví dụ SQL.

5-1. Tối ưu hiệu năng truy vấn (Giảm tải JOIN)

Vấn đề

Khi xử lý các tập dữ liệu lớn, thực hiện các phép JOIN trực tiếp có thể làm giảm hiệu năng.

Giải pháp

Sử dụng bảng tạm để lọc trước dữ liệu mục tiêu trước khi thực hiện JOIN, giảm tải xử lý.

Ví dụ: Lấy dữ liệu đơn hàng cho người dùng đang hoạt động

-- First, store only active users in a temporary table
CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name FROM users WHERE status = 'active';

-- Perform JOIN using the temporary table
SELECT o.order_id, t.name, o.total_price
FROM orders o
JOIN temp_active_users t ON o.customer_id = t.id;

Lợi ích

• Giảm tải JOIN bằng cách chỉ nhắm tới người dùng đang hoạt động thay vì toàn bộ bảng users
• Đơn giản hoá truy vấn chính, cải thiện khả năng đọc

5-2. Xử lý Tổng hợp tạm thời

Vấn đề

Việc chạy lại nhiều lần cùng một truy vấn tổng hợp có thể làm giảm hiệu năng.

Giải pháp

Lưu kết quả tổng hợp vào một bảng tạm một lần để tránh các phép tính lặp lại không cần thiết.

Ví dụ: Lưu dữ liệu bán hàng hàng tháng vào một bảng tạm

-- Calculate monthly total sales and store in a temporary table
CREATE TEMPORARY TABLE temp_monthly_sales AS
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total_price) AS total_sales
FROM orders
GROUP BY month;

-- Retrieve aggregated results
SELECT * FROM temp_monthly_sales WHERE total_sales > 100000;

Lợi ích

• Tái sử dụng dữ liệu đã tổng hợp nhiều lần
• Cải thiện hiệu năng bằng cách tránh các phép tính dư thừa

5-3. Lưu dữ liệu trung gian cho xử lý batch

Vấn đề

When performing bulk updates or deletes, errors during processing can leave data in an inconsistent state.

Giải pháp

Use a temporary table to store intermediate data and maintain data consistency.

Example: Update order data under specific conditions

-- Store target rows in a temporary table
CREATE TEMPORARY TABLE temp_orders AS
SELECT order_id, total_price FROM orders WHERE status = 'pending';

-- Perform update based on the temporary table
UPDATE orders o
JOIN temp_orders t ON o.order_id = t.order_id
SET o.total_price = t.total_price * 1.1; -- Increase price by 10%

Lợi ích

• Safely update only selected data → Cập nhật an toàn chỉ dữ liệu được chọn
• Easy to verify data before and after updates → Dễ dàng kiểm tra dữ liệu trước và sau khi cập nhật

5-4. Per-User Temporary Data Management

Vấn đề

If temporary user-specific data is stored in a regular table, unnecessary data may accumulate over time.

Giải pháp

Temporary tables automatically remove data when the session ends, eliminating maintenance overhead.

Example: Store search results in a temporary table

-- Store user-specific search results
CREATE TEMPORARY TABLE temp_search_results AS
SELECT * FROM products WHERE category = 'electronics';

-- Display search results
SELECT * FROM temp_search_results;

Lợi ích

• Data is automatically removed when the session ends → Dữ liệu được tự động xóa khi phiên làm việc kết thúc
• Temporary search results can be reused during the session → Kết quả tìm kiếm tạm thời có thể được tái sử dụng trong phiên làm việc

5-5. Choosing Between Temporary Tables and Views

Vấn đề

When optimizing frequently executed queries, you may wonder whether to use a temporary table or a VIEW, especially if temporary data storage is required.

Giải pháp

• If data does not change frequently → Use a view (VIEW)
• If data changes frequently or needs materialization → Use a temporary table

Example: Using a temporary table

CREATE TEMPORARY TABLE temp_high_value_customers AS
SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 50000;

SELECT * FROM temp_high_value_customers;

Example: Using a view

CREATE VIEW high_value_customers AS
SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 50000;

Lợi ích

• Temporary tables store data physically, which can improve performance → Bảng tạm lưu trữ dữ liệu một cách vật lý, có thể cải thiện hiệu năng
• Views are convenient for query reuse, but performance may decrease with large datasets → View tiện lợi cho việc tái sử dụng truy vấn, nhưng hiệu năng có thể giảm khi dữ liệu lớn

Tóm tắt

In this section, we introduced five practical use cases for MySQL temporary tables.

✔ Quick recap

1. Optimize query performance (reduce JOIN overhead) → Store only required data in a temporary table before performing JOIN → Tối ưu hiệu năng truy vấn (giảm tải JOIN) → Lưu trữ chỉ dữ liệu cần thiết trong một bảng tạm trước khi thực hiện JOIN
2. Temporary aggregation processing → Store aggregated results to avoid repeated calculations → Xử lý tổng hợp tạm thời → Lưu trữ kết quả tổng hợp để tránh tính toán lặp lại
3. Intermediate data for batch processing → Safely handle large-scale updates → Dữ liệu trung gian cho xử lý hàng loạt → Xử lý an toàn các cập nhật quy mô lớn
4. Per-user temporary data management → Data is automatically removed when the session ends → Quản lý dữ liệu tạm thời theo người dùng → Dữ liệu được tự động xóa khi phiên làm việc kết thúc
5. Choose between temporary tables and views → Use temporary tables for changing data, views for stable query reuse → Lựa chọn giữa bảng tạm và view → Sử dụng bảng tạm cho dữ liệu thay đổi, view cho việc tái sử dụng truy vấn ổn định

6. Three Important Precautions When Using MySQL Temporary Tables Safely

Giới thiệu

MySQL temporary tables operate independently per session and are automatically removed under certain conditions, making them a convenient feature. However, improper use can lead to performance degradation or unexpected errors.

In this section, we explain three important precautions to ensure safe use of temporary tables.

6-1. Precaution 1: Do Not Over-Rely on Automatic Removal

Vấn đề

Because temporary tables are automatically removed when the session ends, it may seem unnecessary to drop them explicitly. However, this can sometimes cause unintended issues.

Ví dụ về các vấn đề

• Kết nối chạy lâu liên tục tiêu tốn bộ nhớ
• Nếu một phiên làm việc vẫn mở, các bảng tạm không được xóa và tiếp tục tiêu tốn tài nguyên cơ sở dữ liệu.
• Việc không xóa bỏ một cách rõ ràng có thể gây ra lỗi thiết kế
• Nếu một quy trình batch kết nối lại bất ngờ, bảng tạm có thể biến mất và gây lỗi.

Giải pháp

• Xóa bỏ các bảng tạm một cách rõ ràng khi không còn cần thiết bằng DROP TEMPORARY TABLE
• Trong các kết nối chạy lâu (ví dụ: công việc batch), xóa các bảng tạm định kỳ

Ví dụ: Xóa một bảng tạm một cách rõ ràng

DROP TEMPORARY TABLE IF EXISTS temp_users;

Điểm chính

• Thêm IF EXISTS ngăn lỗi nếu bảng không tồn tại.

6-2. Thận trọng 2: Tránh Xung Đột Tên với Bảng Thường

Vấn đề

Bạn có thể tạo một bảng tạm với cùng tên với một bảng thường. Tuy nhiên, khi làm như vậy, bảng thường sẽ tạm thời không hiển thị.

Ví dụ về vấn đề

-- A regular users table exists
SELECT * FROM users;

-- Create a temporary table with the same name
CREATE TEMPORARY TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- This now returns data from the temporary table, not the regular one
SELECT * FROM users;

Miễn là bảng tạm tồn tại, bảng thường cùng tên sẽ bị ẩn, điều này có thể dẫn đến lỗi truy xuất dữ liệu không mong muốn.

Giải pháp

• Sử dụng tiền tố như “temp_” cho tên bảng tạm
• Áp dụng quy tắc đặt tên rõ ràng để phân biệt bảng tạm và bảng thường

Ví dụ: Tạo bảng tạm an toàn

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

Lợi ích

• Sử dụng tiền tố temp_ ngăn xung đột với bảng users thường.
• Giúp dễ dàng phân biệt các bảng trong mã ứng dụng.

6-3. Thận trọng 3: Các chỉ mục và ràng buộc không được kế thừa tự động

Vấn đề

Nếu bạn tạo bảng bằng CREATE TEMPORARY TABLE ... AS SELECT ..., các chỉ mục và ràng buộc từ bảng gốc không được kế thừa, điều này có thể làm giảm hiệu năng.

Ví dụ về vấn đề

-- Regular users table (with indexes)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50)
);

-- Create temporary table (indexes are NOT inherited)
CREATE TEMPORARY TABLE temp_users AS
SELECT id, email, name FROM users;

Trong trường hợp này, các ràng buộc PRIMARY KEY và UNIQUE không được chuyển sang temp_users, có thể làm chậm tìm kiếm và cho phép dữ liệu trùng lặp.

Giải pháp

• Thêm chỉ mục một cách rõ ràng sau khi tạo bảng tạm
• Nếu định nghĩa cột thủ công bằng CREATE TEMPORARY TABLE, chỉ định chỉ mục trong quá trình tạo

Ví dụ: Thêm chỉ mục thủ công

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

ALTER TABLE temp_users ADD INDEX idx_email (email);

Cách tiếp cận này cho phép bạn tạo một bảng tạm với các chỉ mục tương tự như bảng gốc.

Tóm tắt

Trong phần này, chúng tôi đã giải thích ba biện pháp thận trọng quan trọng để sử dụng bảng tạm một cách an toàn.

✔ Tóm tắt nhanh

1. Không nên phụ thuộc quá mức vào việc tự động xóa

• Xóa bỏ các bảng tạm một cách rõ ràng bằng DROP TEMPORARY TABLE
• Định kỳ xóa chúng trong các phiên làm việc kéo dài

1. Tránh xung đột tên với các bảng thường

• Nếu một bảng thường cùng tên tồn tại, bảng tạm sẽ được ưu tiên
• Sử dụng tiền tố như temp_ để phân biệt rõ ràng

1. Các chỉ mục và ràng buộc không được kế thừa tự động

• Với CREATE TEMPORARY TABLE ... AS SELECT ..., các chỉ mục bị mất
• Thêm chỉ mục thủ công sau khi tạo

Bằng cách ghi nhớ những điểm này, bạn có thể sử dụng bảng tạm MySQL một cách an toàn đồng thời cải thiện hiệu năng cơ sở dữ liệu.

7. 10 Câu Hỏi Thường Gặp (FAQ) Về Bảng Tạm Thời MySQL

Giới thiệu

Trong phần này, chúng tôi trả lời 10 câu hỏi thường gặp về bảng tạm thời MySQL.
Chúng tôi đề cập cách chúng hoạt động, các hạn chế, cân nhắc về hiệu năng và cách khắc phục trong các tình huống thực tế.

7-1. Câu Hỏi Về Các Thông Số Cơ Bản

Q1. Bảng tạm thời có thể được truy cập từ một phiên khác không?

A. Không, không thể.
Một bảng tạm thời chỉ có hiệu lực trong phiên mà nó được tạo và không thể được truy cập từ các phiên khác.

-- Created in Session A
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Attempt to access from Session B (results in error)
SELECT * FROM temp_users;

Lỗi:

ERROR 1146 (42S02): Table 'temp_users' doesn't exist

Nếu bạn cần chia sẻ dữ liệu giữa các phiên, bạn phải sử dụng một bảng thường.

Q2. Bảng tạm thời có được lưu trên đĩa không?

A. Chúng thường được lưu trong bộ nhớ, nhưng có thể chuyển sang đĩa trong một số điều kiện nhất định.
Nếu kích thước bảng vượt quá tmp_table_size hoặc max_heap_table_size, MySQL có thể tạo bảng tạm thời trên đĩa bằng cách sử dụng InnoDB hoặc MyISAM.

SHOW VARIABLES LIKE 'tmp_table_size';

Để cải thiện hiệu năng, hãy cấu hình tmp_table_size một cách thích hợp.

Q3. Tôi có thể tạo chỉ mục trên bảng tạm thời không?

A. Có, bạn có thể.
Bạn có thể định nghĩa PRIMARY KEY hoặc INDEX giống như với một bảng thường.

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

ALTER TABLE temp_users ADD INDEX idx_email (email);

Tuy nhiên, nếu bạn sử dụng CREATE TEMPORARY TABLE ... AS SELECT ..., các chỉ mục sẽ không được kế thừa, vì vậy bạn phải thêm chúng một cách thủ công.

7-2. Câu Hỏi Về Hiệu Năng và Hành Vi

Q4. Có thay đổi nào đối với bảng tạm thời trong MySQL 8.0 không?

A. MySQL 8.0 đã giới thiệu Common Table Expressions (CTEs) sử dụng câu lệnh WITH.
Từ MySQL 8.0 trở đi, bạn có thể xử lý các tập kết quả tạm thời bằng CTEs mà không cần tạo bảng tạm thời một cách rõ ràng.

WITH temp_users AS (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM temp_users;

Sử dụng CTEs thay vì bảng tạm thời có thể đơn giản hoá truy vấn và giảm việc sử dụng bộ nhớ.

Q5. Sự khác biệt giữa bảng tạm thời và bảng MEMORY là gì?

A. Bảng MEMORY tồn tại vượt qua một phiên, trong khi bảng tạm thời thì không.
Bảng tạm thời được xóa khi phiên kết thúc, trong khi bảng MEMORY vẫn tồn tại cho đến khi máy chủ khởi động lại (hoặc được xóa một cách rõ ràng).

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

Khi nào nên dùng loại nào?

• Bảng tạm thời: Xử lý ngắn hạn, phạm vi trong phiên
• Bảng MEMORY: Truy cập tốc độ cao với tính tồn tại ở mức máy chủ

7-3. Câu Hỏi Về Xóa và Khắc Phục Sự Cố

Q6. Tôi có thể xóa một bảng tạm thời bằng DROP TABLE không?

A. Không, bạn phải sử dụng DROP TEMPORARY TABLE.
Luôn sử dụng DROP TEMPORARY TABLE khi xóa một bảng tạm thời.

DROP TEMPORARY TABLE temp_users;

Sử dụng DROP TABLE thông thường có thể gây ra lỗi.

Q7. Tại sao SHOW TABLES không hiển thị các bảng tạm thời?

A. Các bảng tạm thời không được liệt kê bởi SHOW TABLES.
Để kiểm tra sự tồn tại của chúng, truy vấn INFORMATION_SCHEMA.

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'temp_users';

Nếu không có kết quả trả về, bảng tạm thời có thể đã bị xóa.

Tóm tắt

Trong phần này, chúng tôi đã đề cập 10 câu hỏi thường gặp về bảng tạm thời MySQL.

✔ Tóm tắt nhanh

1. Bảng tạm không thể được truy cập từ các phiên khác
2. Chúng được tạo trong bộ nhớ nhưng có thể chuyển sang đĩa nếu lớn
3. Cần định nghĩa chỉ mục thủ công khi sử dụng AS SELECT
4. CTE (WITH) có sẵn trong MySQL 8.0+
5. Khác với bảng MEMORY, bảng tạm sẽ biến mất khi kết thúc phiên
6. Sử dụng DROP TEMPORARY TABLE để xóa chúng
7. SHOW TABLES không hiển thị các bảng tạm

8. Tóm tắt: Các điểm chính để sử dụng bảng tạm MySQL một cách hiệu quả

Giới thiệu

Bảng tạm MySQL là một công cụ mạnh mẽ để lưu trữ dữ liệu trung gian và tối ưu hiệu suất truy vấn.
Ở đây, chúng tôi tóm tắt các điểm chính đã được thảo luận trong toàn bộ hướng dẫn này.

8-1. Khái niệm cơ bản về Bảng tạm MySQL

Bảng tạm là gì?

• Tồn tại độc lập cho mỗi phiên
• Tự động bị xóa khi phiên kết thúc
• Hỗ trợ INSERT, UPDATE và DELETE giống như một bảng thường

Cú pháp tạo cơ bản

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

Các trường hợp sử dụng chính

• Lưu trữ dữ liệu tạm thời
• Tối ưu hiệu suất truy vấn
• Bảng trung gian cho xử lý batch
• Quản lý dữ liệu tạm thời cho từng người dùng

8-2. Ưu điểm của Bảng tạm

1) Cải thiện hiệu suất truy vấn

• Giảm tải công việc JOIN
• Thực hiện tổng hợp trước để giảm các phép tính lặp lại
• Đơn giản hoá truy vấn bằng cách loại bỏ dữ liệu không cần thiết

Ví dụ: Giảm tải JOIN

CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name FROM users WHERE status = 'active';

SELECT o.order_id, t.name, o.total_price
FROM orders o
JOIN temp_active_users t ON o.customer_id = t.id;

2) Lưu trữ tạm thời và quản lý dựa trên phiên

• Tự động bị xóa khi phiên kết thúc
• Lý tưởng cho việc lưu trữ dữ liệu ngắn hạn
• Thao tác dữ liệu độc lập mà không ảnh hưởng đến các phiên khác

Ví dụ: Lưu trữ tạm thời kết quả tìm kiếm

CREATE TEMPORARY TABLE temp_search_results AS
SELECT * FROM products WHERE category = 'electronics';

SELECT * FROM temp_search_results;

3) Cập nhật dữ liệu an toàn

• Hữu ích như một bảng trung gian trong xử lý batch
• Có thể dùng làm bản sao lưu trong quá trình cập nhật dữ liệu
• Hiệu quả cho việc tạo bộ dữ liệu thử nghiệm

Ví dụ: Cập nhật dữ liệu an toàn

CREATE TEMPORARY TABLE temp_orders AS
SELECT order_id, total_price FROM orders WHERE status = 'pending';

UPDATE orders o
JOIN temp_orders t ON o.order_id = t.order_id
SET o.total_price = t.total_price * 1.1;

8-3. Nhược điểm và Lưu ý

1) Dữ liệu biến mất khi phiên kết thúc

• Không phù hợp cho lưu trữ lâu dài
• Sử dụng bảng thường cho việc lưu trữ dài hạn

2) Không thể chia sẻ giữa các phiên

• Không thể truy cập từ các kết nối khác
• Sử dụng bảng thường khi cần chia sẻ dữ liệu giữa các người dùng

3) Chỉ mục và ràng buộc không được kế thừa tự động

• CREATE TEMPORARY TABLE ... AS SELECT ... không tạo chỉ mục
• Thêm chỉ mục thủ công nếu cần

  ALTER TABLE temp_users ADD INDEX idx_email (email);
  

8-4. Các thực hành tốt nhất để sử dụng an toàn

✅ Xóa bỏ một cách rõ ràng khi không còn cần thiết

DROP TEMPORARY TABLE IF EXISTS temp_users;

✅ Tránh xung đột tên với các bảng thường

• Sử dụng tiền tố temp_

  CREATE TEMPORARY TABLE temp_users (...);
  

✅ Thiết kế với hiệu suất trong tâm trí

• Nếu bảng lớn lên và chuyển sang đĩa, hãy cân nhắc điều chỉnh tmp_table_size

  SHOW VARIABLES LIKE 'tmp_table_size';
  

8-5. Bảng tạm so với các lựa chọn thay thế (Views và CTEs)

Cũng quan trọng để cân nhắc khi nào sử dụng bảng tạm so với views (VIEW) hoặc CTEs (Common Table Expressions).

Tóm tắt

Trong hướng dẫn này, chúng tôi đã đề cập đến tất cả các khía cạnh quan trọng của bảng tạm MySQL.

✔ Tóm tắt nhanh

• Bảng tạm thời được tự động xóa khi phiên làm việc kết thúc
• Chúng giúp tối ưu hiệu năng bằng cách giảm tải JOIN và quá trình tổng hợp
• Hữu ích cho xử lý batch, kết quả tìm kiếm tạm thời và dữ liệu thử nghiệm
• Chúng không thể chia sẻ giữa các phiên, và cần phải thêm chỉ mục một cách thủ công khi cần
• Lựa chọn giữa bảng tạm thời, view và CTE cho phép quản lý dữ liệu linh hoạt

Bạn đã hoàn thành tất cả các phần của hướng dẫn bảng tạm thời MySQL! 🎉
Sử dụng tài liệu tham khảo này để tận dụng hiệu quả các bảng tạm thời trong các dự án MySQL của bạn.