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

Summary of four ways to introduce CSS (sharing)

1. Inline reference: used directly on the label, ...

Solution to find all child rows for a given parent row in MySQL

Preface Note: The test database version is MySQL ...

Configure Java development environment in Ubuntu 20.04 LTS

Download the Java Development Kit jdk The downloa...

Introduction to the use and difference between in and exists in MySQL

First put a piece of code for(int i=0;i<1000;i...

Getting started with JavaScript basics

Table of contents 1. Where to write JavaScript 2....

Sharing of SVN service backup operation steps

SVN service backup steps 1. Prepare the source se...

Chrome plugin (extension) development guide (complete demo)

Table of contents Written in front Preface What i...

Vue custom encapsulated button component

The custom encapsulation code of the vue button c...

MySQL Database Basics: A Summary of Basic Commands

Table of contents 1. Use help information 2. Crea...

Some issues we should pay attention to when designing a web page

Web design, according to personal preferences and ...

MySQL NULL data conversion method (must read)

When using MySQL to query the database and execut...

In-depth understanding of the vertical-align property and baseline issues in CSS

vertical-align attribute is mainly used to change...

26 Commonly Forgotten CSS Tips

This is a collection of commonly used but easily ...

Detailed explanation of MySQL Group by optimization

Table of contents Standard execution process opti...