Set AUTO_INCREMENT starting value in a InnoDB table to zero?
Solution 1
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.
Solution 2
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)
Solution 3
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.
Solution 4
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
Charles Faiga
Software developer working with: Delphi, C++, C, MySql and Embedded Systems
Updated on April 30, 2020Comments
-
Charles Faiga about 4 years
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;
-
Jannes about 11 yearsJust do another ALTER TABLE
table_name
AUTO_INCREMENT=1; after the first insert. -
Ross Smith II about 10 yearsThis answer is somewhat misleading, as MySQL does allow zeros in
AUTO_INCREMENT
fields. See stackoverflow.com/questions/1578518/… for the correct answer to this question.