How to TRUNCATE TABLE in MariaDB

Galaxy Glossary

How do I safely truncate a table in MariaDB?

TRUNCATE TABLE instantly removes all rows from one or more MariaDB tables and resets AUTO_INCREMENT counters.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use TRUNCATE TABLE instead of DELETE?

TRUNCATE TABLE is faster than DELETE without a WHERE clause because it deallocates data pages rather than logging each row removal. It also resets AUTO_INCREMENT values, making it ideal for quickly purging staging or log tables.

What is the basic TRUNCATE TABLE syntax?

Use TRUNCATE TABLE table_name; to wipe every row. The command requires DROP privilege and cannot be rolled back unless the session is in --safe-rollback mode with log-bin disabled.

How do I truncate multiple ecommerce tables at once?

MariaDB allows truncating several tables in a single statement: TRUNCATE TABLE Orders, OrderItems; performs an atomic truncate on both tables, maintaining referential integrity.

Can I keep the AUTO_INCREMENT counter?

No. TRUNCATE TABLE always resets AUTO_INCREMENT to 1. If you must preserve the counter, use DELETE FROM table; instead.

How to truncate only certain partitions?

For partitioned tables, specify partitions: TRUNCATE TABLE Orders PARTITION (p2023_q1, p2023_q2);. Only listed partitions are emptied; other partitions remain untouched.

Best practices for TRUNCATE TABLE

Always disable foreign-key checks or truncate child tables first to avoid constraint errors. Schedule truncations during low-traffic windows and take backups when data recovery is critical.

When should I avoid TRUNCATE TABLE?

Avoid truncating tables referenced by external replication tools or where point-in-time recovery is required. Use DELETE with binlog enabled for auditable row-level changes.

Why How to TRUNCATE TABLE in MariaDB is important

How to TRUNCATE TABLE in MariaDB Example Usage


-- Purge all order-related tables before importing fresh data
START TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE Orders;
TRUNCATE TABLE OrderItems;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

How to TRUNCATE TABLE in MariaDB Syntax


TRUNCATE [TABLE] table_name
    [ , table_name2 ... ]
    [PARTITION (partition_list)];

-- Examples
TRUNCATE TABLE Orders;
TRUNCATE TABLE Orders, OrderItems;
TRUNCATE TABLE Orders PARTITION (p2023_q1, p2023_q2);

Common Mistakes

Mistake: Relying on TRUNCATE TABLE with binary logging ON for selective rollbacks. Why wrong: TRUNCATE is DDL and auto-commits, making rollback impossible. Fix: Use DELETE in a transaction if you might undo.
Mistake: Truncating parent tables before child tables with foreign keys. Why wrong: MariaDB blocks the operation. Fix: Disable FOREIGN_KEY_CHECKS or truncate child tables first.