MySQL gets the maximum self-increment ID (auto_increment)

In the table structure of a relational database, in general, an ' ID ' field with the ' auto_increment ' extended attribute is defined to ensure that each record in the data table has a unique identity.

In practical applications, getting to the nearest maximum ID value is one of the required courses, and in view of this problem, the practice is organized as follows:

1. New test Data Sheet get_max_id

  mysql>CREATETABLE  ' get_max_id ' (

' ID 'int( One) unsigned not NULLAuto_increment COMMENT'Business PRIMARY Key',' Content 'Char( -)DEFAULT NULLCOMMENT'Business Content',   PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=1 DEFAULTCHARSET=UTF8;

2. Uninitialized table gets the maximum self-increment ID

After creating the data table, we know that the contents of the table are temporarily empty, at which point the query for Max (ID) will be null;

　　Mode 1-max (ID):

The advantage of the way is simple rough, straight to the Chrysanthemum, ah not (￣m￣), straight to the subject;

At the same time, it ignores the influence of other client connections (db_connection), can be straight to the 3rd position;

MySQL>Select Max(ID) fromget_max_id; +---------+ | Max(ID)| +---------+ |    NULL | +---------+ 1Rowinch Set(0.00Sec

　　

　　Mode 2-last_insert_id () function:

LAST_INERT_ID (), returns the value of the first table set by the Auto_increment column in the last insert or UPDATE query.

There are some restrictions on the use of this stuff:

1. In the same Connection connection object (same client), the result of select is the ID of the Auto_increment property column of the last insert. The point of this sentence is "the same", meaning that other connected clients do not affect the results of their queries. Suppose client A and B, the table Ta original self-increment ID is 3, after inserting a record in a has a self-increment ID of 4, in client A through the function query result is 4, but the result value of query in client B is still 3; (verified)

2, not related to the table, that is, assuming TA table and TB table, after inserting records to TA, and then into the TB record, the result value is TB max (ID) value; (verified)

3, when using the non-Magic method (' magic ') to insert or update a record, that is, using a non-0/non-null value as the inserted field, the last_insert_id () return value will not change; (verified)

4. In the same INSERT statement, when multiple values are passed in, the last_insert_id () return value is the ID of the first record of the query; (verified)

5, in the advanced aspect, can be used for the uniqueness of the table ID.

The result of initializing the query is 0, which is different from Max (ID);

Mysql>Selectlast_insert_id ();+------------------+ |LAST_INSERT_ID ()| +------------------+ |                0 | +------------------+ 1Rowinch Set(0.00Sec

　　Mode 3-View table status Show Table state

This method provides basic information for each table under the current db (use db_name;), and can obtain the value of the Auto_increment property through the Where condition;

　　　　The result value provided below is the value of the next self-increment ID.

Mysql>ShowTableStatuswhereName='get_max_id';+------------+--------+---------+------------+------+----------------+-------------+-----------------+------- -------+-----------+----------------+---------------------+-------------+------------+-----------------+------- ---+----------------+---------+|Name|Engine|Version|Row_format|Rows|Avg_row_length|Data_length|Max_data_length|Index_length|Data_free|Auto_increment|Create_time|Update_time|Check_time|Collation|Checksum|Create_options|Comment|+------------+--------+---------+------------+------+----------------+-------------+-----------------+-------- ------+-----------+----------------+---------------------+-------------+------------+-----------------+-------- --+----------------+---------+|get_max_id|InnoDB|      Ten |Compact|    0 |              0 |       16384 |               0 |            0 |  10485760 |              1 |  --Geneva- -  One: the: - | NULL        | NULL       |Utf8_general_ci|     NULL |     |         |+------------+--------+---------+------------+------+----------------+-------------+-----------------+-------- ------+-----------+----------------+---------------------+-------------+------------+-----------------+-------- --+----------------+---------+1Rowinch Set(0.00Sec

　　Way 4-information_schema.tables

Provides information about the tables (including views) in the database. Describes in detail which schema, table type, table engine, and so on a table belongs to;

The result value provided below is the value of the next self-increment ID.

Mysql> SelectTABLE_NAME, auto_increment fromInformation_schema.tableswheretable_name="get_max_id";+------------+----------------+|table_name|Auto_increment|+------------+----------------+|get_max_id|              1 |+------------+----------------+1Rowinch Set(0.01Sec

　Mode 5-@ @IDENTITY global variable

　　　Base: Variables that start with @@ 开头 global variables, and variables that begin with @ are user-defined variables.

The @ @IDENTITY here represents the value of the self-increment column that corresponds to the most recent insert data to a table with the identity attribute (auto_increment). The value obtained here is 0.

1, similar to the last_insert_id () function, this mode must be in the same client within the INSERT and select, and is not affected by other clients; (verified)

2, not related to the table; (verified)

3, Non-Magic method insertion does not affect the result value; (verified)

4. Insert multiple records in the same insert, take the ID value of the first record as the result; (verified)

Mysql> Select @ @IDENTITY;+------------+| @ @IDENTITY |+------------+|          0 |+------------+1Rowinch Set(0.00Sec

MySQL gets the maximum self-increment ID (auto_increment)