occasionally , More data leads to trouble . Remove the related table data in the library ( Storage in other warehouses ) It may be necessary work to be done on a regular basis .

The basic steps are as follows :

1. Export scheduled in table data

2. Import to the specified library

3. Remove table data from original database

Is it simple . We do it step by step .

1. Export scheduled in table data

generally speaking , Most tables do not export all of the data , Data that meets the criteria is retained . commonly , Time conditions are more common . such as : Request for retention 2 Data within months .

We use mysqldump This tool can export the specified table and satisfy the specified logical conditions . Here is an example :

mysqldump -uroot -p testdb  testtable --where="id<=125443450" >

The command above is export testdb In the library testtable In the table id Less than or equal to 125443450 All data of .

This command will include creating tables testtable And insert data into testtable Table commands .

If more than one table needs to be exported , Just refer to the order above , Just write more .

Pay attention here , It is common to use time as a requirement to retain data . But use time as a condition ( especially datetime Type ) There may be more or less data ( Measured results ,mysql
5.6, Other versions not detected ). therefore , It is suggested to use time condition to display some data first 10-20 It's OK , Then confirm the last data , And take the id( Self growing field ) Operate as a derived data condition .

2. Import to the specified library

This implementation is simpler , use mysql The tool can be implemented easily . Here is an example :

mysql -uroot -p testdbbak < testtable201901.sql

There are multiple table files that can refer to the above command .

3. Remove table data from original database

The first two steps are very simple to implement .

This step is supposed to be very simple , But being bold and practical can drive you crazy .

Methods for removing table data mysq There are 2 individual ,delete and truncate table

truncate table The command to clear all data in the table .

It is characterized by high speed . If all the data are confirmed, do not , This is the best .

If it is a partial deletion , that truncate table It's not suitable .

In the case of less deleted data ,delete Nothing bad .

If there are more , such as 500 10000 pieces of data , Then you may face the following problems :

a. Longer execution time

b. Index efficiency becomes low

c. Disk space not freed

I've met enforcement delete Over time 40 Minutes ....

In the presence of bc After the situation , It is usually necessary to optimize the table , This is another long process of implementation ...

In view of the problems described above , If it is time to delete more data , Not recommended delete To achieve .

One of the suggestions given here is as follows , For your reference :

use mysqldump Export the data that needs to be preserved in the table , After that, the exported data is immediately restored to the original table .

mysqldump -uroot -p chickens  testdb testtable --where="id>125443450" >
mysql  -uroot -p testdb< testtable201901.sql

Why is this program better than direct use delete good ?

Export first and then import , The essence is to delete the original table, then rebuild and re insert the data . It avoided delete Problems caused by indexing tables , There is no need to optimize later . that is bc The problem is gone .

Then the execution time and delete Less than that one ? This is not a simple answer , Although my test result is delete Longer time .

This mainly depends on the amount of data to be deleted and retained . Accurate data cannot be given here for the time being , But when the two are similar ,delete It will be longer .

As for why , Interested friends can study on their own .

There are other issues that need further discussion , Time limit to be continued later .

©2019-2020 Toolsou All rights reserved,
In the problem of target detection “ recall Recall”,“ Accuracy Precision”Linux Page replacement algorithm C Language implementation About wechat payment body( Product description ) Problems encountered stay Vue Use in Web Worker Calculate the working days of each month c++ Memory Pointer out of bounds detection mechanism _CrtMemBlockHeadeJavaScript Medium Call and Apply Remember once EventBus Project issues caused by memory leaks Hundreds of millions of locusts rarely collide Locusts want to be self driving Heroes Java Swing JList: List box components