截断并插入问题

Question

I am currently working on a script which has a big database so to decrease load i built a table which contain a data that is updated every minute(CRON PHP FILE).So it works just fine but i am having right now an issue. The code I am using:

$conn->query("TRUNCATE tempo");
 $conn->query("INSERT INTO ....");

and the php file i get the data from the tempo table the problem right now is that when php runs every minute there is a short delay like 1 second between data delete and data insert so in this 1 second the table tempo is empty so every minute i have 1second in which the website just return 0 data (because of the delay). How can I solve this problem ?

Answer 1

If you cannot have any downtime in accessing the data, then have 2 tables with identical structure, I'll refer to them as table_A and table_B. At any time, only one of them should be "live", meaning one will serve all requests for the data. Let's say it is table_A at the moment.

When you do the import from the cron, then truncate table_B and insert the data into table_B. When the insertion into table_B is done, then mark table_B as the live one and the next import's target should be table_A.

This way you are not going to have any downtime accessing your data.