MySQL - Truncate Table

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);

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.