Eitan
Eitan

Reputation: 1386

mysql change autoincrement to different value

In a table in mysql db (version: 5.7.37) I have an auto-increment field (id column).

The table is a huge one, but sometime we delete records (so id of 1 million is not 1 millions records) - still a huge one after the deletion.

Sometime the id reaches the maximum integer value (2147483647).

I won't change to bigint (it is complicated - there is limitation in code also).

I want to do it cyclic - when it reaches 2147483647, the next value will be 1, so I want to do:

alter table mytable AUTO_INCREMENT=1;

That may seems work with no any exception (for a fraction of second), but when I do:

SELECT `AUTO_INCREMENT` 
  FROM  INFORMATION_SCHEMA.TABLES t 
  WHERE TABLE_SCHEMA = 'myschema' 
  AND   TABLE_NAME   = 'mytable';

I see that the AUTO_INCREMENT for above is still of value: 2147483647 and not of value 1.

If I do something like:

SET FOREIGN_KEY_CHECKS = 0;
set unique_checks=0;
alter table mytable
  change column `id` `id` int(11) NOT NULL;
set unique_checks=1;
SET FOREIGN_KEY_CHECKS = 1;  

SET FOREIGN_KEY_CHECKS = 0;
set unique_checks=0;
alter table mytable
  change column `id` `id` int(11) NOT NULL AUTO_INCREMENT;
SET FOREIGN_KEY_CHECKS = 1;  
SET unique_checks=1;


alter table paranoid_zero AUTO_INCREMENT=1;

The above take too long time (few minutes), and still AUTO_INCREMENT value checked by query:

SELECT `AUTO_INCREMENT` 
  FROM  INFORMATION_SCHEMA.TABLES t 
  WHERE TABLE_SCHEMA = 'myschema' 
  AND   TABLE_NAME   = 'mytable';

is equal to 2147483647.

How can I update auto increment value (fast) in a proper way with mysql query.

Thanks.

Upvotes: 0

Views: 349

Answers (1)

Rick James
Rick James

Reputation: 142218

An AUTO_INCREMENT will not go below MAX(id). That is, if there are rows still in the table, you cannot change the value all the way down to 1. That is, if your DELETEs did not remove the row with ids near 2147483647, you would gain much breathing room.

You seem to have ``INT SIGNED. Changing to INT UNSIGNEDwould give you about 4 billion limit. But it will be just as slow as changing toBIGINT`.

Use IODKU instead of REPLACE. All flavors of INSERT will assign an id before realizing that it is not needed. Could this be 'burning' ids faster than you realize?

Faster DELETEs: http://mysql.rjweb.org/doc.php/deletebig
Batch Normalization
(And I may have other tips relating to your problem and its cause.

Upvotes: 2

Related Questions