Global Id_mysql based on Mysql extended functionality

This article uses the extended functionality of MySQL REPLACE into to generate global id,replace into and insert functionality, but when inserting new rows with replace into, if the newly inserted row's primary key or unique key (unique key) When an existing row repeats, an existing line is deleted before the new data row is inserted (REPLACE into is the original operation).

Create a table similar to the following:

CREATE TABLE ' tickets64 ' ( 
  ' id ' bigint () unsigned not null auto_increment, 
  ' stub ' char (1) NOT null default ', 
   primary key (' id '), 
  UNIQUE key ' stub ' (' Stub ') 

When you need to obtain a globally unique ID, execute the following SQL statement:

REPLACE into ' Tickets64 ' (' Stub ') VALUES (' a '); 

When this statement is executed for the first time, the Ticket64 table will contain the following data:

+--------+------+
| ID | Stub |
+--------+------+
|    1 | A |
+--------+------+

After executing the previous statement again, the row with the stub field value ' A ' already exists, so MySQL deletes the line first and then inserts it. Therefore, after the second execution, the Ticket64 table still has only one row of data, but the ID field has a value of 2. This table will always have only one row of data.

The better way:

For example, a ticket table is required to provide a sequential ID for all business tables. Now, let's look at our approach. First look at the table structure:

CREATE TABLE ' sequence ' ( 
  ' name ' varchar ') not NULL, 
  ' ID ' bigint (~) unsigned NOT null DEFAULT ' 0 ', 
  PRIMARY KEY (' name ') 

Note the difference, the ID field is not self added, nor is the primary key. Before using, we need to insert some initialization data first:

INSERT into ' sequence ' (' name ') VALUES  

Next, we can get the new photo ID by executing the following SQL statement:

UPDATE ' sequence ' SET ' id ' = last_insert_id (' id ' + 1) WHERE ' name ' = ' photos '; 

We performed an update to increase the ID field by 1 and passed the added value to the LAST_INSERT_ID function, specifying the return value of the last_insert_id.

In fact, we don't necessarily need to specify the name of the sequence beforehand. If we need a new sequence now, we can execute the following SQL statement directly:

INSERT into ' sequence ' (' name ') VALUES (' new_business ') on DUPLICATE KEY UPDATE ' id ' = last_insert_id (' id ' + 1); 
SELECT last_insert_id ();

Here, we use the insert ... On DUPLICATE KEY Update this MySQL extension, this extended feature also inserts a new row of records like insert, but updates are made to existing rows when the primary key or unique key (unique key) of the newly inserted row repeats with the existing row.

When we executed the above statement for the first time, because there was no field named ' New_business ', the insert operation was performed normally, and it should be noted that the starting value of the sequence obtained in this way is 0 instead of 1. The Select last_insert_id () returns 0 because the first execution does not execute the update, so there is no value passed for last_insert_id, and we do not have the self added field. But this should not be a big problem.

UPDATE: This method is easier to solve the single point of problem, and not limited to two servers, as long as the different servers set different initial values (but must be continuous), and then the increment to the number of servers on the line.

The above content is based on the MySQL extended function to generate a global ID of the relevant information, I hope you like.