MySQL self-increment field reflow mysql delete table after increment field starting from 1

Last Update:2014-10-14 Source: Internet

Author: User

Tags mysql delete

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

MyISAM Data Sheet

After you delete the largest numbered record, the number is not reusable.

You can use the "auto_increment=n" option to specify a self-increment initial value when building a table.

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

Other:

The self-increment field is not contiguous because some record lines have been deleted

1,3,4,5,7,10 like this.

-------------------------

The truncate command will restore the self-increment field to start with 1, or you can try to empty the table_a, then cancel the self-increment, save, and add back the self-increment, which is also the method of restoring the self-increment to 1.

-----------

MySQL Database unique number field (AutoNumber field)
In database applications, we often use unique numbers to identify records. The Auto_increment property of the data column can be passed in MySQL

From the dynamic generation. MySQL supports a variety of data tables, each of which has a different self-increment attribute, which describes the data in various tables

The column self-increment property.

ISAM table

If you insert a null into a auto_increment data column, MySQL will automatically generate the next serial number. Numbers from 1 open

and 1 is the base increment.

Inserting 0 into the auto_increment data column has the same effect as inserting a null value. However, it is not recommended to do this, or to insert a null value as well.

When you insert a record, the value is not explicitly specified for auto_increment, which is equivalent to inserting a null value.

When you insert a record, if you explicitly specify a value for the Auto_increment data column, there are two cases, case one, if

The inserted value repeats with the existing number, and an error message occurs because the value of the Auto_increment data column must be unique;

Second, if the inserted value is greater than the numbered value, it will be inserted into the data column and the next number will be passed from the new value.

Increase. In other words, you can skip some numbers.

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

If the self-increment column is updated with the update command, an error occurs if the column value repeats with the existing value. If it is greater than the existing value, the next number

Increment starting from this value.

If you use the Replace command to modify an existing record in the table based on the value in the Auto_increment data column, that is, the number of auto_increment

It is now listed in the WHERE clause of the Replace command, and the corresponding Auto_increment value will not change. But if the Replace life

The primary KEY OR unique index to modify the existing record (that is, the Auto_increment data column does not appear in the

In the WHERE clause of the Replace command), the corresponding Auto_increment value-if it is set to null (if it is not assigned a value)-

Will change.

The last_insert_id () function obtains the last number that is automatically generated by the self-increment column. But the function is only with the server during this session

The generated value is related to. 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 datasheets is based on the mechanism in the ISAM table.

MyISAM Data Sheet

After you delete the largest numbered record, the number is not reusable.

You can use the "auto_increment=n" option to specify a self-increment initial value when building a table.

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

You can use composite indexes to create multiple, independent, self-increment sequences in the same data table by creating a

A primary KEY OR unique index consisting of multiple data columns, and the Auto_increment data column is included in this index as

It's the last data column. Thus, in this composite index, the preceding columns of data each constitute a unique combination, at the very end

The Auto_increment data column generates a sequence number corresponding to that combination.
Heap Data Sheet

The heap data table is not allowed to use the self-increment column starting with MySQL4.1.

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

The self-increment initial value can be modified by the AUTO_INCREMENT=N option of the ALTER TABLE statement.

Numbering is not reusable.

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

BDB Data Sheet

You cannot change the self-increment initial value by using the AUTO_INCREMENT=N option of the CREATE TABLE OR ALTER TABLE.

Reusable numbering.

Supports the use of composite indexes in one data table to generate multiple serial numbers that do not interfere with each other.

INNDB Data Sheet

You cannot change the self-increment initial value by using the AUTO_INCREMENT=N option of the CREATE TABLE OR ALTER TABLE.

Non-reusable numbering.

Using composite indexes in one data table is not supported to generate multiple serial numbers that do not interfere with each other.

When using auto_increment, you should pay attention to the following points:

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 positive sequence, so the data column should be declared as unsigned so that the order

The number of columns can be increased by one more times.

The Auto_increment data column must have a unique index to avoid ordinal duplication.

The Auto_increment data column must have a NOT NULL property.

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 the tinyint data column is 127, such as adding

UNSIGNED, the maximum is 255. Once the upper limit is reached, the auto_increment will fail.

When a full table is deleted, Auto_increment restarts numbering from 1. Full table deletion means that when the following two statements are emitted:

Delete from table_name;
Or
TRUNCATE TABLE table_name

This is because in the case of full table operation, MySQL is actually doing such an optimization operation: first to delete all data and indexes in the data tables, and then

After rebuilding the data table. If you want to delete all rows of data and want to keep the sequence number information, you can use a delete command with a where to

To suppress MySQL optimizations:

Delete from table_name where 1;

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

Force MySQL to no longer use the sequence values that have been used: Create a separate data that is specifically used to generate auto_increment sequences

table, and do not delete records for that table forever. When you need to insert a record in the main data table, start with the one that specifically generates the ordinal

A null value is inserted in the table to produce a number, and then, when inserting data into the main datasheet, take advantage of the last_insert_id () function

Get this number and assign it to the data column of the main table's stored sequence. Such as:

INSERT INTO ID set id = NULL;
INSERT INTO main set main_id = last_insert_id ();

Use the ALTER command to add a data column with the Auto_increment property to a data table. MySQL will automatically generate all the numbers

。

The simplest way to rearrange an existing sequence number is to first delete the column and then rebuild it, and MySQL will regenerate the sequential numbering sequence.

To generate a sequence without auto_increment, use the last_insert_id () function with parameters. If you use a parameter with a

last_insert_id (expr) to insert or modify a data column, followed by a call to the last_insert_id () function without parameters,

The second function call returns the value of expr. Here's how it's done:

First create a data table with only one data row:
CREATE TABLE seq_table (id int unsigned not NULL);
INSERT into seq_table values (0);
Then retrieve the serial number using the following operation:
Update seq_table Set seq = last_insert_id (seq + 1);
Select last_insert_id ();
By modifying the constant values in seq+1, you can generate sequences of different steps, such as seq+10, to generate a sequence with a step of 10.

This method can be used for counters that insert multiple rows in a data table to record different count values. Again with the return of the last_insert_id () function

Value generates a count value for different content. The advantage of this approach is that a unique sequence number can be generated without a transaction or a lock,unlock table. No

can affect normal table operation of other client programs.

ALTER TABLE table_name AUTO_INCREMENT=N;
Note N can only be greater than the integer value of the existing auto_increment, and the value less than is invalid.
Show table status like ' table_name ' you can see that the Auto_increment column is a value that is present.
The step value cannot be changed. Use only with the last_inset_id () function mentioned below