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,
Final review of database : Summary of comprehensive application questions Laplance operator ( Second derivative ) Simple learning of computer composition principle pyqt Button call python program _PyQt: Link button to function in program How much can you go up once you change jobs ? Today, I saw the ceiling of job hopping python in str Function usage _python in str Usage Summary of built-in functions MySQL trigger web The server nginx---linux Installation and deployment C++ Chapter V polymorphism exercises :( It's coming to an end )python Check built-in functions , How to check python Built in function