Use of MySQL truncate table statement

Use of MySQL truncate table statement

The Truncate table statement is used to delete/truncate all data in the table.

  • This is the same as delete, which means deleting all table data, but has faster performance.
  • Similar to executing the drop table and create table statements

Executing Code

mysql> select * from students_bak;
+-----+----------+--------+---------+
| sid | sname | gender | dept_id |
+-----+----------+--------+---------+
| 101 | zhangsan | male | 10 |
| 1 | aa | 1 | 1 |
+-----+----------+--------+---------+
2 rows in set (0.00 sec)

mysql> truncate table students_bak;
Query OK, 0 rows affected (0.16 sec)

mysql> select * from students_bak;
Empty set (0.00 sec)

mysql> set autocommit=off;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from students3;
+-----+-------+--------+---------+--------+
| sid | sname | gender | dept_id | sname2 |
+-----+-------+--------+---------+--------+
| 100 | NULL | 1 | 1 | NULL |
+-----+-------+--------+---------+--------+
1 row in set (0.01 sec)

mysql> truncate table students3;
Query OK, 0 rows affected (0.06 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from students3;
Empty set (0.00 sec)

mysql> delete from students;
Query OK, 5 rows affected (0.00 sec)

mysql> select * from students;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from students;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
| 1 | aa | 3 | 1 |
| 4 | cc | 3 | 1 |
| 5 | dd | 1 | 2 |
| 6 | aac | 1 | 1 |
| 10 | a | 1 | 1 |
+-----+-------+--------+---------+
5 rows in set (0.00 sec)

What permissions does truncate need?

The execution of truncate is drop first and then create, so truncate includes drop and create, which is a compound action. You don't need to grant create permission, so you only need to grant drop permission.

This is the end of this article about the use of MySQL truncate table statement. For more relevant MySQL truncate table content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Detailed explanation of Truncate usage in MYSQL
  • MySQL uses the truncate command to quickly clear all tables in a database
  • Detailed explanation of MySQL Truncate usage
  • Data recovery case after truncate error operation in MySQL
  • mysql delete operation (delete+TRUNCATE)
  • Comparison of MySQL Delete and Truncate statements

<<:  DELL R730 server configuration RAID and installation server system and domain control detailed graphic tutorial

>>:  Detailed explanation of eight methods to achieve CSS page bottom fixed

Recommend

Detailed explanation of how to select all child elements using CSS

How to recursively select all child elements usin...

Practical method of upgrading PHP to 5.6 in Linux

1: Check the PHP version after entering the termi...

Example code for implementing a simple search engine with MySQL

Table of contents Preface Introduction ngram full...

JS ES new feature of variable decoupling assignment

Table of contents 1. Decoupled assignment of arra...

VUE+Canvas implements the sample code of the desktop pinball brick-breaking game

Everyone has played the pinball and brick-breakin...

A brief analysis of different ways to configure static IP addresses in RHEL8

While working on a Linux server, assigning static...

How to install Docker and configure Alibaba Cloud Image Accelerator

Docker Installation There is no need to talk abou...

Detailed explanation of the use of Vue3 state management

Table of contents background Provide / Inject Ext...

Summary of methods to check whether the port is open in Linux

Method 1: Use lsof command We can use the lsof co...

How to add Tomcat Server configuration to Eclipse

1. Window -> preferences to open the eclipse p...

JavaScript implements fireworks effects with sound effects

It took me half an hour to write the code, and th...

Detailed explanation of views in MySQL

view: Views in MySQL have many similarities with ...

CSS hacks \9 and \0 may not work for hacking IE11\IE9\IE8

Every time I design a web page or a form, I am tr...