MySQL - auto increment by more than one?-CodePudding

I have a table with a primary key using AUTO_INCREMENT. I want this column to start at 1 and increment by 3, but I can't find how to increase the amount the column increments by.

ALTER TABLE tbl AUTO_INCREMENT = 3; makes the value start at 3, but still increments by 1. T-SQL equivalent would be IDENTITY(1,3).

CodePudding user response：

@Stu is correct, MySQL does not support an equivalent of Microsoft SQL Server's IDENTITY(). You can't set the value of auto_increment_increment for a specific table. It applies to all tables.

You can set auto_increment_increment as a session variable before inserting into your tbl table, then set it back to the default value after the insert. But you'd have to remember to do this every time.

In general, the feature of auto-increment primary keys varies a lot between different implementations of SQL. The SQL:2003 standard includes more general syntax for generated primary key values, but MySQL does not support that syntax.

You might like to use PostgreSQL instead of MySQL if you need an open-source SQL database that has support for more SQL:2003 features.

CodePudding user response：

You can try this approach to increment the ID during insert programatically :

insert into tbl(id, name)
select max(id) 3, 'John'
from tbl;