Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: auto_random id长度的设置方式
|
username: foxchan
Regarding the length of the id, I don’t quite understand. Is there a clear corresponding relationship?
For example, if you want to limit the id length to 11 digits, what should AUTO_RANDOM(x, xx) be here?
When considering compatibility for front-end calls, JavaScript has size limitations for integers. You generated a bigint. For example, if your ID is 998829288288488281882848488388384885838288484, JavaScript cannot parse it as an integer and it will overflow.
The front end now needs to be limited to 11 digits, as too long will result in loss of precision. So can auto_random be limited in length?
Sure, just provide the document address and follow its format.
According to the documentation, the parameter is 16 bits, but here it requires 11 bits.
If tomorrow the limit is 12 bits, how should it be set?
So what is the specific relationship between the configuration and the ID length?
autoradom (number of tikv, power of 2)
Does this make sense to you?
AUTO_RANDOM is an 8-byte bigint integer, where the highest bit is the sign bit, and by default, bits 63 to 59 are the random bits (shard bits). You cannot directly set the total bit length of AUTO_RANDOM, but you can influence the range and distribution of generated values by adjusting the number of random bits (shard bits). When creating or modifying the table structure, you can set the number of random bits by specifying the number in parentheses after AUTO_RANDOM. Therefore, it seems that there is no way to make it int(11).
“AUTO_RANDOM columns are currently only allowed to be defined on BIGINT and UNSIGNED BIGINT columns,” and the value range of BIGINT exceeds 11 digits. Therefore, if you want to limit the length of the id column to 11 digits, you cannot use AUTO_RANDOM. You can limit the value range by using id int, but id int can easily run out of space.
According to the official documentation, there should be such a requirement. However, my front-end restrictions are strict. If it doesn’t work, I’ll have to recreate the auto_increment.
After using auto_increment for a long time, it will also exceed 11 digits.
We’ll talk about it later; the business might be gone by then. 
For now, create the table using AUTO_RANDOM(3, 32). The id currently has a maximum of 10 digits.
The official concept explanation of AUTO_RANDOM is an attribute applied to columns of type BIGINT for automatic value allocation. If it must be limited to int, it is recommended to use AUTO_INCREMENT.
Should it be explained as autorandom (number of tikv, 2 to the power of N minus 1)?
I hope to limit the ID length to 11 digits. What should be the values for AUTO_RANDOM(x, xx) here?
The first x depends on the number of your TiKV instances. For example, if you have 16 TiKV instances, x should be set to log(2, 16), which is 4. If you have 32 TiKV instances, then x should be set to log(2, 32), which is 5.
The second xx depends on the desired ID length. For example, if the ID is limited to 11 digits, then this xx length is approximately equal to log(2, 99999999999), which is about 33. If you want to limit it to 15 digits, then this xx length is approximately equal to log(2, 999999999999999), which is about 49.
Additionally, if you do not set xx and only set AUTO_RANDOM(x), the default xx will be 64-x-1.
Roughly like this.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: