Equivalent of MSSQL IDENTITY Column in MySQL

Question

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
);

If only MySQL provided some type of [documentation](http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html) — JohnFx, Apr 23 '12 at 15:48
true @JohnFx, although personally I'd prefer to go to StackOverflow than that site from my googling. — JumpingJezza, Jul 01 '15 at 01:38

score 54 · Accepted Answer · answered Apr 23 '12 at 15:46

54

CREATE TABLE Lookups.Gender
(
    GenderID   INT         NOT NULL AUTO_INCREMENT,
    GenderName VARCHAR(32) NOT NULL
);

answered Apr 23 '12 at 15:46

Joe Stefanelli

132,803
19
237
235

2

Thanks for your answer hope could pay you back :) – Allan Chua Apr 23 '12 at 15:48
1

This doesn't work for me (5.6.10; this is Aurora but syntax should work identically): `SQL Error (1075): Incorrect table definition; there can be only one auto column and it must be defined as a key`. – mbourgon Oct 02 '17 at 20:56
1

@mbourgon https://stackoverflow.com/questions/8645889/there-can-be-only-one-auto-column – Joe Stefanelli Oct 02 '17 at 20:59

score 7 · Answer 2 · edited Jun 20 '16 at 14:46

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%';

Equivalent of MSSQL IDENTITY Column in MySQL

2 Answers2

Linked

Related