Perbedaan DELETE dan TRUNCATE SQL

kalau disuruh ingat satu kalimat saja, ini dia, DELETE bisa di-rollback, TRUNCATE umumnya tidak. saya pernah hampir apes karena pakai TRUNCATE padahal cuma mau bersihin sebagian data, untung masih ada backup. biar kalian tidak belajar dengan cara yang sama, di sini saya rangkum bedanya pelan-pelan. kalau transaksi masih bingung, baca dulu perbedaan MyISAM dan InnoDB di MySQL dan trigger dan stored procedure.

1. DELETE itu hapus baris satu per satu

DELETE itu telaten, dia hapus baris per baris dan catat tiap penghapusan di transaction log. karena dicatat, kalau salah masih bisa di-rollback.

-- Hapus satu baris tertentu (bisa rollback)
DELETE FROM users WHERE id = 10;

-- Hapus beberapa baris dengan kondisi (bisa rollback)
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < '2024-01-01';

-- Hapus semua baris satu per satu (bisa rollback, lambat)
DELETE FROM logs;

sedikit catatan yang sering saya tekankan ke tim:

• WHERE id = 10, tanpa WHERE semua baris ikut kehapus, tapi tetap satu-satu
• kalau mau hapus semua tapi pakai DELETE, prosesnya O(n), makin banyak baris makin lama

2. TRUNCATE itu kosongkan tabel langsung

TRUNCATE tidak main per baris. dia dealokasi page tempat data disimpan, lalu bikin ulang tabel kosong dengan struktur sama. makanya tidak ada log per baris.

-- Kosongkan tabel (tidak bisa rollback, sangat cepat)
TRUNCATE TABLE users;

-- Sintaks alternatif (sama hasilnya)
TRUNCATE users;

habis TRUNCATE, tabel users masih ada, kolom dan index masih utuh, cuma isinya nol dan AUTO_INCREMENT balik ke awal.

3. bedanya kalau diadu langsung

tabel ini yang biasanya saya tunjukkin dulu sebelum nerangin panjang lebar.

4. soal AUTO_INCREMENT, ini yang sering bikin kaget

-- INSERT beberapa data
INSERT INTO invoices (customer, total) VALUES
('Andi', 100000),
('Budi', 200000),
('Citra', 150000);

-- DELETE semua
DELETE FROM invoices;
-- AUTO_INCREMENT tetap di 3. INSERT berikutnya = id 4, 5, 6

-- TRUNCATE semua
TRUNCATE TABLE invoices;
-- AUTO_INCREMENT direset. INSERT berikutnya = id 1, 2, 3

kenapa begitu, DELETE cuma hapus baris, tidak bikin ulang tabel. TRUNCATE bikin tabel baru, jadi counter balik ke nol. kalau id kalian tampil ke user kayak nomor invoice, hati-hati, TRUNCATE bikin nomor terlihat ngulang dari awal.

5. transaksi dan rollback, yang bikin DELETE lebih aman

DELETE itu enak buat transaksi, salah hapus masih bisa tarik balik:

START TRANSACTION;

DELETE FROM users WHERE id = 10;

-- Ups, salah hapus! Batalkan
ROLLBACK;

-- Data kembali. users dengan id=10 masih ada
SELECT * FROM users WHERE id = 10;

kalau pakai TRUNCATE di dalam transaksi:

START TRANSACTION;

TRUNCATE TABLE users;  -- Di InnoDB ini tidak bisa di-rollback

COMMIT;  -- Langsung permanen

di MySQL InnoDB, TRUNCATE itu di-optimasi jadi drop dan recreate, jadi walau di dalam transaction tetap tidak bisa di-undo. jangan andalkan rollback untuk TRUNCATE.

6. trigger, DELETE yang manggil, TRUNCATE yang diem

-- Trigger untuk audit log
CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, table_name, old_data, deleted_at)
    VALUES ('DELETE', 'users', OLD.name, NOW());
END;

-- DELETE menembak trigger
DELETE FROM users WHERE id = 10;
-- Trigger aktif: audit_log dapat record baru

-- TRUNCATE tidak menembak trigger
TRUNCATE TABLE users;
-- Trigger tidak aktif. audit_log kosong

alasannya simpel, TRUNCATE tidak hapus per baris, jadi tidak ada event per baris buat trigger. kalau audit penting, jangan pakai TRUNCATE.

