Setting Next Auto_Increment Value

mvreade · #1 (**permalink**) 02-05-08, 10:57

Hi,

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

Thanks

mvreade

sdonovan · #2 (**permalink**) 02-05-08, 11:06

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.

mvreade · #3 (**permalink**) 02-05-08, 11:27

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

by0nder · #4 (**permalink**) 02-05-08, 14:37

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.

mvreade · #5 (**permalink**) 02-05-08, 14:49

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

aschk · #6 (**permalink**) 02-06-08, 06:30

How are you deleting the records from the table?
You should use TRUNCATE, which will reset the auto_increment value back to 0.

aschk · #7 (**permalink**) 02-06-08, 06:35

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...

Internet Services

Web Development

Digital Marketing

Consumer Tech