What is the default limit for auto generated primary Column(Integer) in SQLAlchemy with FastAPI and MYSQL?

Question

Currently, I'm using Python's fastAPI with SQLAlchemy and MYSQL. I've defined an auto-generated primary column as shown below. What's the maximum number that this column can hold? Currently, the DB grows by 10k records per day, and I clear 70% of it on the weekends. The number of records will always stay constant, but I want to know the maximum limit that 'id' can hold before breaking down. This 'id' column is important to me, for sorting the results, and I don't want it to overflow somehow.

class SampleData(SampleTable):
__tablename__ = 'demotable'

id = Column(Integer, primary_key=True)
col1 = Column(String(100))
col2 = Column(String(100))

What's the maximum value that the column 'id' can ever hold?

see [integer maximum value mysql](https://www.google.com/search?q=integer+maximum+value+mysql) or : [What is the size of column of int(11) in mysql in bytes?](https://stackoverflow.com/questions/5634104/what-is-the-size-of-column-of-int11-in-mysql-in-bytes) — Luuk, Jun 23 '21 at 16:22

Bill Karwin · Accepted Answer · 2021-06-23T16:32:33.783

1

The maximum value of a signed integer in MySQL (as well as most other programming languages) is 2³²-1, or 2147483647.

This is documented: https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

If you use 10k id values per day, that will overflow in 214748 days, or just short of 588 years.

If that's not enough, then make your id column a BIGINT. Then it will last until after Earth's Sun envelops the Earth.

edited Jun 23 '21 at 16:32

answered Jun 23 '21 at 16:30

Bill Karwin

499,602
82
638
795

1

@Luuk Your comment is unkind and unnecessary. – Bill Karwin Jun 23 '21 at 16:42

What is the default limit for auto generated primary Column(Integer) in SQLAlchemy with FastAPI and MYSQL?

1 Answers1