About choosing the right length for a BIGINT field in MySQL

Question

MySQL

It is known that for MySQL, in general, there is no linear dependence of the disk space spent on storing data on the character length of this data. A classic example of this feature is the behavior of VARCHAR fields: the string 'abcd' takes up 5 bytes when placed in a VARCHAR field, and 4 bytes when stored in a CHAR(4) field. This phenomenon is described in detail in the manual at dev.mysql.com/doc/refman/5.1/en/char.html

However, the cost of storing the row itself is not limited - there are also indexes, there are external links, and probably something else that stays out of the client's field of vision. Something that responds to the correct choice of field type and length by varying amounts of disk space being consumed.

My question is this:there is a table with an INT(11) UNSIGNED NOT NULL AUTO_INCREMENT field that has run out of valid values ​​- there can be 4294967295 of them for the UNSIGNED field, which can be found in the manual at dev.mysql.com/doc/refman/5.1/en/numeric -types.htmland on real experience, inserting id into the table and trying to do the next insert using auto_increment - we get Duplicate entry '4294967295'. It is impossible to delete part of the data from the table, I need to write further into it. Accordingly, you need to do ALTER TABLE and change the field type to BIGINT, for which the maximum length of the UNSIGNED value is 20 digits, and the maximum value is 18446744073709551615. But I don’t need so much! IDs are added at such a speed that some BIGINT(14) would be enough for me for the next six months, and then we'll see. So - what is the practical difference between using BIGINT(M) and BIGINT(20), where 10 < M < 20?There is a strong suspicion that using BIGINT(20) will create a noticeable disk overhead and I/O overhead compared to a smaller BIGINT field, but I could not find confirmation of this suspicion. It is only known that no more than 4 bytes are spent for storing INT

(11), and no more than 8 bytes for INT(20 ) .

KEY from other tables with cascading CONSTRAINT on UPDATE.

I'd love to hear your thoughts on the correct BIGINT length selection algorithm, the correctness of my thoughts, and the MySQL mechanisms that deal with client-declared field lengths.

In the meantime, having learned from the bitter experience of a sudden stop in production due to the fact that the field ran into INT (11), I can wish all administrators to check the values ​​of auto-increment counters in their databases more often, so as not to repeat my fate, having received an accident with a serious downtime on empty place.

Answer 1

If I understand the manual correctly, then M is "display width" and "display width does not constrain the range of values ​​that can be stored in the column". Those. BIGINT will always be allocated 8 bytes.