Consider two column type declarations:

VARCHAR(20) и VARCHAR(150)

Except how is the data limitation in length, does it differ? For example, in the second version, the table will weigh more, even if all the values ​​in the columns are NULL or something else?

Still, for example, quite often to indicate the type, which can be either true or false, the column is TINYINT(1) , and not TINYINT(255) . After all, TINYINT is 1 byte anyway, isn't it?

In general, I would like to know what the differences in such records. What is the advantage of limiting a column if it will take up as many bytes as indicated in the specification (I think). Is it loaded into memory less or does the data physically weigh less?

    1 answer 1

    In general, the answer is difficult to give, because the methods of implementation in different DBMS may differ. I will answer on the example of MySQL, since there is documentation , as well as source code.

    VARCHAR requires exactly as many bytes as the user saves, plus bytes for storing the length of the string. Up to a length of 255, MySQL allocates 1 byte, more than 255 - 2 bytes.

    The nullable field also requires one bit in the record header, but it can be disregarded, as it relates to another storage aspect.

    Empty string '' for type VARCHAR(100) will occupy 1 byte (which will store the length of string 0), and for type VARCHAR(300) - 2 bytes (which will also store string length 0, but for the range from 0 to 65535 ). The string 'abcd' will take 5 and 6 bytes, respectively.

    Thus, you can save 1 byte per record, reducing the length of VARCHAR fields to 255 characters. Practically, it does not make sense, given the cost of one terabyte of data in the clouds (about $ 10 per year ).

    It is more practical to rely on the subject area and the convenience of maintenance. TINYINT(1) tells the programmer who sees the code for the first time that we store a Boolean value, not a small integer number. He quickly understands the purpose of the field.

    VARCHAR(8000) for storing URLs is based on RFC-7230 :

    It is RECOMMENDED that all HTTP senders and recipients support, at a minimum, request-line lengths of 8000 octets.

    • That is, in the case of TINYINT (mysql), even if there are 100 million entries, regardless of whether it is declared as (1) or (255), there is no difference in size and this is just for acceptable perception? - Alexey Shimansky
    • Yes. At the same time, perception is much more important than volume. Saving one byte for one hundred million entries & nbsp; & mdash; this is 100 megabytes. Cost & nbsp; & mdash; whether hundredths or thousandths a year. But the fact that your colleague will immediately understand what you meant when writing the code is priceless. Anyone who understands someone else's code will understand. - Mark Shevchenko
    • Understood thanks. - Alexey Shimansky