Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: AUTO_RANDOM生成的id过长
[TiDB Usage Environment] Production Environment, Testing Environment
[TiDB Version] v5.1.0
[Encountered Problem: Problem Phenomenon and Impact] The table creation statement in production uses AUTO_RANDOM (id
bigint(20) unsigned NOT NULL AUTO_RANDOM COMMENT ‘Auto-increment ID’), and the generated id is 20 digits, which exceeds the range of a long integer (8 bytes). The maximum value a long integer can accept is 9223372036854775807. Is it possible to generate only 15~18 digit ids, etc.?
If I remember correctly, it doesn’t support downward modifications.
Consider creating a new table, adjusting the size of the ID, and migrating the data over (but be sure to verify the digit settings, as I seem to recall that the digit settings might not necessarily take effect).
It should be using unsigned, so the value range reaches 2^64 (20 digits). If you want to reduce it by one digit, you can try adding (5, 63) after Auto_random. This feature is supported starting from version 6.3, which can truncate the value range of auto_random.
I didn’t see this document in version 5.1, it might not be supported.
It should be. I tested it on 5.4.3, and it doesn’t support this AUTO_RANDOM(3,36) syntax, but 6.5 does support it…
We changed to bigint(1) and bigint(20) for testing, both are 19 digits. Will the ID be exhausted in the future? I don’t think so, as there are so many values and they are random, not incremental.
So if you only need an 18-digit number, is the scheme of adding (5, 59) after Auto_random feasible? This means that 64-59=5, the first five digits are 0 to limit the ID range, the next 5 digits are random values scattered by time, and the remaining 59-5=54 digits are used for auto-increment.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.