How can I get the initial value of the identity column in MySql

Question

To get the seed and step values ​​of the identity column in SQL server, I can use this syntax

SELECT ColumnName = name, Seed = seed_value, Step = increment_value 
  FROM sys.identity_columns

So far in MySql, I have found that if I use this syntax

SELECT * FROM INFORMATION_SCHEMA.TABLES
 WHERE auto_increment IS NOT NULL

I can at least find out which columns are identity ...

The question is, how can I get the SEED and STEP values ​​of the identity column from the MySQL schema.

Answer 1

You can get system settings using:

SHOW VARIABLES LIKE 'auto_inc%';

Result:

| Variable_name            | Value 
+--------------------------+-------
| auto_increment_increment | 1     
| auto_increment_offset    | 1  

Link:

• auto_increment_increment
• auto_increment_offset

AUTO_INCREMENT, , , ALTER TABLE:

ALTER TABLE tbl AUTO_INCREMENT = 100;