CODEWITHSUNDEEP
mysql
Sajal
Sajal

Reputation: 1216

Reset Auto Increment in Mysql

I don't know how Auto Increment is set to 10. Every record inserted is incremented by 10. I fired this query - SHOW VARIABLES LIKE 'auto_inc%';

output is -

auto_increment_increment    10

auto_increment_offset   3

I also tried resetting it - SET @@auto_increment_increment=10; but cannot change it.

How to get rid of it? I have 2,700,000+ records in one table and it will go on increasing.

This database is hosted on a remote mysql. Can i change this auto_increment myself or will have to ask server provider.

Upvotes: 1

Views: 1893

Answers (3)

Rameez
Rameez

Reputation: 1712

To start with an AUTO_INCREMENT value other than 1, you can set that value with ALTER TABLE, like this:

ALTER TABLE tbl AUTO_INCREMENT = 100;

To set intervals of increment:

SET @@auto_increment_increment=1;

Upvotes: 0

Stephan
Stephan

Reputation: 8090

You need to change the value of auto_increment_increment:

SET auto_increment_increment = 1;

Upvotes: 1

Barmar
Barmar

Reputation: 782488

You can reset them with the set command:

set global auto_increment_increment = 1;
set global auto_increment_offset = 1

Upvotes: 5

Related Questions