Người thử nghiệm muốn xóa bảng phân tầng trước khi chạy thử nghiệm mới. Nên là DELETE, TRUNCATE hay DROP? Lựa chọn sai có thể dẫn đến công việc chậm chạp, thiếu bàn hoặc trong quá trình sản xuất, một buổi chiều tồi tệ.

Chúng tôi đã chạy các ví dụ DELETE và DROP bên dưới trong SQLite 3.50 và kết quả đầu ra đến từ lần chạy đó. SQLite không có lệnh TRUNCATE, vì vậy các ghi chú TRUNCATE dựa trên tài liệu PostgreSQL, MySQL và SQL Server chính thức chứ không phải dựa trên thử nghiệm của chúng tôi.

Câu trả lời ngắn gọn

• Cần xóa một số hàng? Sử dụng DELETE ... WHERE.
• Cần làm trống một bảng nhưng vẫn giữ nó? Sử dụng TRUNCATE, nơi nó được hỗ trợ.
• Cần cái bàn biến mất vĩnh viễn? Sử dụng DROP TABLE.

DELETE: xóa hàng

DELETE là lệnh thay đổi dữ liệu (DML). Nó loại bỏ từng hàng một, kích hoạt trình kích hoạt cấp hàng và có thể được lọc. Chúng tôi thiết lập một bảng nhỏ với trình kích hoạt kiểm tra:

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

Chỉ có hàng phù hợp được thực hiện và trình kích hoạt đã ghi lại nó. Việc chạy DELETE FROM logs mà không có mệnh đề WHERE đã loại bỏ ba hàng còn lại và bảng kiểm tra sau đó chứa bốn mục, mỗi mục đã xóa.

DELETE không thiết lập lại bộ đếm tăng tự động. Sau khi làm trống bảng, phần chèn tiếp theo của chúng tôi nhận được id 5, không phải 1. Trong SQLite, điều này là do AUTOINCREMENT. Các cột nhận dạng MySQL InnoDB và SQL Server cũng tiếp tục từ vị trí cũ sau khi XÓA.

TRUNCATE: làm trống bảng

TRUNCATE TABLE logs; xóa mọi hàng cùng một lúc. Nó không thể có mệnh đề WHERE. Thay vì xóa từng hàng một, cơ sở dữ liệu thường giải phóng các trang dữ liệu của bảng, đó là lý do tại sao nó nhanh hơn nhiều trên các bảng lớn.

Khi chạy nó trong SQLite, chúng tôi gặp lỗi cú pháp vì SQLite không có lệnh:

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

Trong SQLite, sử dụng DELETE FROM logs; không có mệnh đề WHERE. Tài liệu của SQLite mô tả “tối ưu hóa cắt ngắn” giúp thực hiện việc này nhanh chóng khi bảng không có trình kích hoạt. Bảng của chúng tôi có một trình kích hoạt, do đó thao tác xóa chạy từng hàng và kích hoạt nó cho từng hàng.

Trong cơ sở dữ liệu hỗ trợ TRUNCATE, theo tài liệu của họ:

• Nó thường không kích hoạt trình kích hoạt DELETE cấp hàng.
• Nó thường đặt lại bộ đếm nhận dạng hoặc bộ đếm tăng tự động, ví dụ như trong MySQL và SQL Server. Trong PostgreSQL bạn thêm RESTART IDENTITY.
• Nó cần quyền mạnh hơn DELETE trong hầu hết các hệ thống.

DROP: tự xóa bảng

DROP TABLE là lệnh lược đồ (DDL). Nó loại bỏ dữ liệu, định nghĩa bảng, chỉ mục, trình kích hoạt và ràng buộc của nó. Bất cứ điều gì đề cập đến bảng, chẳng hạn như dạng xem hoặc khóa ngoại, cũng bị ảnh hưởng.

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS rất hữu ích trong các tập lệnh thiết lập có thể chạy nhiều lần. Chế độ xem trên bảng bị bỏ có thể bị hỏng, như chúng tôi đã trình bày trong Giải thích về chế độ xem SQL.

Bảng so sánh

Bạn có thể quay lại được không?

Đây là nơi có rất nhiều bài viết đơn giản hóa quá mức. Câu trả lời trung thực là nó phụ thuộc vào cơ sở dữ liệu.

DELETE có thể được khôi phục trong một giao dịch ở mọi nơi. Chúng tôi đã thử nghiệm nó:

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

DROP cũng được khôi phục trong SQLite, hỗ trợ DDL giao dịch:

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

Đối với TRUNCATE và DROP trong các cơ sở dữ liệu khác, theo tài liệu của họ:

Vì vậy, “TRUNCATE không bao giờ có thể được khôi phục” là sai đối với PostgreSQL và SQL Server và “nó luôn có thể được khôi phục” là sai đối với MySQL. Bên ngoài một giao dịch, không có giao dịch nào có thể được hoàn tác nếu không có bản sao lưu.

Tại sao TRUNCATE lại nhanh

