Implementation of auto-incrementing sequence generator _ MySQL

Implementation of auto-incrementing sequence generator bitsCN.com
Implementation idea of auto-incrementing sequence generator l requirement u achieves the equivalent function of auto-incrementing field values in MySQL; the auto-incrementing field values in database tables in the same application cluster are globally unique; u supports horizontal sharding tables at the database level, and also supports horizontal sharding tables within the database, whose ID values are from the same configuration record; l the storage design sequence generator configuration table increment_config structure: column name data type empty default value auto-incrementing primary key/index remarks id int unsigned n AUTO_INCREMENT primary key (ID) unique identifier, meaningless TABLE_NAME VARCHAR (40) n unique index table name TABLE_TOTAL tinyint unsigned n 0 mark the number of table shards in the database COLUMN_NAME VARCHAR (40) N field name START_VALUE bigint unsigned n 1 start value OFFSET_VALUE smallint unsigned n 10000 growth step flag tinyint n 0 0 0-normal; 1-void value GMT_MODIFIED timestamp n database automatically updates this value
Note: 1>. GMT_MODIFIED is automatically filled in and updated through database features rather than automatically updated by programs or manually; 2>. for horizontal table shards in the database, that is, TABLE_TOTAL <> 0, they are differentiated by TABLE_NAME + TABLE_TOTAL; l for tables whose operations u initialize for the storage table, add a record to the configuration table, for example, insert into (TABLE_NAME, TABLE_TOTAL, COLUMN_NAME, START_VALUE, OFFSET_VALUE, FLAG) VALUES ('MSG _ ', 23, 'MSG _ id ', 1, 0); the SQL program operated by the u sequence generator obtains the sequence interval segment each time, and the SQL statement used to update the transaction process for related data value operations. assume that the corresponding records of the msg _ series table are, ID = 1 in the configuration table. Start trnsaction; select id, TABLE_NAME, COLUMN_NAME, START_VALUE, START_VALUE + OFFSET_VALUE AS your increment_config where id = 1 for update; UPDATE increment_config SET START_VALUE = START_VALUE + OFFSET_VALUE where id = 1; COMMIT; l program implementation we recommend that u improve the service processing performance by providing the speed at which the sequence is generated. The program needs to be implemented in the form of sequential intervals, instead of getting the serial number value from the database each time it is needed. during each startup of the u program, to initialize the sequence interval values of all valid records in the configuration table; u when the range value obtained by the program START_VALUE reaches: START_VALUE = END_VALUE after a period of use, the processing steps are as follows: 1>. suspends the current sequential call request; 2>. perform the transaction to obtain the sequence range value; 3>. the START_VALUE of the newly obtained range value is sent to a pending call request. Conclusion: the maximum value (END_VALUE) of each interval value is always used as the value of the current period. Author snoopy7713bitsCN.com