MySQL – Truncate Table
Table of content
- MySQL TRUNCATE TABLE Statement
- TRUNCATE vs DELETE
- TRUNCATE vs DROP
- Truncating Table Using a Client Program
”;
MySQL TRUNCATE TABLE Statement
The MySQL TRUNCATE TABLE statement is used to delete only the data of an existing table, but not the table.
This command helps to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and hefty process.
You can delete a table using the DROP TABLE command, but be careful because it completely erases both data and the table”s structure from the database.
If you want to store some data again, you would need to re-create this table once again.
Syntax
Following is the basic syntax of the TRUNCATE TABLE statement −
TRUNCATE TABLE table_name
Where, table_name is the name of the table you need to delete all the records from.
Example
First of all, let us create a table with name CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now, we are inserting 7 records into the above-created table using the following INSERT statement −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ), (2, ''Khilan'', 25, ''Delhi'', 1500.00 ), (3, ''Kaushik'', 23, ''Kota'', 2000.00 ), (4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ), (5, ''Hardik'', 27, ''Bhopal'', 8500.00 ), (6, ''Komal'', 22, ''Hyderabad'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 );
Using the following query, we are displaying the records of CUSTOMERS table −
SELECT * FROM CUSTOMERS;
Following are the records of CUSTOMERS table −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
In the following query, we are using the TRUNCATE TABLE command to remove all the records in the CUSTOMERS table −
TRUNCATE TABLE CUSTOMERS;
Output
The records have been truncated from the CUSTOMERS table without any error.
Query OK, 0 rows affected (0.02 sec)
Verification
To verify whether the records have been truncated, let us retrieve the records using the following query −
SELECT * FROM CUSTOMERS;
As we can see the output below, there are no records present in the CUSTOMERS table. Thus, the records have been truncated.
Empty set (0.00 sec)
TRUNCATE vs DELETE
Following are some major differences between the TRUNCATE and DELETE commands, even though they work similar logically:
DELETE | TRUNCATE |
---|---|
The DELETE command in SQL removes one or more rows from a table based on the conditions specified in a WHERE Clause. |
The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met. |
It is a DML(Data Manipulation Language) command. |
It is a DDL(Data Definition Language) command. |
There is a need to make a manual COMMIT after making changes to the DELETE command, for the modifications to be committed. |
When you use the TRUNCATE command, the modifications made to the table are committed automatically. |
It deletes rows one at a time and applies some criteria to each deletion. |
It removes all of the information in one go. |
The WHERE clause serves as the condition in this case. |
There is no necessity of using a WHERE Clause. |
All rows are locked after deletion. |
TRUNCATE utilizes a table lock, which locks the pages so they cannot be deleted. |
It makes a record of each and every transaction in the log file. |
The only activity recorded is the deallocation of the pages on which the data is stored. |
It consumes a greater amount of transaction space compared to TRUNCATE command. |
It takes comparatively less amount of transaction space. |
If there is an identity column, the table identity is not reset to the value it had when the table was created. |
It returns the table identity to a value it was given as a seed. |
It requires authorization to delete. |
It requires table alter permission. |
When it comes to large databases, it is much slower. |
It is faster. |
TRUNCATE vs DROP
The TRUNCATE and DROP are two different commands. TRUNCATE just deletes the table”s records, whereas DROP command deletes the table entirely from the database.
However, there are still some differences between these commands, which are summarized in the following table −
DROP | TRUNCATE |
---|---|
The DROP command in SQL removes an entire table from a database including its definition, indexes, constraints, data etc. |
The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met and resets the table definition. |
It is a DDL(Data Definition Language) command. |
It is also a DDL(Data Definition Language) command. |
The table space is completely freed from the memory. |
The table still exists in the memory. |
All the integrity constraints are removed. |
The integrity constraints still exist in the table. |
Requires ALTER and CONTROL permissions on the table schema and table respectively, to be able to perform this command. |
Only requires the ALTER permissions to truncate the table. |
DROP command is much slower than TRUNCATE but faster than DELETE. |
It is faster than both DROP and DELETE commands. |
Truncating Table Using a Client Program
Besides truncating a table in a MySQL database with a MySQL query, we can also use a client program to perform the TRUNCATE TABLE operation.
Syntax
Following are the syntaxes to truncate a table from MySQL Database in various programming languages −
To truncate a table from MySQL database through a PHP program, we need to execute the Truncate Table statement using the mysqli function query() as −
$sql = "TRUNCATE TABLE table_name"; $mysqli->query($sql);
To truncate a table from MySQL database through a Node.js program, we need to execute the Truncate Table statement using the query() function of the mysql2 library as −
sql = "TRUNCATE TABLE table_name"; con.query(sql);
To truncate a table from MySQL Database through a Java program, we need to execute the Truncate Table statement using the JDBC function executeUpdate() as −
String sql = "TRUNCATE TABLE table_name"; statement.executeUpdate(sql);
To truncate a table from MySQL Database through a Python program, we need to execute the Truncate statement using the execute() function of the MySQL Connector/Python as −
sql = "TRUNCATE TABLE table_name"; cursorObj.execute(sql);
Example
Following are the programs −
$dbhost = ''localhost''; $dbuser = ''root''; $dbpass = ''password''; $dbname = ''TUTORIALS''; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf(''Connected successfully.
''); $sql = " TRUNCATE TABLE clone_table "; if ($mysqli->query($sql)) { printf("table truncated successfully.
"); } if ($mysqli->errno) { printf("table could not be truncated: %s
", $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
table truncated successfully.
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); //Creating a Database sql = "CREATE DATABASE testdb" con.query(sql); //Selecting a Database sql = "USE testdb" con.query(sql); //Creating table sql = "CREATE TABLE MyPlayers(ID INT,First_Name VARCHAR(255),Last_Name VARCHAR(255),Date_Of_Birth date,Place_Of_Birth VARCHAR(255),Country VARCHAR(255),PRIMARY KEY (ID));" con.query(sql); sql = "insert into MyPlayers values(1, ''Shikhar'', ''Dhawan'', DATE(''1981-12-05''), ''Delhi'', ''India''),(2, ''Jonathan'', ''Trott'', DATE(''1981-04-22''), ''CapeTown'', ''SouthAfrica''),(3, ''Kumara'', ''Sangakkara'', DATE(''1977-10-27''), ''Matale'', ''Srilanka''),(4, ''Virat'', ''Kohli'', DATE(''1988-11-05''), ''Delhi'', ''India''),(5, ''Rohit'', ''Sharma'', DATE(''1987-04-30''), ''Nagpur'', ''India''),(6, ''Ravindra'', ''Jadeja'', DATE(''1988-12-06''), ''Nagpur'', ''India''),(7, ''James'', ''Anderson'', DATE(''1982-06-30''), ''Burnley'', ''England'');" con.query(sql); //List of tables sql = "select * from MyPlayers;" con.query(sql, function(err, result){ if (err) throw err console.log("**MyPlayers Table:**") console.log(result); console.log("--------------------------"); }); //Truncating the records of Myplayers table sql = "TRUNCATE TABLE MyPlayers;" con.query(sql); //fetching the records of table after truncating sql = "select * from MyPlayers;" con.query(sql, function(err, result){ if (err) throw err console.log("**Myplayers table after truncating:**"); console.log(result); }); });
Output
The output produced is as follows −
Connected! -------------------------- **MyPlayers Table:** [ { ID: 1, First_Name: ''Shikhar'', Last_Name: ''Dhawan'', Date_Of_Birth: 1981-12-04T18:30:00.000Z, Place_Of_Birth: ''Delhi'', Country: ''India'' }, { ID: 2, First_Name: ''Jonathan'', Last_Name: ''Trott'', Date_Of_Birth: 1981-04-21T18:30:00.000Z, Place_Of_Birth: ''CapeTown'', Country: ''SouthAfrica'' }, { ID: 3, First_Name: ''Kumara'', Last_Name: ''Sangakkara'', Date_Of_Birth: 1977-10-26T18:30:00.000Z, Place_Of_Birth: ''Matale'', Country: ''Srilanka'' }, { ID: 4, First_Name: ''Virat'', Last_Name: ''Kohli'', Date_Of_Birth: 1988-11-04T18:30:00.000Z, Place_Of_Birth: ''Delhi'', Country: ''India'' }, { ID: 5, First_Name: ''Rohit'', Last_Name: ''Sharma'', Date_Of_Birth: 1987-04-29T18:30:00.000Z, Place_Of_Birth: ''Nagpur'', Country: ''India'' }, { ID: 6, First_Name: ''Ravindra'', Last_Name: ''Jadeja'', Date_Of_Birth: 1988-12-05T18:30:00.000Z, Place_Of_Birth: ''Nagpur'', Country: ''India'' }, { ID: 7, First_Name: ''James'', Last_Name: ''Anderson'', Date_Of_Birth: 1982-06-29T18:30:00.000Z, Place_Of_Birth: ''Burnley'', Country: ''England'' } ] -------------------------- **Myplayers table after truncating:** []
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TruncateTable { public static void main(String[] args){ String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try{ Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //truncate tables...! String sql = "TRUNCATE TABLE clone_tbl"; statement.executeUpdate(sql); System.out.println("Table Truncated successfully...!"); ResultSet resultSet = statement.executeQuery("SELECT * FROM clone_tbl"); while (resultSet.next()){ System.out.println(resultSet.getInt(1)+" "+resultSet.getString(2)+" "+ resultSet.getInt(3)+" "+ resultSet.getString(4)+" "+resultSet.getFloat(5)); System.out.println(); } connection.close(); } catch(Exception e){ System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! Table Truncated successfully...!
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) table_name = ''tutorials_tbl_cloned'' #Creating a cursor object cursorObj = connection.cursor() cursorObj.execute(f"TRUNCATE TABLE {table_name}") print(f"Table ''{table_name}'' is truncated successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
Table ''tutorials_tbl_cloned'' is truncated successfully.
”;