Want to use SQL table count() value once without getting an updated value-CodePudding

I want to use count() value only once, it should not update values when new records comes.

For example: if Table1 has a column ABC, so SELECT COUNT(ABC) FROM TABLE1. This query will give the count every time new records added in the table. But i want to get the old value not the new one added in it.

If you know how can i do it ?

CodePudding user response：

If your table Table1 has columns like id(autoincrement) or created_at(the timestamp or datetime that the row created), then you can use the query SELECT COUNT(*) FROM TABLE1 WHERE id < *** OR SELECT COUNT(*) FROM TABLE1 WHERE created_at < *** to get the old value of rows count.

CodePudding user response：

At first, it may look tempting to mark a spot as a checkpoint so you know where you stopped last time. e.g select count(*) ,max(id) from terms; But what happens if the id values before the max(id) change or get removed? Then the count result before the checkpoint will most likely be inconsistent with the previous attempt. Personally, I would suggest storing the count result into a table with a timestamp so you know the time when you did the counting. You can add columns to store things like stopped_at_id as a reference for a particular counting, but not as a fail-safe checkpoint.

create table count_record(
    count_id int primary key auto_increment,
    count_result int,
    counted_at timestamp
);
insert count_record (count_result) values ((select count(*) from terms)) ;