Generate a global ID based on the MySql extension function,

Generate a global ID based on the MySql extension function,

This article uses the MySQL extension function replace into to generate a global id. replace into is the same as INSERT. However, when replace into is used to INSERT new data, if the primary Key or UNIQUE Key (UNIQUE Key) of the newly inserted row already exists, the existing row will be deleted first, then Insert the new data row (replace into is the original operation ).

Create a table similar to the following:

CREATE TABLE `tickets64` (   `id` bigint(20) unsigned NOT NULL auto_increment,   `stub` char(1) NOT NULL default '',   PRIMARY KEY (`id`),   UNIQUE KEY `stub` (`stub`) ) ENGINE=MyISAM; 

To obtain a globally unique ID, run the following SQL statement:

REPLACE INTO `tickets64` (`stub`) VALUES ('a'); SELECT LAST_INSERT_ID(); 

After this statement is run for the first time, the ticket64 table will contain the following data:

+ -------- + ------ +
| Id | stub |
+ -------- + ------ +
| 1 | a |
+ -------- + ------ +

Run the preceding statement again later. The row with the stub field value 'a' already exists. Therefore, MySQL deletes the row and inserts it again. Therefore, after the second execution, the ticket64 table still has only one row of data, but the value of the id field is 2. This table will have only one row of data.

Better method:

For example, you only need a ticket table to provide continuous IDs for all business tables. Next, let's take a look at our method. First, let's take a look at the table structure:

CREATE TABLE `sequence` (   `name` varchar(50) NOT NULL,   `id` bigint(20) unsigned NOT NULL DEFAULT '0',   PRIMARY KEY (`name`) ) ENGINE=InnoDB; 

Note that the id field is neither auto-incrementing nor primary key. Before use, we need to insert some initialization data:

INSERT INTO `sequence` (`name`) VALUES  ('users'), ('photos'), ('albums'), ('comments'); 

Next, we can execute the following SQL statement to obtain the new photo ID:

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 1) WHERE `name` = 'photos'; SELECT LAST_INSERT_ID(); 

We performed an update operation, adding 1 to the id field, and passing the added value to the LAST_INSERT_ID function, thus specifying the return value of LAST_INSERT_ID.

In fact, we do not need to specify the sequence name in advance. If we need a new sequence, we can directly execute the following SQL statement:

INSERT INTO `sequence` (`name`) VALUES('new_business') ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id` + 1); SELECT LAST_INSERT_ID();

Here, we use INSERT... On duplicate key update MySQL extension, this extension function inserts a new record for a row like INSERT, but when the primary Key or unique key (UNIQUE Key) of the newly inserted row) if it is the same as an existing row, the UPDATE operation is performed on the existing row.

When we execute the preceding statement for the first time, the insert operation is normal because there is no field named 'new _ business, note that the starting value of the sequence obtained in this way is 0, rather than 1. Because UPDATE is not executed during the first execution, the value is not passed for LAST_INSERT_ID, and we do not have an auto-increment field. Therefore, SELECT LAST_INSERT_ID () will return 0. However, this should not be a big problem.

UPDATE: This method is easier to solve single point of failure (spof) and is not limited to two servers. You only need to set different initial values (but must be continuous) for different servers ), then, you can change the incremental value to the number of servers.

The above content is related to generating a global ID based on the MySql extension function. I hope you will like it.

Articles you may be interested in:

• How to solve the "unable to load mysql extension" problem that may be encountered during PHP installation
• Ext/mysql extension of PHP source code
• Php cannot load mysql extension
• How to connect to the database after mysqli extension is enabled in php
• Solution to the lack of mysqli extension in phpmyadmin
• Mysql global ID Generation Method