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

Detailed explanation of the use of find_in_set() function in MySQL

First, let’s take an example: There is a type fie...

Detailed explanation of Javascript event capture and bubbling methods

Table of contents 1. Event Processing Model 1. Ev...

Detailed explanation of COLLATION examples in MySQL that you may have overlooked

Preface The string types of MySQL database are CH...

MySQL quickly inserts 100 million test data

Table of contents 1. Create a table 1.1 Create te...

Ubuntu 18.04 installs mysql 5.7.23

I installed MySQL smoothly in Ubuntu 16.04 before...

How to check if the firewall is turned off in Linux

1. Service method Check the firewall status: [roo...

Do you know how to use the flash wmode attribute in web pages?

When doing web development, you may encounter the...

Example of how to set up a Linux system to automatically run a script at startup

Preface Hello everyone, I am Liang Xu. At work, w...

How to analyze MySQL query performance

Table of contents Slow query basics: optimizing d...

Detailed explanation of how to implement secondary cache with MySQL and Redis

Redis Introduction Redis is completely open sourc...

MySQL loop inserts tens of millions of data

1. Create a test table CREATE TABLE `mysql_genara...

Native js to realize a simple snake game

This article shares the specific code of js to im...

Tutorial on installing PHP on centos via yum

First, let me introduce how to install PHP on Cen...

Explanation on whether to choose paging or loading in interactive design

The author of this article @子木yoyo posted it on hi...

js to achieve the effect of light switch

This article example shares the specific code of ...