1. Initial Auto Increment Value

By default, the starting value is 1. When creating a table, you can specify a custom initial value using AUTO_INCREMENT=n. For example:

CREATE TABLE test(
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(15) NOT NULL
) AUTO_INCREMENT=100;

2. Resetting the Auto Increment Value

ALTER TABLE table_name AUTO_INCREMENT=n;

Notes:

1. Inserting NULL into an auto-increment column causes MySQL to generate the next sequence number. The sequence starts at 1 and increments by 1.

2. Inserting 0 into an auto-increment column behaves similarly to inserting NULL. However, it's better to use NULL for clarity.

3. If no value is explicit provided for an auto-increment column during insertion, it is treated as NULL.

4. If an explicit value is provided for an auto-increment column during insertion, two scenarios can occur:

   Case 1: If the provided value duplicates an existing one, an error occurs because the auto-increment column must contain unique values.

   Case 2: If the provided value is larger than the current maximum, it is inserted, and the next sequence starts from this new value, potentially skipping some numbers.

5. Updating an auto-increment column may cause errors if the new value conflicts with existing ones. If it is greater than all existing values, the next sequence will start from that value.

3. Modifying the Auto Incerment Step Size

1. Check relevant variables:

SHOW VARIABLES LIKE 'auto_inc%';

auto_increment_increment defines the step size (default is 1), while auto_increment_offset determines the starting point (default is 1). InnoDB manages auto-increment values using its own counter, which is locked during inserts until the transaction completes. To improve performance, innodb_autoinc_lock_mode was introduced:

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

The possible values are:

1. 0 - Table lock
2. 1 - Default, uses a mutex and pre-allocates values, which might result in gaps
3. 2 - Non-continuous values, better performance

When innodb_autoinc_lock_mode is set to 1, MySQL pre-allocates IDs, and after insertion, these allocated IDs are marked as used.

A known issue occurs when using INSERT ... ON DUPLICATE KEY UPDATE, where pre-allocated IDs may be wasted if the insert fails, leading to gaps in the sequence.

Application:

In a master-master replication setup, set auto_increment_increment=2 and auto_increment_offset=1 and 2 on each server to avoid conflicts between servers.

2. Set global variables:

Method 1: Use SQL commands to change the increment and offset values:

mysql> SET @@auto_increment_increment=n; mysql> SET @@auto_increment_offset=m;

If changes do not persist after restarting MySQL, they were likely defined in the configuration file (my.cnf). Modify the file directly:

vi /etc/my.cnf

Search for auto_increment_increment and update it. After saving, restart the MySQL service:

#/etc/init.d/mysql restart

This approach aplies globally and affects all tables and databases.