The table structure is the same except for the index of the table name, and if the ' id ' field of each table is still in the ' auto_increment ' way, the ID cannot only determine a single record.
This requires a mechanism in addition to the various tables to generate IDs, we generally use a separate data table (may wish to assume that the table name is ' Ticket_mutex ') to save this ID, no matter which table has the data increase, it is first to the Ticket_mutex table with the ID value plus 1, Then get the ID value.
The operation of this ID seems very complex, fortunately, MySQL provides a last_insert_id mechanism, so that we can complete one step.
1, new data table Ticket_mutex
Copy Code code as follows:
CREATE TABLE Ticket_mutex (
Name varchar not NULL PRIMARY KEY COMMENT ' business name ',
Value bigint (a) UNSIGNED not NULL COMMENT ' ID value '
Engine=innodb DEFAULT Charset=utf8 COMMENT ' Save table ID table ';
The field ' name ' is used to indicate which business the ID is, such as the ID of the ' user ', which we can specify as ' users ';
Field ' value ' is the ID value of the business.
2, initialization of the business and its ID value
Copy Code code as follows:
INSERT into Ticket_mutex (name, value) VALUES (' USER ', 0), (' POST ', 0);
+------+-------+
| name | Value |
+------+-------+
| POST | 0 |
| USER | 0 |
+------+-------+
We have initialized 2 records, that is, 2 different businesses, representing ' user information ' and ' subject information ' respectively, and their initial ID values are ' 0 ';
3, get the table unique ID
This is the time to take advantage of the last_insert_id () mechanism provided by MySQL.
When you add a new piece of data to the user table, get the ' User ID ':
Copy Code code as follows:
UPDATE Ticket_mutex SET value=last_insert_id (value+1) WHERE name= ' USER '; SELECT last_insert_id ();
+------------------+
| last_insert_id () |
+------------------+
| 1 |
+------------------+
After this statement, we get the result of 1, which is the value we need. To view the data records, we found that the total number of records did not change, but the ' user ' ID is already 1;
Copy Code code as follows:
+------+-------+
| name | Value |
+------+-------+
| POST | 0 |
| USER | 1 |
+------+-------+
To view all records:
Copy Code code as follows:
+------+-------+
| name | Value |
+------+-------+
| POST | 1 |
| USER | 1 |
+------+-------+
From the above can be seen, through the MySQL last_insert_id mechanism, we can ensure that the total number of records does not grow, so that the business ID is constantly increasing, thus ensuring the uniqueness of the table ID.
4, last_insert_id description
As can be seen from the name, last_insert_id is the last inserted ID value, according to the official MySQL manual, it has 2 ways to use
One is without parameters: last_insert_id (), this method is used with the Auto_increment property, and when the record is added to the table with the ' Auto_increment ' property field, last_insert_id () returns the value of the field. Everyone can try (I have verified);
The second is an expression: as described above last_insert_id (value+1), it returns the value of the expression, that is ' value+1 ';
