How to Set an Initial Value and Maintain Auto-Increment in MySQL?

Setting Initial Value and Auto-Increment in a MySQL Table

MySQL tables often feature an "id" column as a primary key, usually with an auto-increment property to ensure unique identification of rows. This article will address the specific question of how to set an initial value for the "id" column, starting from a non-default value (e.g., 1001) while still maintaining auto-increment functionality.

Initial Value Configuration

To set the initial value of the "id" column, you can use the ALTER TABLE statement followed by the AUTO_INCREMENT clause. This allows you to specify the value from which the auto-increment sequence should start.

ALTER TABLE users AUTO_INCREMENT=1001;

Adding the "id" Column with Initial Value

If you have not yet added the "id" column to your users table, you can combine the creation of the column with the initial value setting using the following command:

ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);

This statement not only adds the "id" column as an unsigned integer, but also sets the auto-increment starting value to 1001 and creates an index on the column for faster retrieval.

With these adjustments in place, you can now execute your INSERT statement without explicitly specifying the "id" value, and the rows will be inserted with unique "id" values starting from 1001.

The above is the detailed content of How to Set an Initial Value and Maintain Auto-Increment in MySQL?. For more information, please follow other related articles on the PHP Chinese website!