[SOP Series 34] Empirical Test of bigint Maximum Value

Translated
1

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

Original topic: 【SOP 系列 34】bigint最大值实测

| username: Billmay表妹

Thanks @wl21787 for sharing:

Background

TiDB Version: V3.0.8

Data Type: bigint

When using the bigint data type, it is unclear what the specific upper limit is. If a large value is inserted, ERROR 1467 (HY000): Failed to read auto-increment value from storage engine will occur.

Test

Test Table

CREATE TABLE t(id bigint UNIQUE KEY AUTO_INCREMENT, a int NOT NULL DEFAULT 0);

Test Data

insert into t(id) values(18446744073709551616);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

Where 18446744073709551616 = 2^64

Continue testing after truncating

truncate table t;

Due to limited technical capability, binary search was used for continuous testing, and the final maximum value was obtained

mysql> insert into t(id) values(9223372036854775805);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values();
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

Summary: For the bigint data type with auto-increment, the maximum value is 9223372036854775805.

2
| username: forever | Original post link

To be precise, the range for unsigned is 0-18446744073709551615, and for signed, since there are no negative numbers for primary keys, the range is 0-9223372036854775807.

3
| username: Raymond | Original post link

So, if we use bigint for auto_random, is it possible to generate two identical random values within this range?

4
| username: Hacker007 | Original post link

Calculate the probability of this… It’s basically impossible, right?