The MySQL (and Maria DB) TRUNCATE Command, With Examples (And Warning!)

MySQL Truncate

MySQL (and its functional equivalent, MariaDB) have two things named TRUNCATE – so watch out which one you want to use or suffer dire consequences!

This article outlines the difference and usages of the MySQL TRUNCATE function and TRUNCATE TABLE statement – they share similar names but do very different things.

TRUNCATE Function – Truncating a Number to a Certain Number of Decimal Places

First, the TRUNCATE function. The TRUNCATE function reduces the number of decimal places for a given number.

MySQL TRUNCATE Function Syntax

TRUNCATE(number, decimals)

Note that:

  • number is a number
  • decimals is an integer representing the number of decimal places number should be truncated to.
  • It does not round the number – it just cuts off any additional numbers after the given decimal place (or adds zeros if there aren’t enough)

MySQL TRUNCATE Function Example

Here, a number with 4 decimal places is truncated to 3 decimal places.

SELECT TRUNCATE (3.1459, 3);

Which will return

3.145

Note that the number has been truncated – not rounded!

TRUNCATE Statement – Deleting ALL Data from a Table Irreversibly

The TRUNCATE statement is an entirely different thing. It irreversibly deletes ALL data from a given table.

You’ll want to be quite careful when using it.

TRUNCATE TABLE Statement Syntax

TRUNCATE TABLE database_name.table_name;

Note that:

  • database_name is the name of the database containing the table to be cleared
    • It is optional if you are already working inside a database
  • table_name is the name of the table to be cleared
  • Operations performed by the TRUNCATE TABLE statement cannot be rolled back!
  • The counters for any columns using AUTO_INCREMENT will be reset
    • So, if you have an id column, they will start counting at 0 again
  • When a table is truncated, MySQL is actually dropping the table completely and recreating it, rather than deleting the data in it row by row.
    • Because of this, DELETE triggers will not be fired during the truncate operation.
  • Depending on your MySQL version, you may not truncate an InnoDB or NDB table referenced by a foreign key in another table.
SHARE:
nv-author-image

Brad Morton

I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Reply

Your email address will not be published. Required fields are marked *