CODEWITHSUNDEEP
mysqlauto-increment
Dee
Dee

Reputation: 1

MySQL auto_increment initial value

I created a table in MySQL V5.7.28 where the PKs initial value is 50 and increases in steps of 10. e.g. 50, 60, 70 etc. My code is :

  Set Session auto_increment_increment=10;

  Create Table Dept (
  DeptNo Integer NOT NULL auto_increment,  
  DName Varchar(45), 
  Primary Key (DeptNo)
  ) AUTO_INCREMENT=50;

I inserted 3 rows into the table, and get

  51, Accounting
  61, Sales
  71, Tax

The increment size is correct (10) but the initial value is 1 higher than I expected.

If I open another session, rerun the above code without the "Set Session auto_increment_increment=10;" I get what is expected

  50, Accounting
  51, Sales
  52, Tax

which is the correct initial value of 50, but the step size is 1 rather than the 10 I which I was planning on using.

Setting the auto_increment_increment seems to increase the initial value of auto_increment field by 1.

Any thoughts on what I am doing wrong? Many Thanks,

Upvotes: 0

Views: 82

Answers (2)

Rick James
Rick James

Reputation: 142208

It is unwise to expect anything other than 'uniqueness' from AUTO_INCREMENT.

There are many cases where a number will be skipped without really letting you know.

If different connections are inserting into that table, they need proper coordination.

Etc.

Instead of using A_I, consider something like

INSERT INTO Dept (...)
    SELECT MAX(DeptNo) + 10, "PartyPlanning"
        FROM Dept;

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562230

If you want the values to be multiples of 10, you can set auto_increment_offset=10;

Demo:

mysql> create table mytable (id int auto_increment primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> set auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)

mysql> set auto_increment_offset=10;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mytable () values ();
Query OK, 1 row affected (0.04 sec)

mysql> insert into mytable () values ();
Query OK, 1 row affected (0.04 sec)

mysql> insert into mytable () values ();
Query OK, 1 row affected (0.04 sec)

mysql> select * from mytable;
+----+
| id |
+----+
| 10 |
| 20 |
| 30 |
+----+

You can't set the offset to 0, because the minimum value is 1.

mysql> set auto_increment_offset=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Truncated incorrect auto_increment_offset value: '0' |
+---------+------+------------------------------------------------------+

Upvotes: 1

Related Questions