How to Set Initial Values and Configure Auto-Increment in MySQL?

Setting Initial Value and Configuring Auto Increment in MySQL

To establish an initial value and enable automatic increment for the "id" column in a MySQL table, the following methods can be employed:

Setting the Initial Value

To set the initial value for the "id" column, utilize the ALTER TABLE command followed by AUTO_INCREMENT=. For instance, to begin at 1001:

ALTER TABLE users AUTO_INCREMENT=1001;

Configuring Auto Increment

If the "id" column has not yet been created, it can be added using the following command:

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

In this command:

• INT UNSIGNED NOT NULL: Specifies an unsigned integer type that cannot store null values.
• AUTO_INCREMENT: Enables automatic incrementing of the column value for each new row.
• INDEX (id): Creates an index on the "id" column for faster retrieval.

Example Insert Statement

Once the initial value and auto increment have been configured, you can insert data into the "users" table without specifying the "id" value:

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

The auto increment mechanism will automatically assign the next sequential value starting from the initial value of 1001.

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