How to truncate a MySQL table with PHP

This tutorial will show you how to truncate a MySQL table using PHP.

In the example below, we will use the PDO object to execute a TRUNCATE statement and empty table.

//Our SQL statement. This will query empty / truncate
//a table called "videos"
$sql = "TRUNCATE TABLE `videos`";

//Prepare the SQL query.
$statement = $pdo->prepare($sql);

//Execute the statement.
$statement->execute();

An explanation of the code above:

  1. We created a “Truncate Table” SQL statement. In this example, we are deleting everything from a table called “videos”.
  2. We prepared the SQL statement.
  3. Finally, we executed the statement and emptied the table.

Note that there are a number of differences between using the TRUNCATE statement and deleting all rows with the DELETE statement:

  1. The TRUNCATE TABLE statement is faster if you’re dealing with large tables.
  2. To empty a MySQL table using this method, your user account will need to have the DROP privilege.
  3. You cannot roll back a TRUNCATE TABLE statement.
  4. MySQL will reset the AUTO_INCREMENT value of the table. This means that the autoindex value of the next primary key will be 1. This is not the case with DELETE.
  5. Behind the scenes, this statement is dropping the table and then recreating it.
  6. This statement will not return any meaningful data. In other words, it will not return the number of rows that have been deleted. It will tell you that 0 rows have been affected.