Use LAST_INSERT_ID of MySQL to determine the unique ID value of each table shard.

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
Copy codeThe Code is as follows:
Create table ticket_mutex (
Name varchar (32) not null primary key comment 'service 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
Copy codeThe Code is as follows:
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 ':
Copy codeThe Code is as follows:
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;
Copy codeThe Code is as follows:
+ ------ + ------- +
| Name | value |
+ ------ + ------- +
| POST | 0 |
| USER | 1 |
+ ------ + ------- +

View All records:
Copy codeThe Code is as follows:
+ ------ + ------- +
| 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 ';