http://blog.sina.com.cn/s/blog_68431a3b0100y04v.html
Method 1:
TRUNCATE TABLE name
This will not only delete all the data, but also reposition the increment field
Method 2:
Delete from your table name
DBCC CHECKIDENT (your table name, reseed,0)
Reposition the self-increment field so that it starts at 1
Method 3:
If you want to save your data, introduce you to the third method, by Qinyi
Export the database with phpMyAdmin, you'll find it in there.
Edit the SQL file, change the self-increment next ID number, and then import it.
-------------------------
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 primary KEY OR unique index that consists 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 columns of data constitutes a unique combination, and the Auto_increment data column at the end produces 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 number of the sequence can be incremented by one time.
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 tinyint data columns is 127, or, for example, 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 delete all data and indexes in the data table, 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 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 data table specifically designed to generate the auto_increment sequence, and never delete records for that table. 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 data table, the last_insert_id () function is used to obtain the 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 last_insert_id (expr) to insert or modify a data column, and then call 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. The return value of the last_insert_id () function is then combined to generate a count of 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 auto_increment this column is a value. The step value cannot be changed. You can only work with the last_inset_id () function mentioned below
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 tinyint data columns is 127, or, for example, unsigned, the maximum is 255. Once the upper limit is reached, the auto_increment will fail.
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, and then call 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. The return value of the last_insert_id () function is then combined to generate a count of 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.
There are two points that need to be strengthened:
1, only one column of time is not!
2, automatic numbering must be the primary key to be effective!
Methods for controlling table self-increment values after MySQL clears or deletes table data
