[Growth] creating MySQL auto-increment Fields

From: http://blog.chinaunix.net/space.php? Uid = 13626447 & Do = Blog & cuid = 392987

 

Usage:

Create Table Test
(
Id int unsigned not null primary key auto_increment,
Username varchar (15) not null
)
Auto_increment = 100;

In database applications, we often use unique numbers to identify records. In MySQL, The auto_increment attribute of the data column can be automatically generated. MySQL supports multiple data tables, and the auto-increment attributes of each data table are different. Here we will introduce the auto-increment attributes of Data columns in various data tables.

• Isam table

  • If a null value is inserted into an auto_increment data column, MySQL automatically generates the next sequence number. The number starts from 1 and increases with 1 as the base number.

  • Insert 0 into the auto_increment data column to be the same effect as insert null value. However, it is not recommended to insert null values.

  • When a record is inserted, if no value is specified for auto_increment, it is equivalent to inserting a null value.

  • When you insert a record, if you specify a value for the auto_increment data column, there are two situations. Case 1: If the inserted value is the same as the existing number, an error occurs because the value of the auto_increment data column must be unique. Case 2: If the inserted value is greater than the numbered value, the inserted value is inserted into the data column, and the next number will increase progressively from the new value. That is to say, you can skip some numbers.

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

  • If you use the update command to update the auto-increment column, if the column value is the same as the existing value, an error occurs. If the value is greater than the existing value, the next number increases progressively from this value.

  • If the replace command is used to modify the existing records in the data table based on the values in the auto_increment data column, that is, the auto_increment data is listed in the WHERE clause of the replace command, the corresponding auto_increment value will not change. However, if the replace command uses other primary key or unique indexes to modify existing records (that is, the auto_increment data column does not appear 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) -- changes.

  • The last_insert_id () function obtains the last number automatically generated by the auto-increment column. However, this function is only related to the value generated during the current session of the server. If the auto_increment value is not generated in this session with the server, the function returns 0.

  The automatic numbering mechanism of other data tables is based on the mechanism in the isam table.

• MyISAM data table

  • after deleting a record with the largest number, this number cannot be reused.

  • you can use the" auto_increment = N "option to specify an auto-increment initial value when creating a table.

  • the alter table table_name auto_increment = n command can be used to reset the start value of auto-increment.

  • you can use composite indexes to create multiple self-incrementing sequences in the same data table. The specific procedure is as follows: create a primary key or unique index composed of multiple data columns for the data table, and include the auto_increment data column in this index as its last data column. In this way, each of the preceding data columns in the composite index constitutes a unique combination. The auto_increment data column at the end generates a sequence number corresponding to the combination.

• heap data table

  • auto-increment columns can be used only when the heap data table starts with mysql4.1.

  • auto-increment can be set using the auto_increment = n option of the create table statement.

  • you can use the auto_increment = n option of the alter table statement to modify the initial value of auto increment.

  • numbers cannot be reused.

  • heap data tables do not support using compound indexes in a data table to generate multiple serial numbers that do not affect each other.

• Bdb data table

  • You cannot use the auto_increment = n option of create table or alter table to change the auto-increment initial value.

  • Reusable ID.

  • You can use composite indexes in a data table to generate multiple serial numbers that do not affect each other.

• Inndb data table

  • You cannot use the auto_increment = n option of create table or alter table to change the auto-increment initial value.

  • Numbers cannot be reused.

  • You cannot use composite indexes in a data table to generate multiple serial numbers that do not affect each other.

When using auto_increment, pay attention to the following points:

• Auto_increment is an attribute of a data column. It is only applicable to integer data columns.

• The data column that sets the auto_increment attribute should be a positive number sequence, so the data column should be declared as unsigned, so that the number of the sequence can be doubled.

• The auto_increment data column must have a unique index to avoid repeated sequence numbers.

• The auto_increment data column must have the not null attribute.

• The maximum number of the auto_increment data column is subject to the data type constraints of this column. For example, the maximum number of the tinyint data column is 127. If unsigned is added, the maximum value is 255. Once the upper limit is reached, auto_increment becomes invalid.

• When deleting a full table, auto_increment starts numbering from 1. When a full table is deleted, the following two statements are issued:

    Delete from table_name; ortruncate table table_name  

  This is because MySQL optimized the entire table: delete all data and indexes in the data table first, then, recreate the data table. If you want to delete all data rows and retain sequence numbers, you can use a DELETE command with where to suppress MySQL optimization:

    Delete from table_name where 1;  

  This forces MySQL to evaluate a conditional expression for each Deleted Data row.

• The method that forces MySQL not to reuse the used sequence value is: create a data table dedicated to generating the auto_increment sequence and never delete the records of the table. When you need to insert a record in the primary data table, First insert a null value in the table that specifically generates the sequence number to generate a number. Then, when inserting data into the primary data table, use the last_insert_id () function to obtain this number and assign it to the data column of the storage sequence of the master table. For example:

  Insert into Id set id = NULL; insert into main set main_id = last_insert_id ();

• You can use the alter command to add a data column with the auto_increment attribute to a data table. MySQL automatically generates all numbers.

• To rearrange an existing serial number, the simplest way is to delete the column and recreate it. MySQL will regenerate the continuous serial number sequence.

• Generate a sequence without using auto_increment, you can use the last_insert_id () function with parameters. If you use a last_insert_id (expr) with parameters 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. The following shows the specific operation of this method:

    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, use the following operations to retrieve the serial number:  
    Update seq_table set seq = last_insert_id (SEQ + 1); select last_insert_id ();  
    By modifying the constant value in seq + 1, you can generate a sequence with different step sizes. For example, seq + 10 can generate a sequence with a step size of 10.  

  This method can be used for counters, insert multiple rows in the data table to record different count values. Then, use the return value of the last_insert_id () function to generate the Count values of different content. The advantage of this method is that the unlock table can generate unique sequence numbers without transactions or locks. Normal table operations of other Programs are not affected.