CODEWITHSUNDEEP
mysqlsqldatabase
Michał Skrzypek
Michał Skrzypek

Reputation: 699

How to correctly set id limit in mySQL?

I have a question about how to set an id field in mySql table. This may seem basic for some of you but I cannot find the correct answer.

I have a pivot table with fields: 

id          (int(11)) 
store_id    (int(11))
product_id  (int(11))
price       (decimal(10,2))

Every time the table is updated all the data is purged and new data is inserted, so the table never contains more than 20000 records, so not much.

However, I noticed that the id field (which is a primary key and is autoincremented) always assigns higher ids that the ones that were "freed" during deleting data.

I'm afraid at some point I will run out of numbers. Is int(11) enough? Should I set a different value? Or maybe will the numbering start from the top while I reach the limit?

Please advise. I cannot submit any code, since there is no coding involved.

Upvotes: 0

Views: 55

Answers (2)

frosty
frosty

Reputation: 69

int(11) can be between -2147483648 and 2147483647 (Signed) or 4294967295 (Unsigned). So you dont need to worry about running out of numbers.

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

If your table is not referenced with any other table then you can Truncate table instead of delete it. So when you insert the new data it will again start with 1

Upvotes: 1

Related Questions