Shulou(Shulou.com)05/31 Report--

This article will explain in detail the method of emptying the data table by mysql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

In mysql, you can use the "DELETE" and "TRUNCATE" keywords to empty the data in the data table, with the syntax "DELETE FROM data Table;" and "TRUNCATE TABLE data Table;".

MySQL provides the DELETE and TRUNCATE keywords to delete data from the table.

MySQL DELETE keyword

In MySQL, you can use the DELETE statement to delete one or more rows of data from a table.

Use the DELETE statement to delete data from a single table in the syntax format:

DELETE FROM [WHERE clause] [ORDER BY clause] [LIMIT clause]

The syntax is as follows:

Specifies the name of the table to delete the data

ORDER BY clause: optional. When you delete, the rows in the table are deleted in the order specified in the clause.

WHERE clause: optional. Indicates that the deletion condition is qualified for the delete operation, and if the clause is omitted, all rows in the table are deleted.

LIMIT clause: optional. Used to tell the server the maximum value of the deleted row before the control command is returned to the client.

Note: when the WHERE condition is not used, all data will be deleted.

Delete all data in the table

Example: delete all data in the tb_courses table

Mysql > DELETE FROM tb_courses;Query OK, 3 rows affected (0.12 sec) mysql > SELECT * FROM tb_courses;Empty set (0.00 sec)

MySQL TRUNCATE keyword

The TRUNCATE keyword is used to completely empty a table. The syntax format is as follows:

TRUNCATE [TABLE] Table name

The TABLE keyword can be omitted.

Example: use the TRUNCATE statement to clear records in the tb_student_course table

Mysql > TRUNCATE TABLE tb_student_course;Query OK, 0 rows affected (0.04 sec) mysql > SELECT * FROM tb_student_course;Empty set (0.00 sec)

The difference between TRUNCATE and DELETE

Logically, the TRUNCATE statement works the same as the DELETE statement, but in some cases there is a difference in usage.

DELETE is a statement of type DML; TRUNCATE is a statement of type DDL. They are all used to empty the data in the table.

DELETE deletes records row by row, while TRUNCATE deletes the original table directly and recreates a new table that is exactly the same, rather than deleting the data in the table row by row, which is faster than DELETE. Therefore, when you need to delete all data rows in the table, use TRUNCATE statements as much as possible, which can shorten the execution time.

After DELETE deletes data, it can be recovered with event rollback. TRUNCATE does not support transaction rollback, and data cannot be recovered after deletion.

After DELETE deletes data, the system does not reset the counter for the self-increment field; after TRUNCATE clears the table record, the system resets the counter for the self-increment field.

DELETE is more widely used because it can delete part of the data by specifying conditions through the WHERE clause, while TRUNCATE does not support the WHERE clause and can only delete the whole.

DELETE returns the number of rows deleted, but TRUNCATE only returns 0, which doesn't make any sense.

This is the end of this article on "the method of mysql emptying data sheet data". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.