DELETE ghi lại từng hàng đã xóa để thay đổi có thể được hoàn tác và sao chép, kích hoạt trình kích hoạt và kiểm tra các ràng buộc theo từng hàng. Trên một bảng có hàng triệu hàng, điều đó tạo ra rất nhiều công việc và khối lượng nhật ký. TRUNCATE thường phân bổ bộ nhớ trong một thao tác, ghi nhật ký ít hơn nhiều. Để xóa một tập hợp con lớn thay vì tất cả, hãy xóa theo đợt để tránh bị khóa lâu. Chúng tôi chi trả chi phí khóa và truy vấn trong Tối ưu hóa truy vấn SQL.

Khóa và quyền ngoại

• Khóa ngoại. SQL Server từ chối TRUNCATE một bảng được tham chiếu bằng khóa ngoại. PostgreSQL cũng yêu cầu bạn cắt bớt các bảng tham chiếu hoặc sử dụng CASCADE. Thay vào đó, DELETE sẽ kiểm tra từng hàng theo các ràng buộc.
• Quyền. DELETE cần có quyền xóa. TRUNCATE thường cần ALTER hoặc quyền TRUNCATE chuyên dụng và DROP cần quyền sở hữu hoặc ALTER trên lược đồ.
• Sao chép và kiểm tra. Nếu các hệ thống khác phụ thuộc vào nhật ký thay đổi hoặc trình kích hoạt cấp hàng, TRUNCATE có thể bỏ qua chúng. Hãy kiểm tra xem ai sở hữu những hệ thống đó trước.

Nên sử dụng cái nào

Đối với dữ liệu thử nghiệm, TRUNCATE rất phổ biến vì nó đặt lại ID, giúp các thử nghiệm có thể lặp lại. Gói từng thử nghiệm trong một giao dịch được khôi phục là một tùy chọn khác, được giải thích trong Giao dịch SQL và ACID.

Danh sách kiểm tra an toàn

1. Xác nhận cơ sở dữ liệu và môi trường bạn đang kết nối.
2. Trước tiên hãy chạy mệnh đề WHERE dưới dạng CHỌN.
3. Bao bọc thay đổi trong một giao dịch mà cơ sở dữ liệu của bạn hỗ trợ.
4. Có bản sao lưu đã được kiểm tra gần đây trước khi TRUNCATE hoặc DROP trong quá trình sản xuất.
5. Đặt câu lệnh DROP trong các tập lệnh di chuyển đã được xem xét, không bao giờ đặt trong bảng điều khiển đặc biệt.
6. Kiểm tra những gì phụ thuộc vào bảng, chẳng hạn như chế độ xem, khóa ngoại và báo cáo, trước khi loại bỏ bảng.

Xóa lượng lớn dữ liệu một cách an toàn

Xóa hàng triệu hàng bằng một DELETE có thể khóa bảng trong thời gian dài, điền vào nhật ký giao dịch và sao chép chậm. Một cách phổ biến là xóa theo từng đợt nhỏ và lặp lại cho đến khi không còn gì:

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

Chạy câu lệnh trong một vòng lặp, với một khoảng dừng ngắn giữa các đợt, cho đến khi nó báo cáo không có hàng nào. Mỗi lô là một giao dịch nhỏ, do đó, khóa rất ngắn và các truy vấn khác vẫn tiếp tục chạy. Một chỉ mục trên cột bộ lọc ở đây created_at giúp giữ cho mọi lô đều nhanh chóng.

Đối với dữ liệu tăng theo ngày, chẳng hạn như nhật ký và sự kiện, phân vùng bảng thậm chí còn tốt hơn. Mỗi tháng tồn tại trong một phân vùng riêng và việc xóa dữ liệu cũ trở thành một thao tác nhanh chóng giúp loại bỏ hoặc cắt bớt một phân vùng thay vì xóa từng hàng một.

Xóa mềm

Nhiều ứng dụng không bao giờ xóa hồ sơ kinh doanh về mặt vật lý. Họ thêm một cột chẳng hạn như deleted_at và đặt cột đó thay vào đó, sau đó truy vấn sẽ lọc theo deleted_at IS NULL. Xóa mềm giúp khôi phục dễ dàng và lưu giữ lịch sử để kiểm tra, nhưng mọi truy vấn đều phải ghi nhớ bộ lọc, đây là một công việc tốt cho chế độ xem. Các ràng buộc duy nhất cũng cần được quan tâm vì một hàng bị xóa mềm vẫn giữ giá trị của nó. Luật về quyền riêng tư có thể yêu cầu xóa thực sự dữ liệu cá nhân sau một thời gian, vì vậy việc xóa tạm thời không thể thay thế cho chính sách lưu giữ.

Lời kết

DELETE là công cụ chính xác và cẩn thận. TRUNCATE là nút đặt lại nhanh và DROP sẽ xóa toàn bộ bảng. Rủi ro lớn nhất là giả sử một cơ sở dữ liệu hoạt động giống cơ sở dữ liệu khác, đặc biệt là về vấn đề khôi phục. Khi nghi ngờ, trước tiên hãy kiểm tra lệnh trên bản sao của bảng trong cơ sở dữ liệu của riêng bạn.

Câu hỏi thường gặp

Tiếp tục học hỏi