mysql advanced (twenty-one) deleting table data

MySQL delete table data

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

If you want to clear all records in the 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 the table, you can only use the DELETE statement.

　      DELETE FROM table1 WHERE ...;

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

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

      DELETE FROM table1 WHERE 1;

The above statement will scan each record when executed. But it doesn't compare because the WHERE condition is always true. Although this can maintain the maximum auto-increment value, since it scans all records, 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, the mysql log will not be recorded and the data cannot be restored. The effect of delete is a bit like deleting all the records in the mysql table one by one until the deletion is complete, while truncate is equivalent to retaining the structure of the mysql table and re-creating the table. All states are equivalent to a new table. It can also return the number of deleted records. TRUNCATE TABLE cannot delete specified records, and cannot return deleted records. But it executes very quickly.

Unlike standard SQL statements, DELETE supports ORDER BY and LIMIT clauses. Through these two clauses, we can better control the records to be deleted. For example, when we only want to delete a part of the records filtered 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 whose 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 of the six deleted records are. To be more secure, we can use ORDER BY to sort the records.

      DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;

美文美图

The above is the content of mysql advanced (21) deleting table data, more related Please pay attention to the PHP Chinese website (www.php.cn) for content!