From the official MySQL documentation table, and a question here on SOpt , I had a question.
Considering the table:
Let'ssayI'mgoingtouseaTINYINTandaBIGINTtostorethesamevalue(eg240).
Iask:
Numeric data always have fixed space; when you choose the type you are determining how much space you will occupy in the row of that table, it does not change. And the table shown is showing this. It's the same in any language.
So if you said you want a
BIGINT it will occupy 8 bytes, but you can store something that fits into a smaller type. It's like on a sheet of paper, the space to put several digits is there, if you do not use all space it's your problem. If you do not always use it, you probably chose the wrong type. If you have a number that does not fit in the space also chose wrong, it will give an error try to put there.
VARCHAR is something else, there is inherently variable data. What we're talking about here is more in the line of CHAR that has the size defined in your table structure declaration. Just remembering that CHAR you define how many characters you have in that column, and there it does not change, if you say that it is 10, it will always be 10, even if you do not use all of them. And so maybe people think SMALLINT(3) has to do with characters, and it's something else entirely. , the numbers have the allocated space defined independent of that number in parentheses.
This is not the case for all databases, for example SQLite only has INT , and the size occupied varies according to the precision that the number requires. It occupies 1 byte (if it is not null) up to 9 bytes (each extra byte adds a multiplication of 128 since 1 bit is used to inform if it has a new byte to evaluate, other than the ninth byte that, if it exists, can multiplying the 256 possible one byte, which would make it 8 usable bytes and one that is the sum of the control bits.
There are all bytes reserved, I still do not understand why it might give the contrary perception if the table presented makes this clear. And I still do not understand if some of the other questions make sense.
But nothing would prevent it from being different, as it is in some database. In general the rows can be different sizes because of VARCHAR or some type of BLOB , if the number is also so, nothing changes, in many cases, can be advantageous. Think of a line as if it were always a VARCHAR that has multiple segments (a BLOB would be even better), and indeed internally it is usually treated like this, there is a key, usually ID and a value > key-value ) that would be this BLOB . Already in an index the pair what is called key is the key that everyone knows and the value is ID or another PK of the table.
In the case of ints, the entire space of the int variable is allocated even if the value of the variable is 1 or 100. There are even sectors in large developers that evaluate the most feasible way to declare field types in a database of data to maximize disk space savings.
Actually, I just answered on the topic that quoted something along those lines. If you fix the size, it is memory that will be used, even without use. It generates a search facility, improving performance (since you do not need to map fields before returning). As for being 'broken', it is as if they were blank fields for memory, to fill that space, different from the dynamic that it allocates as needed.