Search code examples
mysqldefault-valueauto-increment

MySQL: can AUTO_INCREMENT=some_value; be voided when create a table and insert rows?

I've just converted MYISAM into InnoDB. Before doing that, I've saved the table (see the code below, that was created automatically by phpMyAdmin), dropped it, changed the engine to InnoDB and created it again.

CREATE TABLE IF NOT EXISTS `orders` (
  `orderid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `memberid` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=34;

---and adding some rows right after creating.

My question is about AUTO_INCREMENT=34. As I understand, 34 will be the next value of orderid in the table when we insert a new row . Can AUTO_INCREMENT be voided when creating a new table and adding rows, or it's highly recommended? Thank you.

Solution

  • You can remove the AUTO_INCREMENT=34 part and it will start after the highest value in the table, or 1 for an empty table.

    No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

    http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html