Auto increment by 2 satrting from 1000-CodePudding

im 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 im trying to make the auto incrment start from 1000 and incrment by 2 (1000,1002,1004,1006.....) while using CREATE

CodePudding user response：

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

CodePudding user response：

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 effect all over the database. Because, It's Global variable for MySQL.
For setting starting value from 1000 you may need to set
AUTO_INCREMENT at the end of the Create Table syntax.

CodePudding user response：

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 first with id 1000