How to set initial value and auto-increment in MySQL?

The AUTO_INCRMENT attribute is used to generate a unique identifier for a new row. If a column is declared "NOT NULL", NULL can be assigned to the column to generate a sequence of numbers.

When any value is inserted into an AUTO_INCREMENT column, the column will be set to that column value and the sequence will also be reset so that it automatically generates values ​​within the sequential range of the largest column value.

Existing "AUTO_INCRMENT" columns can be updated, which will also reset the "AUTO_INCRMENT" sequence. Latest automatically generated 'AUTO_INCRMENT;The value can be retrieved using the 'LAST_INSERT_ID()' function in SQL or using the C API function 'mysql_insert_id()'.

These functions are connection-specific, which means that their return values ​​are not affected by other connections performing insert operations. The smallest integer data type that can be used for the "AUTO_INCRMENT" column that is large enough to accommodate the largest sequence value required by the user.

AUTO_INCREMENT rules

You need to follow the following rules when using the AUTO_INCRMENT attribute -

• Each table has only one AUTO_INCRMENT column, and its data type is Usually an integer.

• AUTO_INCREMENT column needs to be indexed. This means it can be PRIMARY KEY or UNIQUE index.

• AUTO_INCRMENT column must have NOT NULL constraint.

• When AUTO_INCREMENT is set to the column, MySQL automatically adds NOT NULL constraints on the column itself.

If the id column has not been added to the table, you can use the following statement-

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

If the id column already exists, you can use the following command-

ALTER TABLE tableName AUTO_INCREMENT=specificValue;

Here, tableName refers to the name of the table where the "AUTO_INCRMENT" column needs to be set. "specificValue" refers to the integer starting from the user-specified "AUTO_INCRMENT" value.

The above is the detailed content of How to set initial value and auto-increment in MySQL?. For more information, please follow other related articles on the PHP Chinese website!