Automatic increment value problem of Mysql database _mysql

Create TABLE Test
(
ID INT UNSIGNED NOT null PrimaRY KEY auto_increment,
username VARCHAR () not null
) 
   auto_increment = 100;

In database applications, we often use a unique number to identify the record. The Auto_increment property of the data column can be generated automatically in MySQL. MySQL supports a variety of data tables, each of which has a difference in the properties of the data table, here will be introduced in various data tables of the data column self-added properties.

ISAM table

If you insert a null into a auto_increment data column, MySQL automatically generates the next sequence number. The numbering starts at 1 and 1 is the base increment.

The effect of inserting 0 into a auto_increment data column is the same as inserting a null value. This is not recommended, however, or it is better to insert a null value.

When you insert a record without explicitly specifying a value for Auto_increment, the equivalent is to insert a null value.

When you insert a record, if you explicitly specify a numeric value for the auto_increment data column, there are two cases where, if the inserted value repeats with the existing number, an error message occurs because the value of the Auto_increment data column must be unique; condition two, If the inserted value is greater than the numbered value, it is inserted into the data column and the next number is incremented from the new value. In other words, you can skip some numbers.

If the maximum value of the self-added sequence is deleted, the value is reused when the new record is inserted.

If you update the self-add column with the update command, an error occurs if the column value repeats with the existing value. If it is greater than an existing value, the next number is incremented from that value.

If you use the Replace command to modify an existing record in the datasheet based on the value in the Auto_increment data column, the Auto_increment data is listed in the WHERE clause of the Replace command, and the corresponding auto_ The increment value will not change. However, if the Replace command modifies an existing record through another primary KEY or unique index (that is, the Auto_increment data column does not appear in the WHERE clause of the Replace command), the corresponding auto_ Increment value--if you set it to null (if it is not assigned a value), it changes.

The last_insert_id () function obtains the last number that is automatically generated from the Add column. However, the function is only relevant to the value generated during this session of the server. If the Auto_increment value has not been generated in this session with the server, the function returns 0.

The automatic numbering mechanism for other data tables is based on the mechanism in the ISAM table.

MyISAM data table

This number is not reusable after you delete the maximum number of records.

You can use the "auto_increment=n" option when you are building a table to specify a self increasing initial value.

Use the ALTER TABLE TABLE_NAME AUTO_INCREMENT=N command to reset the starting value of the self increase.

You can use a composite index to create multiple, independent, self-contained sequences in the same datasheet by creating a primary KEY or unique index consisting of multiple data columns for the datasheet and auto_ The Increment data column is included in this index as its last data column. Thus, in this composite index, each of the preceding data columns constitutes a unique combination, the Auto_increment data column at the end produces a sequence number corresponding to the combination.

Heap data table

Heap data tables are allowed to use the self-add column starting with MySQL4.1.

Self-appreciation can be set by the AUTO_INCREMENT=N option of the CREATE TABLE statement.

You can modify the Auto_increment=n starting value by using the options for the ALTER TABLE statement.

Numbering is not reusable.

The heap datasheet does not support the use of composite indexes in a single datasheet to generate multiple sequence numbers that do not interfere with each other.

BDB data table

You cannot change the self-added initial value by using the AUTO_INCREMENT=N option of the CREATE TABLE or Alter table.

Reusable number.

Supports the use of composite indexes in one datasheet to generate multiple, discrete sequence numbers.

INNDB data table

You cannot change the self-added initial value by using the AUTO_INCREMENT=N option of the CREATE TABLE or Alter table.

Non-reusable numbering.

It is not supported to use a composite index in one datasheet to generate multiple sequence numbers that do not interfere with each other.

When using Auto_increment, the following points should be noted:

Auto_increment is a property of a data column and applies only to integer type data columns.

The data column that sets the Auto_increment property should be a sequence of positive numbers, so the data column should be declared as unsigned so that the number of the sequence can be increased by one fold.

The Auto_increment data column must have a unique index to avoid duplicate serial numbers.

The Auto_increment data column must have a NOT NULL attribute.

The maximum value of the Auto_increment data column ordinal is constrained by the data type of the column, such as the maximum number of tinyint data columns is 127, and the maximum is 255 if the unsigned is added. Once the cap is reached, the auto_increment will fail.

Auto_increment will restart numbering from 1 when the entire table is deleted. The whole table deletion means that when the following two statements are issued:

Delete from table_name;ortruncate table table_name

This is because MySQL is actually doing this optimization when you're doing a full table operation: Delete all the data and indexes in the datasheet and then rebuild the datasheet. If you want to delete all rows of data and want to keep the sequence number information, you can use a where delete command to suppress MySQL optimizations:

Delete from table_name where 1;

This will force MySQL to evaluate the conditional expression once for each deleted row of data.

The way to force MySQL to no longer use a sequence value that has already been used is to create a data table specifically designed to generate the auto_increment sequence and never delete the record of the table. When you need to insert a record in the main datasheet, insert a null value in the table that specifically generates an ordinal to produce a number, and then, when inserting data into the main table, use the last_insert_id () function to get the number and assign it to the data column of the host's stored sequence. Such as:

INSERT INTO ID set id = null;insert into main set main_id = last_insert_id ();

The ALTER command can be used to add a data column with the Auto_increment property to a data table. MySQL automatically generates all the numbers.

The easiest way to rearrange an existing sequence number is to delete the column before rebuilding it, and MySQL will restart the sequential numbering sequence.

A sequence is generated without auto_increment, and a last_insert_id () function with parameters is available. If you insert or modify a data column with a parameter last_insert_id (expr), and then call the last_insert_id () function without arguments, the second function call returns the value of expr. The following is a demonstration of how the method is done:

Create a datasheet that has only one data row: CREATE TABLE seq_table (id int unsigned NOT NULL), insert into seq_table values (0), and then retrieve the serial number with the following action: Update s eq_table Set seq = last_insert_id (seq + 1), select last_insert_id (), and by modifying constant values in seq+1, you can generate sequences of different steps, such as seq+10 to generate a sequence with a step size of 10.

This method can be used for counters that insert multiple rows in a datasheet to record different count values. The count value of different content is generated with the return value of the last_insert_id () function. The advantage of this approach is that a unique sequence number can be generated without a transaction or Lock,unlock table. does not affect normal table operations for other client programs.

Here to introduce the MySQL database modified automatic increment value

Where TableName is the name of the table, NUM is the new automatic increment to be set, then insert a data, the AutoIncrement value is num, but NUM must be greater than or equal to the existing automatic increment, otherwise the SQL statement will execute successfully, but it does not actually work.