Thân cây MySQL

The TRUNCATE statement in MySQL removes the complete data without removing its structure. It is a part of DDL or data definition language command. Generally, we use this command when we want to delete an entire data from a table without removing the table structure

The TRUNCATE command works the same as a DELETE command without using a WHERE clause that deletes complete rows from a table. However, the TRUNCATE command is more efficient as compared to the DELETE command because it removes and recreates the table instead of deleting single records one at a time. Since this command internally drops the table and recreates it, the number of rows affected by the truncate statement is zero, unlike the delete statement that returns the number of deleted rows

This command does not maintain the transaction log during the execution. It deallocates the data pages instead of rows and makes an entry for the deallocating pages instead of rows in transaction logs. This command also locks the pages instead of rows; thus, it requires fewer locks and resources

The following points must be considered while using the TRUNCATE command

• We cannot use the WHERE clause with this command so that filtering of records is not possible
• We cannot rollback the deleted data after executing this command because the log is not maintained while performing this operation
• We cannot use the truncate statement when a table is referenced by a foreign key or participates in an indexed view
• The TRUNCATE command doesn't fire DELETE triggers associated with the table that is being truncated because it does not operate on individual rows

cú pháp

The following syntax explains the TRUNCATE command to remove data from the table

In this syntax, first, we will specify the table name which data we are going to remove. The TABLE keyword in the syntax is not mandatory. But it's a good practice to use it to distinguish between the TRUNCATE() function and the TRUNCATE TABLE statement

MySQL Truncate Table Example

Let us demonstrate how we can truncate the table with the help of an example. First, we are going to create a table named "customer" using the below statement

Next, we will add values to this table using the below statement

Now, verify the table by executing the SELECT statement whether the records inserted or not

We will get the output, as shown below

Now, execute the following statement that truncates the table customer using the TRUNCATE syntax discussed above

After the successful execution, we will get the following output

As we can see, this query returns 0 rows are affected even if all the table records are deleted. We can verify the deletion of the data by executing the SELECT statement again. This command gives the following output that shows none of the records present in the table

How to Truncate Table with Foreign key?

If we perform the TRUNCATE operation for the table that uses a foreign key constraint, we will get the following error

In that case, we need to log into the MySQL server and disable foreign key checks before executing the TRUNCATE statement as below

Now, we are able to truncate tables. After execution, re-enable foreign key checks as given below

How to truncate all tables in MySQL?

The TRUNCATE statement in MySQL will delete only one table at a time. If we want to delete more than one table, we need to execute the separate TRUNCATE statement. The below example shows how to truncate multiple tables in MySQL

We can also use the below SQL query that generates several TRUNCATE TABLE commands at once using the table names in our database

Tóm lược. in this tutorial, you will learn how to use the MySQL

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Introduction to the MySQL CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL ) ENGINE=INNODB;Code language: SQL (Structured Query Language) (sql)4 statement

The MySQL

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Logically, the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

However, the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Here is the basic syntax of the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

TRUNCATE [TABLE] table_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the table which you want to remove all data after the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

The

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

DELIMITER $$
CREATE PROCEDURE load_book_data(IN num INT(4))
BEGIN
	DECLARE counter INT(4) DEFAULT 0;
	DECLARE book_title VARCHAR(255) DEFAULT '';

	WHILE counter < num DO
	  SET book_title = CONCAT('Book title #',counter);
	  SET counter = counter + 1;

	  INSERT INTO books(title)
	  VALUES(book_title);
	END WHILE;
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

If there is any

DELIMITER $$
CREATE PROCEDURE load_book_data(IN num INT(4))
BEGIN
	DECLARE counter INT(4) DEFAULT 0;
	DECLARE book_title VARCHAR(255) DEFAULT '';

	WHILE counter < num DO
	  SET book_title = CONCAT('Book title #',counter);
	  SET counter = counter + 1;

	  INSERT INTO books(title)
	  VALUES(book_title);
	END WHILE;
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Because a truncate operation causes an implicit commit, therefore, it cannot be rolled back

The

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

DELIMITER $$
CREATE PROCEDURE load_book_data(IN num INT(4))
BEGIN
	DECLARE counter INT(4) DEFAULT 0;
	DECLARE book_title VARCHAR(255) DEFAULT '';

	WHILE counter < num DO
	  SET book_title = CONCAT('Book title #',counter);
	  SET counter = counter + 1;

	  INSERT INTO books(title)
	  VALUES(book_title);
	END WHILE;
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

The

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Unlike a

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

MySQL CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL ) ENGINE=INNODB;Code language: SQL (Structured Query Language) (sql)4 example

Let’s take an example of using the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

First, create a new table named

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Next, insert dummy data to the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Then, load 10,000 rows into the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

After that, check the data in the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Finally, use the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Note that you can compare the performance between the

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MySQL

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
)  ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

What is TRUNC in MySQL?

How to use TRUNC in MySQL?

How to TRUNCATE a column in MySQL?

What is the use of TRUNCATE ()?