AUTO_RANDOM generated ID is too long

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: AUTO_RANDOM生成的id过长

| username: jingyesi3401

[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.?

| username: ealam_小羽 | Original post link

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).

| username: Yves | Original post link

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.

| username: ealam_小羽 | Original post link

I didn’t see this document in version 5.1, it might not be supported.

| username: tidb菜鸟一只 | Original post link

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…

| username: jingyesi3401 | Original post link

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.

| username: Yves | Original post link

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.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.