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.
