Space occupied between different types of integers

Question

By the table of official Mysql documentation, and a question here at Sopt, I had a question.

Considering the table:

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
TINYINT	1	-128	0	127	255
SMALLINT	2	-32768	0	32767	65535
MEDIUMINT	3	-8388608	0	8388607	16777215
INT	4	-2147483648	0	2147483647	4294967295
BIGINT	8	-263	0	263-1	264-1

Suppose I use one TINYINT and a BIGINT to store the same value (ex.: 240).

---

I ask you:

• It does not "reserve" all bytes for that record (it would be dynamic occupied space)?
• If dynamic, the 2 would be the same size in disk space?
• If not "reserve" that space, as it "increases" afterwards?
• This changes from bank to bank?

Answer 1

Numerical data has fixed space always, when you choose the type you are determining how much space you will occupy in that table row, it does not change. And the table presented is showing this. It’s the same in any language.

So if you said you want one BIGINT it will take 8 bytes, but it can store something that fits in a smaller type. It’s like on a sheet of paper, the space to put multiple digits is there, if you don’t use all the space is your problem. If you do not always use, you probably have chosen the wrong type. If you have a number that does not fit in the space also you have chosen wrong, it will be a mistake to try to put there.

VARCHAR is another thing, there is an inherently variable datum. What we are talking about here goes further on the line of CHAR which has the size defined in its statement of the table structure. Just remembering that the CHAR you define how many characters will be in that column, and then it doesn’t change, if you say it is 10, it will always be 10, even if you don’t use all of them. And so maybe people think that the SMALLINT(3) has to do with characters, and it’s something else entirely, the numbers have the allocated space set independent of that number in parentheses.

Not so in all databases, Sqlite for example only has the INT, and the occupied size varies according to the accuracy that the number requires. Occupies 1 byte (if not null) up to 9 bytes (each extra byte adds a 128 multiplication since 1 bit is used to tell if it has a new byte to evaluate, other than the ninth byte which, if it exists, can multiply the 256 possible of a byte, which would have 8 useful bytes and one which is the sum of the control bits.

There is reserve 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 prevent it from 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 this, nothing changes, in many cases it can be advantageous. Think of a line as if it were always a VARCHAR which 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 this 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 table PK.

Answer 2

In the case of ints, the entire space of the int variable 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 field types in a database to maximize disk space savings.

Answer 3

Actually, I just answered on the topic that quoted something in this sense. If you fix the size, it is memory that will be used, even without use. Generates a facility for research, improving performance (because you do not need to map the fields before returning). As for getting 'broken', it looks like blank fields for memory, to fill that space, different from the dynamic that is allocating as needed.