Set AUTO_INCREMENT starting value in a InnoDB table to zero?

Question

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;

score 6 · Accepted Answer · answered Oct 16 '09 at 15:01

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.

This answer is somewhat misleading, as MySQL _does_ allow zeros in `AUTO_INCREMENT` fields. See http://stackoverflow.com/questions/1578518/set-auto-increment-starting-value-in-a-innodb-table-to-zero/16291136#16291136 for the correct answer to this question. — Ross Smith II, Mar 12 '14 at 17:48

Stefan Gehrig · Answer 2 · 2009-10-16T15:04:46.767

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

works with MySQL >= 5.0.3.

EDIT:

Just noticed that MySQL in general does not like auto-increment values equal to 0 - that's independent from the used storage engine. MySQL just uses 1 as the first auto-increment value. So to answer the question: NO that's not possible but it does not depend on the storage engine.

score 2 · Answer 3 · answered Apr 30 '13 at 02:11

This works in both InnoDB and MyISAM, and the second insert is a 1 not a 2:

CREATE TABLE ex1 (id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM;

SET sql_mode='NO_AUTO_VALUE_ON_ZERO';

INSERT INTO ex1 SET id=0;
INSERT INTO ex1 SET id=NULL;

SELECT * FROM ex1;

+----+
| id |
+----+
|  0 |
|  1 |
+----+
2 rows in set (0.00 sec)

CREATE TABLE ex2 (id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

SET sql_mode='NO_AUTO_VALUE_ON_ZERO';

INSERT INTO ex2 SET id=0;
INSERT INTO ex2 SET id=NULL;

SELECT * FROM ex2;

+----+
| id |
+----+
|  0 |
|  1 |
+----+
2 rows in set (0.00 sec)

score 1 · Answer 4 · answered Nov 23 '12 at 22:17

Daren Schwenke's technique works. To bad that the next record inserted will be 2.
For example:

CREATE TABLE IF NOT EXISTS `table_name` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
`Name` VARCHAR(100) NOT NULL, 
PRIMARY KEY( `ID` )
) ENGINE=InnoDB  AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

INSERT INTO `table_name` (`Name`) VALUES ('Record0?');
UPDATE `table_name` SET `ID`=0 WHERE `ID`=1;
INSERT INTO `table_name` (`Name`) VALUES ('Record1?');
SELECT * FROM `table_name`;

ID     Name
0      Record0?
2      Record1?

This isn't a big deal its just annoying.

Tim

Just do another ALTER TABLE `table_name` AUTO_INCREMENT=1; after the first insert. — Jannes, Apr 03 '13 at 13:34

score 0 · Answer 5 · answered Oct 16 '09 at 15:13

I have not been able to have autoincrement start at 0, but starting at 1 and then setting it to 0 via an UPDATE works fine.

I commonly use this trick to detect deletes in a table.

On update of any row, I set that row's last update time.

On deletes, I set the last update time of row 0.

Set AUTO_INCREMENT starting value in a InnoDB table to zero?

5 Answers5