Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    25

    Unanswered: Setting Next Auto_Increment Value

    Hi,

    Is there a way to set the next auto_increment value into a mysql database?

    Thanks

    mvreade

  2. #2
    Join Date
    Nov 2002
    Posts
    32
    Good question. I don't know how you could insert your own autoincrement value, but you could find the next value to be used by using this snipet of code I found here.

    http://blog.jamiedoris.com/geek/560/

    Code:
    <?
    $tablename 		= "tablename";
    $next_increment 	= 0;
    $qShowStatus 		= "SHOW TABLE STATUS LIKE 'tablename'";
    $qShowStatusResult 	= mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . qShowStatus );
    
    
    while ($row = mysql_fetch_assoc($qShowStatusResult)) {
    	$next_increment = $row['Auto_increment'];
    }
    mysql_free_result($qShowStatusResult);
    
    echo "next increment number: [$next_increment]";
    ?>
    I have not tested this.

  3. #3
    Join Date
    Sep 2006
    Posts
    25
    There are various ways of getting the last ID, including Max() and Last_insert_id functions...

    How to set it, is what I'm struggling with...

    So what I ended up doing is a Dump, then I modified the following line:

    ") ENGINE=InnoDB AUTO_INCREMENT=5358 DEFAULT CHARSET=utf8;"

    by setting the AUTO_INCREMENT=1. Then imported the whole thing again.

    It works, though I'd love to know a more elegant way to do it.

    All the best,

    mvreade

  4. #4
    Join Date
    Jan 2008
    Location
    Kingdom of Sweden
    Posts
    11
    Can't you just do:

    ALTER TABLE table_name AUTO_INCREMENT=1; ?

    This will set the next autoincrement number to the current greatest number + 1.

  5. #5
    Join Date
    Sep 2006
    Posts
    25
    That's not really what I wanted though. I had a non-usual situation where a bunch of records had been imported and deleted. Before re-importing the data, I wanted to reset the next serial number to 1.

    mvreade

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    How are you deleting the records from the table?
    You should use TRUNCATE, which will reset the auto_increment value back to 0.

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Apologies just re-read what's going on. You're dumping with the AUTO_INCREMENT value set. Can you not dump without auto_increment appended? I'm sure that's an option...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •