Modifying initial values for an AUTO_INCREMENT column

Posted on

Question :

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?

Answer :

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:

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.

Leave a Reply

Your email address will not be published.