Space occupied between different types of integers
By the table of official MySQL documentation, and a question here in the SOpt, a doubt arose.
Considering the table:
Let's assume that I will use a TINYINT and a BIGINT to store the same value (e.g. 240).
Question:
- doesn't it "Reserve" all the bytes for that record(would the occupied space be dynamic)?
- if dynamic, they would have the 2 same size in disk space?
- If you don't" Reserve "this space, how does it" increase " afterwards?
- does this change from bank to bank?
3 answers
Numeric data has fixed space always, when you choose the type it is determining how much space it will occupy in the row of that table, it does not change. And the table presented is showing this. It's the same in any language.
So if you said you want a BIGINT it will take up 8 bytes, but that can store something that fits 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 the space is your problem. If always not use, probably chose the wrong type. If you have a number that does not fit in the space also chose wrong, will give error try to put there.
VARCHAR it's something else, there is an inherently variable data. What we're talking about here goes further in the row of CHAR which has the size defined in its table structure statement. Just remembering that CHAR you define how many characters will have in that column, and then it does not change, if you say that it is 10, it will always be 10, even if you do not use all. And so maybe people think that the SMALLINT(3) it has to do with characters, and it's something else completely different, the numbers have the allocated space defined independent of that number in the parentheses.
Is not so in all databases, the SQLite for example only has the INT, and the occupied size varies according to the accuracy that the number requires. Occupies from 1 byte (if 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 trims to evaluate, except for the ninth byte which, if it exists, can multiply the possible 256 of a byte, which would make it have 8 useful bytes and one which is the sum of the control bits.
There is reservation of all bytes, I still do not understand why it can give the opposite perception if the table presented makes this clear. And I still don't understand if some of the other questions make sense.
But nothing would stop it being different, as it is in some databases. In general the lines may have different sizes because of the VARCHAR or some kind of BLOB, if the number is also like that, nothing changes, in many cases it can be advantageous. Think of a line as if it were always a VARCHAR that has several segments (a BLOB would be even better), and in fact internally it is usually treated like this, there is a key, usually the ID and a value ( key-value) that would be that BLOB. Already in an index the pair what is called Key is the key that everyone knows and the value is the ID or other PK of the table.
In the case of ints, the entire space of the variable int is allocated even if its value is 1 or 100. There are even sectors in large developers that evaluate what is the most viable way to declare the types of fields in a database to maximize disk space savings.
Actually, I just answered in the thread that cited something in this sense. If you set the size, it is memory that will be used, even without use. Generates a facility for the search, improving performance (since you do not need to map the fields before returning). As for getting 'broken', it is as if they were blank fields for memory, to fill that space, different from the dynamic that allocates as needed.