Question 21622 in Mysql Question posted in
The official MySQL documentation can be found here.

Auto increment by 2 starting from 1000 – Mysql

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.

Tags:

4

Answers


  1. 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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
  4. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search