7. foreign key bikin TRUNCATE gagal

-- Error: Cannot truncate a table referenced in a foreign key constraint
TRUNCATE TABLE orders;

-- Opsi 1: Nonaktifkan foreign key check dulu
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE orders;
SET FOREIGN_KEY_CHECKS = 1;

-- Opsi 2: Hapus data di child table dulu
DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders);
DELETE FROM orders;

kalau TRUNCATE error karena foreign key, berarti ada tabel anak yang masih refer ke tabel ini. saya lebih suka opsi 2, karena matiin FOREIGN_KEY_CHECKS kalau lupa balikin bisa bikin constraint jebol di operasi lain.

8. DELETE bisa ORDER BY dan LIMIT

ini kelebihan DELETE yang sering kepakai buat bersih-bersih:

-- Hapus 100 data terlama (berguna untuk cleanup)
DELETE FROM logs
ORDER BY created_at ASC
LIMIT 100;

-- Hapus data duplikat (simpan satu)
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

ORDER BY ... LIMIT 100 enak buat cron yang bersihin log 1000 per hari, tidak nge-lock tabel kelamaan.

9. soft delete, kalau mau bisa balikin

kadang hapus permanen itu terlalu berisiko. soft delete cuma tandai:

-- Soft delete (UPDATE, bukan DELETE)
UPDATE users
SET deleted_at = NOW()
WHERE id = 10;

-- Query data aktif (exclude soft delete)
SELECT * FROM users
WHERE deleted_at IS NULL;

-- Query data yang sudah dihapus
SELECT * FROM users
WHERE deleted_at IS NOT NULL;

enaknya, bisa di-restore kapan saja, tidak perlu backup. minusnya, tiap query harus tambah WHERE deleted_at IS NULL, kalau lupa data yang sudah dihapus ikut kehitung.

10. DELETE vs TRUNCATE vs DROP

-- DELETE: hapus data, TABLE tetap ada
DELETE FROM users;
-- users tabel masih ada, hanya isinya kosong

-- TRUNCATE: hapus semua data, TABLE tetap ada, AUTO_INCREMENT reset
TRUNCATE TABLE users;
-- users tabel masih ada, kosong, AUTO_INCREMENT=1

-- DROP TABLE: hapus TABLE beserta STRUKTUR, DATA, INDEX, CONSTRAINT
DROP TABLE users;
-- users tabel GONE. kolom, index, foreign key, semuanya hilang

analogi yang sering saya pakai, DELETE itu kosongkan laci, TRUNCATE kosongkan laci plus reset nomor laci, DROP itu buang mejanya sekalian. DROP tidak bisa di-rollback tanpa backup.

11. kapan pakai apa, biar tidak salah pilih

kalau ragu, pilih DELETE. lebih lambat tapi aman.

12. catatan kecil sebelum eksekusi

• backup dulu sebelum TRUNCATE, tidak ada undo
• jangan pernah DELETE FROM tabel tanpa WHERE di production, kalau mau kosongkan pakai TRUNCATE atau WHERE yang jelas
• untuk data penting, pertimbangkan soft delete dulu
• batch cleanup pakai LIMIT biar tidak lock lama
• DELETE banyak bisa bikin transaction log penuh, TRUNCATE tidak
• cek foreign key, kalau TRUNCATE gagal berarti ada child table
• kalau id tampil ke user, ingat AUTO_INCREMENT TRUNCATE bikin id ngulang

Kesimpulan

• DELETE itu hapus baris satu-satu, bisa pakai WHERE, bisa rollback, trigger jalan, AUTO_INCREMENT tidak reset, lambat kalau data banyak
• TRUNCATE itu kosongkan tabel, tidak bisa WHERE, tidak bisa rollback, tidak trigger, AUTO_INCREMENT reset, super cepat
• mau hapus sebagian, pakai DELETE WHERE
• mau kosongkan total dan tidak butuh undo, pakai TRUNCATE
• butuh audit trail, pakai soft delete UPDATE SET deleted_at
• untuk perbedaan MyISAM dan InnoDB di mana transaction support beda, ingat InnoDB support transaction tapi TRUNCATE tetap tidak bisa di-rollback

Baca Juga Mengenai :

• Perbedaan MyISAM dan InnoDB MySQL
• Trigger dan Stored Procedure MySQL