Equivalent of MSSQL IDENTITY Column in MySQL
56,076
Solution 1
CREATE TABLE Lookups.Gender
(
GenderID INT NOT NULL AUTO_INCREMENT,
GenderName VARCHAR(32) NOT NULL
);
Solution 2
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%';
Author by
Allan Chua
Just a simple guy that loves everything about web development, cloud (AWS), software architecture, DevOps and AI/ML. I'm writing a blog at medium with the hopes of being able to share my humble knowledge to the online developer community.
Updated on June 21, 2020Comments
-
Allan Chua almost 4 years
What is the equivalent of MSSQL
IDENTITY
Columns in MySQL? How would I create this table in MySQL?CREATE TABLE Lookups.Gender ( GenderID INT IDENTITY(1,1) NOT NULL, GenderName VARCHAR(32) NOT NULL );