Menu
Although using daily in my programming , but never realized about this question :
since the int(unsigned) data type can hold values from 0 to 4294967295, what actually happens
If I declare this INT fields to be auto increment and one morning, it just reaches 4294967295?
The obvious answer will be it should throw an error that cannot insert further, but data never stops comin and we have to store the records that still keep commin. What to do in this case?
Also is declaring int(20) will be large enough than this default limit?
Any suggestions?
Thanks.
481
To avoid a situation like this where your resources of Server or Client are blocked by unwanted queries; MySQL Workbench has limited the number of rows to be retrieved by any single query to 1000. You can easily change this limit by going to MySQL Workbench >> Edit >> Preferences >> SQL Queries tab.
When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails. That is why it is advised to use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value required by us.
In MySQL, INTEGER (INT) is a numeric value without a decimal. It defines whole numbers that can be stored in a field or column. In addition, MySQL supports the display_width attribute (for example, INT(1)) and the ZEROFILL attribute, which automatically adds zeros to the value depending on the display width.
If you get 4 billion rows, the fact that the field will overflow is probably the least of your problems.
You can declare it to be BIGINT, which holds 8 bytes. Meaning that it will count up to 18446744073709551615.
If it overflows it will go back to 0, and keep inserting, returning an error if a row exists with that number.
154
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
