Auto increment by 2 starting from 1000

Question

I’m using MySQL to make a table:

CREATE TABLE school.student(
    Std_id INT NOT NULL AUTO_INCREMENT,
    Std_name CHAR(40) NOT NULL,
    Std_Birth DATE,
    Std_Group CHAR(2) check(Std_Group in ('G1', 'G2','G3','G4'))
);

And I’m trying to make the auto increment start from 1000 and increment by two (1000, 1002, 1004, 1006, etc.) while using CREATE.

What means *without changing the default auto_increment_increment and auto increment offset* ? You can't have the default (staring 1 incrment by 1) and your customer autoincrement at same time and same column — Jens, Dec 23 '22 at 06:43

score 1 · Answer 1 · edited Apr 17 '23 at 09:55

1

You can do it as follows:

INSERT INTO _students
(Std_id, Std_name, Std_Birth, Std_Group)
select case when count(Std_id) >= 1 then max(Std_id) + 2 else 1000 end as Std_id, 'test', '2022-10-10', 'G1'
from _students;

select case when count(Std_id) >= 1 then max(Std_id) + 2 else 1000 end: this will check if there are any records in your table. If not, it will insert the first with id 1000.

edited Apr 17 '23 at 09:55

Peter Mortensen

30,738
21
105
131

answered Dec 23 '22 at 07:45

SelVazi

10,028
2
13
29

score 0 · Answer 2 · edited Dec 23 '22 at 09:44

0

Create table #autoincre
(
    Std_id int not null Primary key identity (1000,2),
    Std_name char(40) Not null,
    Std_Birth date,
    Std_Group char(2) check(Std_group in ('G1','G2','G3','G4'))
)

Drop table #autoincre

insert into #autoincre values('Ajay','2022-07-10','G1')

select * from #autoincre

edited Dec 23 '22 at 09:44

K H A N

234
1
8

answered Dec 23 '22 at 06:56

Vineet Goyal

1
4

This is Microsoft SQL Server syntax, not MySQL. MySQL doesn't support the Microsoft function `identity()`. – Bill Karwin Dec 23 '22 at 16:01
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 25 '22 at 14:39

score 0 · Answer 3 · edited Apr 17 '23 at 10:01

You may try this solution:

CREATE TABLE school.student(
    Std_id INT NOT NULL AUTO_INCREMENT,
    Std_name CHAR(40) NOT NULL,
    Std_Birth DATE,
    Std_Group CHAR(2) check(Std_Group in ('G1', 'G2','G3','G4'))
) AUTO_INCREMENT = 1000;

set @@auto_increment_increment=2;

while you set the value of @@auto_increment_increment, it will affect all over the database. Because, it's a global variable for MySQL.
For setting the starting value from 1000, you may need to set AUTO_INCREMENT at the end of the Create Table syntax.

score 0 · Answer 4 · answered Dec 23 '22 at 16:10

You can start the auto_increment for a given table at 1000 with a table option:

CREATE TABLE school.student(
 ...
) AUTO_INCREMENT=1000;

Note that the next id generated will be one greater than the table option is set to, so it will generate value 1001. If you want it to include the value 1000, set the table option AUTO_INCREMENT=999.

Your other requirement is more difficult.

https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increment_increment says:

It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server.

In other words, if you set auto_increment_increment=2, this will apply to all tables with an auto-increment column. It is not a per-table option.

You could set auto_increment_increment=2 as a session variable only before inserting into the student table, then set it back to the default before inserting to another table. That sounds like it will be error-prone, because you could forget to change the session variable.

Auto increment by 2 starting from 1000

4 Answers4