About MySQL auto-Increment

1. Keywords

Auto_increment

2. Auto-Increment
Usage

Example:
Create Table animals (ID mediumint not null auto_increment,
Name char (30) not null,
Primary Key (ID ));

3.
About Auto-Increment

Q: How to obtain the current maximum auto-increment value?
 
A
: Select @ identity

Q:
How to obtain the current auto-increment maximum value of a table?
 
A
: Select max (ID) from table

Q:
What is auto-increment?
 
A
: Normally,
ID. You can use select max (ID) from table.
Last_insert_id is irrelevant to the table, as shown in figure
If you insert data to table A and then insert data to table B, the last_insert_id will change.
Insert multiple records using a single insert statement
, Last_insert_id returns a list.
@ Identity indicates the last time a user has been directed to an auto-incrementing column with the identity attribute.
The value of the auto-increment column corresponding to the data inserted in the table. It is a global variable defined by the system.
For example, if a table A has an auto-increment column ID, after a row of data is inserted into Table
If the column value is automatically increased to 101, the value obtained through select @ identity is 101.
Note: last_insert_id is a function.
Use
Method: last_insert_id ()
 
Q: In last_insert_id () and MSSQL of MySQL
@ Identity
 
A
: According to application requirements
The ID of the record in the newly inserted database table.
You can use the last_insert_id () function in MySQL and use it in MSSQL.
@ Identity.
A convenient function.
HoweverNote:
Of
Yes. When insert statements are used to insertMultiple records
Use last_insert_id ()Return
The first ID value is returned.
And @ identityReturns the last entry.
.
 
Q: mysql_insert_id ()
And last_insert_id ()

A:Mysql_insert_id ()
Replace
The Return Value of the c api function mysql_insert_id () is converted to long (INT in PHP ). For example
If the type of the auto_increment column is bigint, the value returned by mysql_insert_id () is incorrect. Yes
The SQL function last_insert_id () in MySQL is used in SQL queries.
 
MySQL
Mysql_insert_id () is implemented by calling last_insert_id.

 
In
MySQL uses last_insert_id ()... in the program using mysql_insert_id ()... MySQL auto_increment and some precautions MySQL
The auto_increment attribute in is used to automatically generate an ID for a record in a table, which can replace Oracle, PostgreSQL, and other databases to a certain extent.
Sequence. 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 class.
Type (tinyint, smallint, Int, bigint, etc ).

When a record is inserted, the system automatically generates a value for the auto_increment attribute.
Generally, the method does not specify the value of this attribute 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
Auto_increment: the specified value is 0.
, 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
It takes effect only when SQL mode does not contain no_auto_value_on_zero in version 5.0 and later versions.
(If
If no_auto_value_on_zero is specified for mode, 0 is inserted. this parameter is not recommended. There are other methods, such as specifying the auto_increment attribute.
It is 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 is used immediately after the insert operation:
Select last_insert_id ();
To generate new achievements
The value of auto_increment.

You can also use "where auto_col
Is null "condition selects the newly inserted row
, That is, immediately after insert:
Select * from t where A is
NULL;
Select
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 result is actually satisfied with "A is
Null "condition, because a is the primary key, it will certainly return an empty set. This looks weird, right? But MySQL doesn't want to do this either, but it is used in the ODBC standard to support
ODBC and MySQL cannot be used either. 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. Use "alter
When the table "command adds the auto_increment attribute, 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
Physical storage order.