What is the difference between SMALLINT(3) and INT(3)

Asked

Viewed 11,349 times

5

I have a question about the difference between two types of data in Mysql: That document has this table:

tebela com capacidades dos tipos

The standard construction of these types is SMALLINT(M) and INT(M) where M = numero máximo de dígitos que o campo irá receber. I need to store the resolution of an image that might be 72, or 240, or 300 DPI.

What is the difference between these two fields if I use SMALLINT(3) or INT(3)? Because from what I understand the big difference is in Storage and Min/Max Value Signed

mysql typing storage

  • 72,240 is a broken number, so you can’t use any kind of int.

  • @Robertodecampos is a comma to separate values, in vdd are integer values.

  • @Robertodecampos corrected

  • This raised me a beautiful question. I will ask!

4 answers

4


Well, the table already shows the difference. Trying to use other words:

  • the first takes 2 bytes and allows 65536 distinct numbers, usually half for each side
  • the second occupies 4 bytes and allows more than 4 billion variations being that the normal half negative and half positive. What differentiates is the storage capacity.

The issue of 3 in parentheses it will be displayed by default with 3 digits, which does not make much sense for the two cases, the 3 makes sense for the TINYINT. But note that it is just presentation, does not take up less or more space, does not change accuracy, accuracy, none of that. It is only when it presents in the database.

Obviously I will not repeat what is already in the question.

2

The difference is the range of values they accept (according to the table), i.e.:

Signed

SMALLINT: -32768 to 32767

INT: -2147483648 to 2147483647

Unsigned

SMALLINT: 0 to 65535

INT: 0 to 4294967295

Signed to Unsigned Difference

Each type has its limit in bytes (e.g.: SMALLINT 1 byte).

Signed: "divides" the possible amount between negative and positive.

Unsigned: "does not divide", thus accepting only positive numbers.

Value in parentheses

A very explanatory quote (Source):

5 - Values need to be filled with left zeros or have an expected width?

The answer to the fifth question is only used for formatting number. If we want the numbers to be returned with zeros to left, we use the modifier "ZEROFILL". Already the "width" of fields is used so the application can display the value with spaces to left. The width of the fields is returned between the metadata coming from a consultation.

The width is specified differently for integers and reals. In integers, the width is specified in parentheses, after the field name (for example "INT(11)"). For real numbers, a width is precisely the precision of the field (cited above), difference is that the accuracy number becomes the total width of the field, including with the score.

The biggest confusion I see around is the use of the "width" of the field finding it to be the "maximum digit capacity" of the field. That’s wrong. That is, if the field is INT(1), INT(2) or INT(11), its value maximum will continue to be "2.147.483.647". To specify fields with greater or lesser capacity, field variations shall be used (TINYINT, SMALLINT, MEDIUMINT or BIGINT for INT fields or use FLOAT or DOUBLE for floating point fields).

Note: The ZEROFILL option automatically applies the UNSIGNED option in the field, even if you have explicitly specified SIGNED.

Concluding your question:

You can use the SMALLINT at the BIGINT because everyone accepts the values: 72, or 240, or 300.

The value in parentheses (3), means that your column will be bounded by 3 visible digits, but the values will still be possible according to field boundaries.

Useful links

Official documentation

  • But there are no characters in it.

  • @Maniero truth, I started thinking about my question and went beyond.

  • Essentially what is written there is what is in the question, what is different is about the presentation limiter that has not been said anything.

  • 1

    @Maniero complemented!

-1

according to progression, accept ever-increasing number ranges.

tinyint = 1 byte
smallint = 2 bytes
mediumint = 3 bytes
int = 4 bytes
bigint = 8 bytes
  • 3

    Yes, this is in the question table.

-2

As for the quantity of characters, totally in agreement. As for (3), there are two things to think about using this. First, by indicating (3), your BD will always reserve this specific space for that value, whether you include value with 1 or 3 characters, which will improve with regard to the performance of queries, especially when this BD starts to take larger proportions. Now, if you think more about space, whenever you enter a specific amount of characters, this space will always be reserved, occupying more memory space than a dynamic field, like 'int()'. Obviously, with a dynamic field, there is the question that the query will first need to check the amount of characters before returning the value, as it can only return after the mapping.

Summarizing (with examples for integer numbers INT):

Int(): Allocates the amount of memory dynamically, depending on the number of characters included.

Int(3): Will allocate memory for 3 characters, regardless if it includes 1 or 999 in its result.

  • 1

    I think your answer comes up against this: Space occupied between different types of integers. So far I’m trying to find out whether or not integers actually reserve bytes. And if the answer you gave is correct, yours is incorrect.

  • It allocates space according to what you indicate... in the case of example (3), will allocate the 3 spaces, regardless of the type of integer you indicate... be it TINYINT or a normal INT.

  • They just answered there. It’s not like that. You’re talking about exactly the doubt that I had, because the VARCHAR for example, it does not allocate.

  • Well, VARCHAR really is different from using CHAR, for example. VARCHAR(10) you limit to 10, but do not occupy the 10 if you write 'hello', for example, occupying only the 5 characters. If you use CHAR(10), it should occupy the 10 spaces.

  • As I said in the other question, it caused me a lot of doubt, I opened a question: Dynamic space reserve

  • Well, a negative, but you confused my answer... It is nothing about VARCHAR nor whether it is INT or BIGINT, but rather about (1), (2) or (3), for example.

  • Cool... When I answered had not appeared its return. Let’s wait for a new information so that we have the most correct answer possible. Thanks.

  • Bruno, the negative is because it is incorrect, since the integers allocate the spaces. If you have any link that shows otherwise, post that we will see, and take the negative if it is correct.

  • You said it’s incorrect because the integers allocate space, that is, it’s exactly what I said, but okay, I’ll take it easy as soon as possible again.

  • Bruno, that’s not what I said, so much so that others didn’t understand it that way. What you’re getting in your answer is that integer types don’t allocate space, unless, you define a value, as you said here: "First, you enter (3), your BD will always reserve this specific space for that value, whether you include 1 or 3 character value"

  • Rephrase your answer, there is no problem in this. I am also beginner, learning by observing or erring, the important thing is to know the correct!

  • No, I did not say that it does not allocate space. I said that it allocates memory according to the number of characters it indicates, in this case it was INT(3).

  • But that’s exactly what’s wrong. It allocates all memory. Regardless of what you indicate. It’s there, in the other question I asked.

  • I think we’re saying the same thing and disagreeing with each other. Revised. See if that’s what you also understood.

  • No man... " Int(): Will allocate the amount of memory dynamically, depending on the number of characters included."This is wrong! The whole type allocates all space independent of having value there.

  • In any case I took the -1. But from what I understand so far, it is wrong.

  • Well, if that’s really it, I learned wrong, sorry then... I will search as soon as I get this question calmly here. Thank you.

  • If it’s really the way you’re saying it, everyone else is wrong. We will certainly thank you if you have any documentation about this to learn the right thing! Feedback is very important! Thanks!

  • You can leave, I will try to bring a resolution, not to find who is right, but so that we all have correct information. VLW!

Show 14 more comments

Browser other questions tagged mysql typing storage

You are not signed in. Login or sign up in order to post.