Geek asks and answers

MySQL 5 atomic identifier generator

My logic is to know the table identifier before INSERT (setting the identifier manually when inserting). Since the logic using this identifier is located on several servers, allowing mysql to create the identifier may be a good idea, but the function I wrote is not atomic, so it will be returned with some identification numbers rather than unique numbers under heavy load:

CREATE FUNCTION `generate_document_log_id`() RETURNS BIGINT(16)
BEGIN
    DECLARE R_ID BIGINT(16);
    UPDATE document_log_sequence SET id = id + 1;
    SELECT id INTO R_ID FROM document_log_sequence;
    RETURN R_ID;
END

I use the table "document_log_sequence" with one column "id" to store the last identifier and increase it for each new identifier. Perhaps the solution is far from this, but I have no more ideas.

I forgot to explain: There is only one database server and n number of clients. The above function is not (kind of) thread safe.

+3
source share
3 answers

You can use the alternating identifier strategy

Each server is numbered from 0 to n and adds its server number to the sequence that executes n. One of the implementations would be to have a table AUTO_INCREMENTfor generating identifiers, then use this identifier in such a function:

CREATE TABLE ID_CREATOR (ID INT AUTO_INCREMENT PRIMARY KEY);

Assuming there are 3 servers, and this server is server number 1, this function will give a unique value for each server:

CREATE FUNCTION generate_document_log_id() RETURNS INT
BEGIN
    DECLARE NUMBER_OF_SERVERS INT DEFAULT 3; -- Or read from some table
    DECLARE THIS_SERVER_ID INT DEFAULT 1; -- Or read from some table. Must be less than NUMBER_OF_SERVERS
    INSERT INTO ID_CREATOR VALUES (); -- This creates the next id
    RETURN LAST_INSERT_ID() * NUMBER_OF_SERVERS + THIS_SERVER_ID;  -- Unique numbers that interleave each other
END
+1
source

FYI, if you have only one database server, this will give you the value of the following identifier to insert:

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_NAME = 'my_table'
AND TABLE_SCHEMA = 'my_schema';

(EDITED - )

+1

1) UUID, 16 (128 ); n :

uuid(); (, CHAR (32) UUID , MySQL os GUIID (128 ) )

, :

BEGIN
DECLARE str_uuid varchar(32);
DECLARE cur1 CURSOR FOR select replace (o.oid, '-', '') from (select lower(UUID()) as oid) as o;
open cur1;
fetch cur1 into str_uuid;
close cur1;
return str_uuid;
END

2) INT BIGINT, :

my_last_id, dt_hr_id (my_last_id - AUTOINCREMENT) ,

  • INSERT INTO YourTable (dt_hr_id) (NOW());
  • SELECT LAS_INSERT_ID(); (SELECT , )

;

This can be done inside stored procedures / stored functions, but you need to know that MySQL does not support multiple transactions in the same connection, so you need to do this in an isolated connection.

0
source

More articles:


All Articles