Mysql deletes some data from a large table.

We will use delete or truncate to clear table data when deleting general table data in mysql. However, if you encounter a large table, you will find this method a little difficult, the following example shows how to delete some data from a large mysql table.

Mysql normal table Deletion

Definition of the delete statement:

Most of the children who often deal with databases use delete statements to delete data. Now let's take a look at the definition of the delete statement.

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_definition]

[Order by...]

[LIMIT row_count]

Example

Delete from friends where user_name = 'simaopig ';

Truncate statement TRUNCATE [TABLE] tbl_name

Here is a simple example. to delete all records in the friends table, bKjia. c0m can use the following statement:

Truncate table friends;

However, when I deleted a large table, I found that the delete operation was not working, so do not consider adding indexes. In mysql, adding low_priorty, quick, and ignore to delete is not helpful either.

There is a solution to seeing the mysql documentation: http://dev.mysql.com/doc/refman/5.0/en/delete.html

If you are deleting tables rows from a large table, you may www. bKjia. c0m exceed the lock table size for an InnoDB table. to avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use Delete at all) might be helpful:

Select the rows not to be deleted into an empty table that has the same structure as the original table:

Insert INTO t_copy Select * FROM t Where ...;
Use rename table to atomically move the original table out of the way and rename the copy to the original name:

Rename table t TO t_old, t_copy TO t;
Drop the original table:

Drop TABLE t_old;

E is not good, simple translation:

When deleting multi-row data that meets the standards, innodb will exceed the lock table size limit. The solution to minimize the lock table time is as follows:

1. Select data that does not need to be deleted and store them in an empty table with the same structure.

2. Rename the original table and name the new table as the original table name.

3. Delete the original table

To sum up, you can use the new table, copy the data, delete the old table, and rename the data when deleting a part of the big table.