1

for a MySQL table, I want the primary ID column to be Auto_Incremented by 100, with starting value as 1. So the value will be 1, 101, 201

For SQL server, it can be achieved by following :-

CREATE TABLE Persons (
    Personid int IDENTITY(1,100) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

How can I achieve the same with MySQL? The idea behind this is to make the ID not collide with the IDs generated in other data centers.

Jerald Baker
  • 1,121
  • 1
  • 12
  • 48
  • No It doesn't. It auto increments previous value by 1001. not by 1000 with initial value as 1 – Jerald Baker Mar 15 '21 at 11:57
  • MySQL allows you to set the starting value but not the increment as far as I know. I have no idea why the question is being downvoted; it seems like a very reasonable question. – Gordon Linoff Mar 15 '21 at 11:57
  • Don't do it. Primary keys are internal values. Their purpose is to be UNIQUE row identifiers, not to be good looking or sexy. If you are asking for specific values, then you probably want to expose the PK to the external world. – The Impaler Mar 15 '21 at 11:58
  • 1
    consider 2-way replicated database. When ID generated DB should not collide with ID generated by other DB. This is possible when DB1 generates values like 101, 201, 301.. and DB2 generates values like 102, 202, 302, etc. This is why we need to be able to specify both start value, as well as increment size. – Jerald Baker Mar 15 '21 at 12:05
  • In MySQL, auto_increment step is basically global for all tables: https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increment_increment – Álvaro González Mar 15 '21 at 12:07
  • yeah but other DBs provide a way to specify this for each table. I have asked the question here again - https://stackoverflow.com/questions/66637760/how-to-set-both-starting-value-and-auto-increment-size-for-a-table-in-mysql-8 – Jerald Baker Mar 15 '21 at 12:11

0 Answers0