MySQL auto_increment and some precautions

MySQL auto_increment and some precautions

The auto_increment type attribute in MySQL is used to automatically generate an ID for a record in a table, which can replace sequence in databases such as Oracle and PostgreSQL to a certain extent. A table can only have one auto_increment attribute, which must be a part of the primary key. The auto_increment attribute can be any integer type (tinyint, smallint, Int, bigint, etc ).

When a record is inserted, the system automatically generates a value for the auto_increment attribute. The value of this attribute is not specified in the values list. If a table t is defined as follows:
Create Table T (A int auto_increment primary key, B INT );
The following insert statement is generally used:
Insert into T (B) values (1 );
That is, the value of A is not specified during insertion.

The second method is to specify 0 for the auto_increment attribute during insertion, that is:
Insert into T values (0, 1 );
And
Insert into T (B) values (1 );
The effect is the same. However, this method is not applicable to the first method, and takes effect only when no_auto_value_on_zero is not included in SQL mode in 5.0 and later versions. (If no_auto_value_on_zero is specified in SQL mode, 0 is inserted. It is not recommended. Other methods such as specifying the value of the auto_increment attribute as default are also not recommended.

There are also multiple methods to obtain the value of the newly generated auto_increment attribute. The recommended method is to use the last_insert_id () function, that is, immediately after the insert operation:
Select last_insert_id ();
To obtain the new value of auto_increment.

In addition, you can use the "where auto_col is null" condition to select the newly inserted row, that is, immediately after the insert:
Select * from t where A is null;
The selected row is the newly inserted row instead of the row that actually meets the "A is null" condition. However, if you execute the preceding query again, the returned row is actually a row that meets the "A is null" condition. Because a is the primary key, it will certainly return an empty set. It looks strange, though, but MySQL does not want to do this either. However, this is a usage in the ODBC standard. To support ODBC, MySQL cannot. However, you can set SQL _auto_is_null to 0 to disable this usage.

In addition, the auto_increment attribute also makes copying troublesome. In general, copying the auto_increment attribute can work correctly, but there are still problems in the following situations:
1. Insert delayed... values (last_insert_id () cannot be correctly copied
2. Records inserted using the auto_increment attribute in the stored procedure cannot be correctly copied.
3. When the auto_increment attribute is added using the "alter table" command, the values generated on the master and slave nodes may be different, because the values of the auto_increment attribute of each row depend on the storage order of the physical machine.

__________________
More database/MySQL articles welcome to my blog http://wangyuanzju.blog.163.com

From: http://www.itpub.net/790059.html