How to delete table data in MySQL

How to delete table data in MySQL

There are two ways to delete data in MySQL, one is the DELETE statement and the other is the TRUNCATE TABLE statement. The DELETE statement can select the records to be deleted through WHERE. Using TRUNCATE TABLE will delete all records in the table. Therefore, the DELETE statement is more flexible.

If you want to clear all records in a table, you can use the following two methods:

DELETE FROM table1

TRUNCATE TABLE table1

The TABLE in the second record is optional.

If you want to delete some records in a table, you can only use the DELETE statement.

 DELETE FROM table1 WHERE ...;

If DELETE does not have a WHERE clause, it is the same as TRUNCATE TABLE, but there is one difference between them, that is, DELETE can return the number of deleted records, while TRUNCATE TABLE returns 0.

If a table has an auto-increment field, after deleting all records using TRUNCATE TABLE and DELETE without a WHERE clause, the starting value of the auto-increment field will be restored to 1. If you do not want to do this, you can add a permanent WHERE in the DELETE statement, such as WHERE 1 or WHERE true.

   DELETE FROM table1 WHERE 1;

The above statement will scan every record when executed. But it does not compare, because this WHERE condition is always true. Although this can maintain the maximum value of the auto-increment, it scans all records, so its execution cost is much greater than DELETE without a WHERE clause.

The biggest difference between DELETE and TRUNCATE TABLE is that DELETE can select the records to be deleted through the WHERE statement, but the execution speed is not fast.

After truncate is deleted, MySQL log is not recorded and data cannot be restored. The effect of delete is a bit like deleting all the records in the MySQL table one by one until all are deleted, while truncate is equivalent to retaining the structure of the MySQL table and recreating the table, and all states are equivalent to the new table. It can also return the number of deleted records. However, TRUNCATE TABLE cannot delete the specified records and cannot return the deleted records. But it executes very quickly.

Unlike standard SQL statements, DELETE supports ORDER BY and LIMIT clauses. With these two clauses, we can better control the records to be deleted. For example, if we only want to delete part of the records filtered out by the WHERE clause, we can use LIMIT. If we want to delete the last few records, we can use ORDER BY and LIMIT together. Suppose we want to delete the first 6 records in the users table where name is equal to "Mike". You can use the following DELETE statement:

   DELETE FROM users WHERE name = 'Mike' LIMIT 6;

Generally, MySQL is not sure which 6 records are deleted. To be safe, we can use ORDER BY to sort the records.
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;

Summarize

The above is all the content of this article about how to delete table data in MySQL. I hope it will be helpful to everyone. Interested friends can refer to: Detailed explanation of MySQL prepare principles, several important MySQL variables, analysis of key points of ORACLE SQL statement optimization technology, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time.

You may also be interested in:
  • Code for batch deleting data tables with the same prefix in Mysql in PHP
  • Detailed instructions and syntax for creating and deleting tables in MySQL
  • Getting Started with MySQL (IV) Inserting, Updating, and Deleting Data from a Table
  • How to delete a MySQL table
  • mysql drop database delete database command example explanation
  • Detailed explanation of how to find and delete duplicate data in MySQL and keep only one example
  • How to quickly delete all tables in MySQL without deleting the database
  • Comparison of MySQL Delete and Truncate statements
  • Summary of methods for deleting duplicate data in MySQL database
  • Mysql delete data and data table method example

<<:  Detailed explanation of upgrading Python and installing pip under Linux

>>:  CocosCreator general framework design resource management

Recommend

Using Openlayer in Vue to realize loading animation effect

Note: You cannot use scoped animations! ! ! ! via...

Description of meta viewport attribute in HTML web page

HTML meta viewport attribute description What is ...

TypeScript learning notes: type narrowing

Table of contents Preface Type Inference Truth va...

React uses emotion to write CSS code

Table of contents Introduction: Installation of e...

win10 mysql 5.6.35 winx64 free installation version configuration tutorial

mysql 5.6.35 winx64 free installation version con...

vue3 timestamp conversion (without using filters)

When vue2 converts timestamps, it generally uses ...

Detailed steps for porting busybox to build a minimal root file system

Busybox: A Swiss Army knife filled with small com...

MariaDB under Linux starts with the root user (recommended)

Recently, due to the need to test security produc...

Example of converting timestamp to Date in MySQL

Preface I encountered a situation at work: In the...

Tutorial on using Multitail command on Linux

MultiTail is a software used to monitor multiple ...

Install mysql 5.6 from yum source in centos7.4 system

System environment: centos7.4 1. Check whether th...