Equivalent of MSSQL IDENTITY Column in MySQL
CREATE TABLE Lookups.Gender( GenderID INT NOT NULL AUTO_INCREMENT, GenderName VARCHAR(32) NOT NULL);
CREATE TABLE `Persons` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `LastName` varchar(255) NOT NULL, `FirstName` varchar(255) DEFAULT NULL, `Address` varchar(255) DEFAULT NULL, `City` varchar(255) DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1;This above example uses the AUTO_INCREMENT syntax. You can specify a starting offset specific to the table.
The Increment, however, has to be set globally.
SET @@auto_increment_increment=10;
You can also set a global default for the offset like follows:
SET @@auto_increment_offset=5;
To view your current values, type SHOW VARIABLES LIKE 'auto_inc%';