Use LAST_INSERT_ID of MySQL to determine the unique ID value of each table Shard _ MySQL

Use LAST_INSERT_ID of MySQL to determine the unique ID value of each table Shard in the MySQL data table structure. generally, a 'id' field with the 'auto _ INCREMENT 'extension property is defined, to ensure that each record of the data table can be uniquely identified by this ID;

As data continues to expand, in order to improve database query performance and reduce query hotspots, a table is generally divided into multiple data tables according to certain rules, that is, table shards;

In addition to the index of different table names, the table structure is the same. if the 'id' field of each table still adopts the 'auto _ INCREMENT 'method, ID cannot identify only one record.

In this case, an external table Shard mechanism is required to generate an ID. we generally use a separate data table (assuming the table name is 'ticket _ mutex) to save this ID, no matter which sub-table has increased data, the ID value is first added to 1 in the ticket_mutex table, and then the ID value is obtained.

This operation to retrieve IDs seems complicated. Fortunately, MySQL provides the LAST_INSERT_ID mechanism, allowing us to complete it step by step.

1. create a data table ticket_mutex

Create table ticket_mutex (name varchar (32) not null primary key comment 'business name', value bigint (20) unsigned not null comment 'Id value ') engine = InnoDB default charset = UTF8 COMMENT 'Save the table Shard ID ';

The 'name' field is used to describe the business of this ID. for example, the 'user' ID can be set to 'user ';
The field 'value' is the business ID value.

2. initialize the business and its ID value

INSERT INTO ticket_mutex(name, value) values('USER', 0),('POST', 0);+------+-------+| name | value |+------+-------+| POST |     0 || USER |     0 |+------+-------+

We initialized two records, that is, two different services, representing 'user information' and 'topic information' respectively. their initial ID values are '0 ';

3. obtain the unique ID of a table shard.
At this time, we need to use the LAST_INSERT_ID () mechanism provided by MySQL.
When adding a piece of data to the user table, obtain the 'user ID ':

UPDATE ticket_mutex SET value=LAST_INSERT_ID(value+1) WHERE name='USER';SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                1 |+------------------+

After using this statement, we get the result 1, which is the value we need. Check the data records. we found that the total number of records has not changed, but the 'user' ID is already 1;

+------+-------+| name | value |+------+-------+| POST |     0 || USER |     1 |+------+-------+

In the same way, we can obtain the 'topic 'ID:

UPDATE ticket_mutex SET value=LAST_INSERT_ID(value+1) WHERE name='POST';SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                1 |+------------------+

View all records:

+------+-------+| name | value |+------+-------+| POST |     1 || USER |     1 |+------+-------+

From the above, we can see that through the LAST_INSERT_ID mechanism of MySQL, we can ensure that the business ID is continuously increased without increasing the total number of records, thus ensuring the uniqueness of the table Shard ID.

4. LAST_INSERT_ID description
From the name, we can see that LAST_INSERT_ID is the last inserted ID value. according to the instructions in the MySQL official manual, there are two ways to use it.
First, the parameter LAST_INSERT_ID () is not included. this method is used with the AUTO_INCREMENT attribute. when a record is added to a table with the 'auto _ INCREMENT 'attribute field, LAST_INSERT_ID () that is, the value of this field is returned. you can try it (I have verified it );
Second, there is an expression. for example, LAST_INSERT_ID (value + 1) described above, it returns the value of the expression, that is, 'value + 1 ';

------------------------
Thank you for your criticism:
Http://obullxl.iteye.com
Http://www.cnblogs.com/obullxl
Http://hi.baidu.com/obullxl
----------------------------- The fate is, the margin is, and the margin is gone! Blog: http://obullxl.iteye.com ------