We have a table as below. While deleting the records,we will capture the records into this table. To generate the id value we are using ‘auto_increment’ column. We will truncate this table weekly once. How to reset the auto_increment values from the number 1 again whenever we truncated the table.(We don’t want to drop and recreate the table.)
CREATE TABLE IF NOT EXISTS endpoints_audit
(id int auto_increment ,
model varchar(100) not null,
.
.
.
.
.
.’
created_on timestamp default now(),
KEY (model,id) USING CLUSTERED COLUMNSTORE
)
MemSQL doesn’t support a way to reset the auto_increment values. Why don’t you want to drop the table and recreate it, given that you are deleting all the data anyway?
This post talks about how to create your own sequence generator using an auto_increment on a reference table, but of course it is not fully general. It might work for some apps.
Any update on AUTO_INCREMENT Value to reset features. I could not drop and recreate , i will have permission to truncate/delete the data.so i need to reset the value to 1 whenever i required.
We don’t support this. We have an open feature request for it. At some point I expect us to add sequences that could be reset and could be used to provide the next value for a table column. That would solve this problem for you. That’s the likely thing we’d do first.
You can adjust your next auto increment value but it must be higher than all the data and all the previously used values: