How to Configure Initial Value and Auto-Increment for a MySQL Column?

Configuring Initial Value and Auto-Increment in MySQL

To assign a specific initial value for an "id" column that starts from 1001 in a MySQL table, follow these steps:

Using ALTER TABLE:

ALTER TABLE users AUTO_INCREMENT=1001;

This command changes the auto-increment value for the existing "id" column in the "users" table to 1001.

Adding a New Column with Initial Value and Auto-Increment:

If the "id" column does not exist in the "users" table, you can add it with the following command:

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

This command adds a column named "id" with the following properties:

• INT UNSIGNED: Represents an integer value that cannot be negative.
• NOT NULL: Ensures that the column cannot contain null values.
• AUTO_INCREMENT: Automatically increments the value for each new row inserted into the table, starting from 1001 (as specified by the AUTO_INCREMENT=1001 option).
• INDEX (id): Creates an index on the "id" column to improve performance during data retrieval and update operations.

Example Usage:

To insert a new row into the "users" table without specifying the "id" value, you can use the following statement:

INSERT INTO users (name, email) VALUES ('{$name}', '{$email}');

MySQL will automatically assign the next value in the "id" column sequence, which would be 1001.

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