Modifying initial values for an AUTO_INCREMENT column

I am using MYSQL Workbench, and I have a table called Customer and have a primary key customer_id which has the INT data type and is auto incremented (“AI” in Workbench column specifications).

This is the DDL code that workbench generates:

ALTER TABLE infrastructure.mfg 
CHANGE COLUMN
 mfg_id mfg_id INT(10) UNSIGNED 
NOT NULL AUTO_INCREMENT 

My intentions:

• I want to start the numbering at “1000” and auto increment by 100.
• I want the start number to be for only this table

For example:

• 1st = 1000
• 2nd = 1100
• 3rd = 1200
• 4th = 1300

How can I do this? Do I use these functions and if so how?

LAST_INSERT_ID()
mysql_insert_id()

If this is not an option can I check the “G” rather than the “AI”?

If I check “G” the MySQL script that is generated is:

ALTER TABLE infrastructure.mfg 

CHANGE COLUMN mfg_id
 mfg_id INT(10) GENERATED ALWAYS AS () VIRTUAL 

Where is there information on how to use this?

MySQL docs for CREATE TABLE explain almost everything about AUTO_INCREMENT columns:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

-- irrelevant stuff omitted

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

-- irrelevant stuff omitted

table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value

-- many stuff omitted

There are two AUTO_INCREMENT settings, the column attribute and a table option that can change the initial value of the auto incremented column from the default (1):

Column Data Types and Attributes

• AUTO_INCREMENT

  An integer or floating-point column can have the additional
  attribute AUTO_INCREMENT. When you insert a value of NULL
  (recommended) or 0 into an indexed AUTO_INCREMENT column, the column
  is set to the next sequence value. Typically this is value+1,
  where value is the largest value for the column currently in the
  table. AUTO_INCREMENT sequences begin with 1. …

Table Options

• AUTO_INCREMENT

  The initial AUTO_INCREMENT value for the table. …

So, to add a modify a column with the AUTO_INCREMENT attribute that starts from a 100, you do:

ALTER TABLE infrastructure.mfg 
    CHANGE COLUMN mfg_id mfg_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    AUTO_INCREMENT = 100 ;

Test in dbfiddle.uk

Further information can be found in the related pages of the documentation:

• Using AUTO_INCREMENT
• AUTO_INCREMENT Handling in InnoDB

Particularly in the last one above, it explains what options you have regarding changing the increment from 1 to 100. This isn’t trivial:

InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1. This default can be overridden by the auto_increment_increment configuration setting.

If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

Test 2 in dbfiddle.uk

Note that these two variables affect all the tables in the MySQL Server.