Detailed explanation of MySQL's DELETE data deletion example

MySQLDELETEStatement is used to delete records from database tables. andUPDATEThe statement is similar,DELETEStatements are also very powerful and support a variety of usages and options. This article will introduce in detailDELETEBasic syntax, advanced usage, performance optimization strategies and precautions for statements.

1. Basic syntax

Single table deletion

The basic syntax for single table deletion is as follows:

• LOW_PRIORITY: If specifiedLOW_PRIORITYOptions, thenDELETEThe operation is delayed until no other client is reading data from the table.
• QUICK: Applicable only to MyISAM storage engine. Deletion operations will not merge the index end nodes of the deleted tables, thereby speeding up deletion.
• IGNORE: If specifiedIGNOREOptions, then when an error is encountered (such as foreign key constraint conflict),DELETEThe operation will not be interrupted, but will issue a warning.
• table_name: The name of the table to which the record is to be deleted.
• WHERE condition: Optional, used to specify which rows should be deleted. If notWHEREclause, then all rows in the table will be deleted.
• ORDER BY …: Optional, used to specify the order in which rows are deleted.
• LIMIT row_count: Optional, used to limit the maximum number of rows to be deleted.

Example

-- Delete table students middle id for 1 Records of
DELETE FROM students
WHERE id = 1;
-- Delete table students middle所有记录
DELETE FROM students;

2. Advanced usage

Use subquery to delete

-- Delete table students middle class_id For the table classes middle name for 'Mathematics Class' of class_id of记录
DELETE FROM students
WHERE class_id = (SELECT id FROM classes WHERE name = 'Mathematics Class');

Delete multiple tables

-- Delete table orders and order_details The total amount of orders is greater than 1000 Records of
DELETE o, od
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
WHERE o.total_amount > 1000;

useORDER BYandLIMIT

-- Delete table students The first in descending order of age 3 Famous student
DELETE FROM students
ORDER BY age DESC
LIMIT 3;

Delete duplicate records

-- Delete table students Repeated in email Record，reserve id 更小的一条Record
DELETE e1
FROM students e1, students e2
WHERE  >  AND  = ;

3. Performance optimization strategy

Using indexes

existWHEREUsing index fields in clauses can significantly speed up data retrieval. Make sure that the fields in the delete condition have appropriate indexes.

-- Assumptions id Fields have indexes
DELETE FROM students
WHERE id = 1;

Batch Delete

If you need to delete multiple records, you can consider adding multiple recordsDELETECombine statements into one to reduce transaction overhead.

-- Delete multiple records in batches
DELETE FROM employees
WHERE id IN (1, 2, 3);

Avoid deletion of all tables

Try to avoid not bringingWHEREClause ofDELETEstatement, because this will cause the entire table to be deleted and consume a lot of resources.

-- Avoid this writing
DELETE FROM employees;

useTRUNCATEClear the table

If you need to delete all records in the table, you can useTRUNCATEstatement, it isDELETEStatements are faster, but cannot be used in transactions, nor can they be used with locks on tables.

-- Clear the table students
TRUNCATE TABLE students;

Optimize transactions

For large-batch deletion operations, you can consider performing deletion in batches, and manually submitting transactions after each batch is deleted to avoid long-term table locking.

START TRANSACTION;
DELETE FROM employees
WHERE id BETWEEN 1 AND 1000;
COMMIT;
START TRANSACTION;
DELETE FROM employees
WHERE id BETWEEN 1001 AND 2000;
COMMIT;

4. Things to note

• Backup data: Before performing large-scale or important deletion operations, it is recommended to back up data first.
• Usage transactions: For complex deletion operations, it is recommended to use transactions to ensure data consistency and integrity.
• Performance considerations: When deleting a large amount of data, the use of indexes and the impact of locking mechanisms should be considered.
• Data consistency: Ensure that the deletion operation does not cause data inconsistency or violate business rules.

5. Practical examples

Suppose we have oneemployeesTable, containing the following fields:id, name, salary, department_id. Here are some practical examples:

Delete records from specific employees

-- delete id for 1 The employee records
DELETE FROM employees
WHERE id = 1;

Delete multiple employee records

-- Delete the department as 10 Records of all employees
DELETE FROM employees
WHERE department_id = 10;

Delete employee records and keep one

-- Delete table employees Repeated in email Record，reserve id 更小的一条Record
DELETE e1
FROM employees e1, employees e2
WHERE  >  AND  = ;

Use subqueries to delete employee records

-- Delete table employees middle department_id For the table departments middle name for 'R&D Department' of department_id of记录
DELETE FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'R&D Department');

useORDER BYandLIMITDelete records

-- Delete table employees The first in descending order of wages 3 Famous employees
DELETE FROM employees
ORDER BY salary DESC
LIMIT 3;

6. Summary

MySQLDELETEStatements are an indispensable part of database operations. By using indexes reasonably, batch deletion, avoiding the deletion of all tables, and using them.TRUNCATE,useORDER BYandLIMITAnd optimize transactions can significantly improveDELETEThe execution efficiency of the statement.

This is the end of this article about the detailed explanation of MySQL's DELETE data deletion. For more related contents of mysql delete data deletion, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!