Global Self-increase ID
In the single-machine MySQL environment, the self-increase field can help users generate the unique table value increasingly. However, when conditions of DRDS are changed, the original table is actually split to several sub-tables on the backend in terms of table sharding. Therefore, the traditional self-increase fields cannot meet users’ demands. For this reason, DRDS made related improvement, generating unique value for table split.
Remarks: The global self-increase ID specified in the document is applicable only to table split. For cases other than table split, the MySQL syntax can be directly used,
The global self-increase ID of DRDS can be used for generating self-increase ID as per the three methods below, depending on users’ definitions.
- SIMPLE: When the system applies for self-increase ID per time, the system database will be accessed and the self-increase ID is guaranteed to be purely incremental and unique. But the performance of this method is relatively low.
- GROUP (default): The system takes a section of continuous ID in advance and puts it in the memory of DRDS, assigning one GROUP to each DRDS node. When an application applies for self-increase ID, such ID is obtained from the memory, achieving extremely high performance. However, as the application may apply for IDs from two GROUPs, it can ensure that the self-increase IDs obtained with such method are unique, but they are not continuous and incremental.
- TIME: ID is generated with the approximation snowflake algorithm as per time stamps, time stamps can be accurate to the millisecond level and at least 2^16 self-increase IDs are supported by the same millisecond.
Note: For the self-increase ID with the TIME method, the field type must be bigint.
CREATE TABLE table_name (
[column definition] auto_increment by [SIMPLE| GROUP | TIME ],
[other column definition],
) auto_increment=<start value>
To cooperate with the global self-increase ID of DRDS, three variables are added to DRDS. The three variables **only supports global level setting rather than the session level, **thus it is suggested that assessment on whether there is any influence to other self-increase IDs shall be made in the case of modification.
- drds_auto_increment_increment: Correspond to auto_increment_increment of MySQL.
- drds_auto_increment_offset: Correspond to auto_increment_offset of MySQL.
- drds_group_size: If self-increase ID is applied with the group method, size of each group is 1000 by default.
Note: Set auto_increment_increment and auto_increment_offset of original MySQL in DRDS, which can be session level only, because set of global level is ineffective.
drds-related variables can be viewed by using 'show variables'
show variables like 'drds%'
- Create a table with the self-increase ID type of group and starting value as 10
create table increment_demo1(
id int auto_increment by group,
dbpartition by int_mod(id);
- For the self-increase ID with the TIME method, the field type must be bigint.
- The global self-increase ID of DRDS can be circularly used. Errors will be reported if it exceeds the maximum value. Therefore, please set an appropriate int field type.
- SIMPLE, GROUP and TIME are case insensitive.
- Where errors are reported to the insert statement, the current ID value will be abandoned, and a new ID will be applied for next insertion.
- If '0' is inserted for the self-increase ID field, the self-increase ID will be produced. This is the difference to MySQL.