How to Change the Starting Number of Auto Increment in MySQL?

How to Set Auto Increment Starting Number in MySQL

MySQL provides an auto-increment feature that assigns unique sequential numbers to rows in a table. By default, the auto_increment value starts from 1. However, you may want to change this starting number to a different value for specific reasons.

Changing the Auto Increment Starting Number

To change the auto_increment value to a different number, you can use the ALTER TABLE statement. The syntax for this statement is:

ALTER TABLE table_name AUTO_INCREMENT = starting_value;

For instance, to set the auto_increment value to 5 for a table named "tbl", you would use the following statement:

ALTER TABLE tbl AUTO_INCREMENT = 5;

Notes

• The starting_value must be greater than the current highest value in the auto_increment column.
• If you specify a starting_value that is less than the current highest value, an error will occur.
• The AUTO_INCREMENT value is stored in the .ibd file for the table.
• If you recreate the table, the AUTO_INCREMENT value will reset to 1.

Conclusion

Using the ALTER TABLE statement, you can easily change the auto_increment starting number in MySQL. This allows you to customize the initial value of the auto-increment column based on your specific requirements.

The above is the detailed content of How to Change the Starting Number of Auto Increment in MySQL?. For more information, please follow other related articles on the PHP Chinese website!