Search code examples
sqlmysqlinnodbauto-increment

Set AUTO_INCREMENT starting value in a InnoDB table to zero?

Is there any to get the an AUTO_INCREMENT field of a InnoDB to start counting from 0 not 1

CREATE TABLE `df_mainevent` (
  `idDf_MainEvent` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idDf_MainEvent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Solution

  • MySQL documentation:

    If a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value had not been specified and generates a new value for it.

    So it means that 0 is a 'special' value which is similar to NULL. Even when you use AUTO_INCREMENT = 0 is will set the initial value to 1.

    Beginning with MySQL 5.0.3, InnoDB supports the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, to set the initial counter value or alter the current counter value. The effect of this option is canceled by a server restart, for reasons discussed earlier in this section.