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

In-depth understanding of javascript prototype and prototype chain

Table of contents 1. What is a prototype? 2. Prot...

How to fix abnormal startup of mysql5.7.21

A colleague reported that a MySQL instance could ...

Discussion on the problem of garbled characters in iframe page parameters

I encountered a very unusual parameter garbled pro...

TypeScript Mapping Type Details

Table of contents 1. Mapped Types 2. Mapping Modi...

Display special symbols in HTML (with special character correspondence table)

Problem Reproduction When using HTML for editing,...

Detailed process of installing Presto and connecting Hive in Docker

1. Introduction Presto is an open source distribu...

Research on the value of position attribute in CSS (summary)

The CSS position attribute specifies the element&...

JavaScript Basics: Immediate Execution Function

Table of contents Immediately execute function fo...

Native js canvas to achieve a simple snake

This article shares the specific code of js canva...

Mysql database design three paradigm examples analysis

Three Paradigms 1NF: Fields are inseparable; 2NF:...

MySQL learning database operation DML detailed explanation for beginners

Table of contents 1. Insert statement 1.1 Insert ...

Vue3.0 implements the magnifying glass effect case study

The effect to be achieved is: fixed zoom in twice...