Khóa học miễn phí MySQL – Truncate Tables nhận dự án làm có lương

MySQL – Truncate Table

Table of content


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.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc