38

I have an ID field that is my primary key and is just an int field.

I have less than 300 rows but now every time someone signs up that ID auto inc is inputted really high like 11800089, 11800090, etc.... Is there a way to get that to come back down so it can follow the order (310,311,312).

Thanks!

Kristijan Iliev
  • 4,901
  • 10
  • 28
  • 47
Chris Olson
  • 1,021
  • 4
  • 12
  • 19
  • Maybe you should tell us what database you're using and what tool you're using to access it? – Dan Nov 17 '11 at 03:37
  • Sorry Dan, it's a mysql database. I am just inputing new users through a registration form that uses a php insert. – Chris Olson Nov 17 '11 at 03:49

6 Answers6

61
ALTER TABLE table_name AUTO_INCREMENT=310;

Beware though, you don't want to repeat an ID. If the numbers are that high, they got that way somehow. Be very sure you don't have associated data with the lower ID numbers.

https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

Rajat
  • 2,467
  • 2
  • 29
  • 38
Brad
  • 159,648
  • 54
  • 349
  • 530
  • MySQL seems to go overboard **protecting you from ID collisions**. It actually sets this internal value to the maximum + 1 of all the IDs in the database *and* the value you supply here. So you can't for example hope to have a special ID 999999999 but autoincrement from 100. – Bob Stein Mar 31 '13 at 14:12
  • 6
    Notice if you using db engine inodb you have to restart mysql server. Otherwise the new auto increment value will be ignored. That is because the increment value is cached and not manually changeable at runtime! – sensi Nov 08 '13 at 17:29
  • 2
    It's also worth noting that the value set is the ID that you would like to allocate next; i.e. if your last row is ID #5, set it to 6. – Luke Briggs Jul 28 '14 at 20:02
  • Indeed, as BobStein says setting `AUTO_INCREMENT=1` will only set that value if the table is empty. This means that you don't have to worry about what value you write, just set it to 1 or even 0 and it will become the minimum possible based on the rows already present. I do this whenever I delete a row. – JonP Feb 09 '18 at 12:17
  • @JonP You're reusing IDs, even after deleted? That's asinine. – Brad Feb 09 '18 at 16:35
  • @Brad There are good reasons to reuse IDs, e.g. to prevent ID overflow. – JonP Feb 09 '18 at 16:48
  • @JonP That's not a thing. Are you using Int16 for your ID column? What sort of universe do you work in where the 5,316,911,983,139,663,491,615,228,241,121,400,000 possible GUIDs aren't enough? Are you assigning individual IDs to every molecule on the planet? – Brad Feb 09 '18 at 17:27
  • @Brad Well, legacy databases... The OP gives another good reason to reuse IDs, do you think he is asinine too? – JonP Feb 09 '18 at 17:48
  • @JonP That depends. As I pointed out in my answer, messing with this requires caution. There's little context in the question, so I had originally assumed that there was a one-time accident. In this case, a little well-controlled database repair work is probably okay. But, anything like this is dangerous. I'm calling you out specifically because you're giving terrible advice to people on Stack Overflow who often don't know any better. I've heard the "I don't want to run out of IDs" argument before from people with like 35 records. You're reinforcing it. – Brad Feb 09 '18 at 18:08
2

The auto increment counter for a table can be (re)set two ways:

  1. By executing a query, like others already explained:

    ALTER TABLE <table_name> AUTO_INCREMENT=<table_id>;

  2. Using Workbench or other visual database design tool. I am gonna show in Workbench how it is done - but it shouldn't be much different in other tool as well. By right click over the desired table and choosing Alter table from the context menu. On the bottom you can see all the available options for altering a table. Choose Options and you will get this form: enter image description here

    Then just set the desired value in the field Auto increment as shown in the image. This will basically execute the query shown in the first option.

Kristijan Iliev
  • 4,901
  • 10
  • 28
  • 47
2

There may be a quicker way, but this is how I would do it to be sure I am recreating the IDs;

If you are using MySQL or some other SQL server, you will need to:

  1. Backup your database
  2. Drop the id column
  3. Export the data
  4. TRUNCATE or 'Empty' the table
  5. Recreate the id column as auto_increment
  6. Reimport the data

This will destroy the IDs of the existing rows, so if these are important, it is not a viable option.

ctrl_freak
  • 137
  • 1
  • 1
  • 7
0

I was playing around on a similar problem and found this solution:

SET @newID=0;
UPDATE `test` SET ID=(@newID:=@newID+1) ORDER BY ID;
SET @c = (SELECT COUNT(ID) FROM `test`);
SET @s = CONCAT("ALTER TABLE `test` AUTO_INCREMENT = ",@c);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I hope that helps someone in a similar situation!

Harald Wirth
  • 25
  • 3
0

I believe that mysql does a select max on the id and puts the next. Try updating the ids of your table to the desired sequence. The problem you will have is if they're linked you should put a Cascade on the update on the fk. A query that comes to my mind is:

UPDATE Table SET id=(SELECT max(id)+1 FROM TAble WHERE id<700)

700 something less than the 11800090 you have and near to the 300 WHERE id>0;

I believe that mysql complaints if you don't put a where

Jubin Patel
  • 1,959
  • 19
  • 38
marspzb
  • 364
  • 1
  • 8
  • FYI, MySQL does **not** do a `SELECT MAX()`. The next auto-increment ID is stored with the table definition. – Brad Nov 17 '11 at 03:45
  • In Inno db it initializes like that http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable. MyISam is different I didn't knew that. Thanks – marspzb Nov 17 '11 at 04:06
0

Guessing that you are using mysql because you are using PHP. You can reset the auto_increment with a statement like

 alter table mytable autoincrement=301;

Be careful though - because things will break when the auto inc value overlaps

Adrian Cornish
  • 23,227
  • 13
  • 61
  • 77