4

I have a question I'd like to help me solve, it's about the values of the data type in the fields of the database.

  1. It is mandatory to specify the number of characters or values that will have a field identifier id INT.

id INT UNSIGNED NOT NULL AUTO_INCREMENT

  1. What is the difference between id INT and id INT(11), It is mandatory to establish a number of values?.

id INT UNSIGNED NOT NULL AUTO_INCREMENT

id(11) INT UNSIGNED NOT NULL AUTO_INCREMENT

  1. What is the default setting MySQL in id INT, if not specify any value?

  2. What is the maximum amount exact numbers that allows me to add INT

  3. In that case you must use INT

  4. In that case you should use BIGINT

Example: I will take an example of a news portal, that I could receive many visits.

Need to record the number of times it is accessed worldwide news, let's say your news year is visited 999.999.999 times, is a bit exaggerated know :), but it is valid to use in this case INT or BIGINT

I much appreciate your support.

Improve this question
2
  • 3
    Useful: dev.mysql.com/doc/refman/5.7/en/integer-types.html Commented Jan 7, 2016 at 19:16
  • All you need to know is what the length of the data is. INT is short for 4 byte integer, which means it's a number that can be allocated within 4 bytes. 1 byte = 8 bits, therefore 4 bytes = 32 bits which equals to 2 to the power of 32. When storing unsigned value, that's ~ 4.2 billion. So, if you think that 4.2 billion isn't enough, you can amp it up to bigint, which is 2 ^ 64 - now that's a quite large number, you'd take a few million years to exceed that one :) Commented Jan 7, 2016 at 22:05

2 Answers 2

Reset to default
4

According to Numeric Type Attributes:

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

And according to Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT:

The datatype INT uses 4 bytes (from -2147483648 to 2147483647 or unsigned from 0 to 4294967295), the datatype BIGINT uses 8 bytes (from -9223372036854775808 to 9223372036854775807 or unsigned from 0 to 18446744073709551615).

So the answers to your questions in my opinion are:

  • Ad 1) No.
  • Ad 2) The display with as described above.
  • Ad 3) No display width will be specified.

  • Ad 3) The display with will be set to the default value for datatype INT. That is 10 for unsigned and 11 for signed (signed has more to allow space for a leading dash character, the minus sign, for negative values).

    Thanks spencer7593 for the correction.

  • Ad 4) See above.

  • Ad 5 and 6) Up to 2147483647 you can use INT, above that value you can must either use unsigned INT or BIGINT.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+1. but 3) The display width will be set to the default value for datatype INT. That's 10 for unsigned, and 11 for signed.(signed has one more to allow space for a leading dash character, the minus sign, for negative values.
2
  1. It is mandatory to specify the number of characters or values that will have a field identifier id INT.

id INT UNSIGNED NOT NULL AUTO_INCREMENT

ANS: In the Database the primary key begins at one and increments by one. So 1,2, ... For this reason, you do not have to specify UNSIGNED

  1. What is the difference between id INT and id INT(11), It is mandatory to establish a number of values?.

id INT UNSIGNED NOT NULL AUTO_INCREMENT

// the (11) would be on the datatype, not the name of the column.

id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT

ANS: When you specify the limit, that is the maximum number of characters it can go. The maximum for an INT is 2147483647. In an INT, the limit does not affect

  1. What is the default setting MySQL in id INT, if not specify any value?

ANS: the Default for an INT is 0 unless it is a Primary Key, in which case it is a 1

  1. What is the maximum amount exact numbers that allows me to add INT?

enter image description here

  1. In that case you must use INT

Ans: For Primary Keys or for a column in which you always want a whole number. Example: Quantity of something.

  1. In that case you should use BIGINT

Ans:You can use BIGINT for when the number is a lot more than what an INT can hold

You can refer to http://dev.mysql.com/doc/refman/5.7/en/integer-types.html

Improve this answer

6 Comments

Wow... you were fast... I started answering immediately as soon as I saw the question :D
Wao if it is fast :), Ok then I can deduce that the maximum value for INT is: Two billion, one hundred and forty-seven million, four hundred and eighty-three thousand and six hundred and forty-seven - And BIGINT Nine quintillion, two hundred and twenty-three quadrillion, three hundred and seventy-two trillion, thirty-six billion, eight hundred and fifty-four million, seven hundred and seventy-five thousand and eight hundred and seven.
@Learningandsharing Yeah, the BIGINT is a bigger int, in terms of the maximum and minimum it can hold dev.mysql.com/doc/refman/5.7/en/integer-types.html
Thank you very much for helping to resolve the doubts, it has become very clear to me!
"It is not mandatory to specify the length. It is preferred if you do as a limit would use less DB space" This is incorrect. In case of integer types the length parameter indicates the display width (which is ignored by most of the clients anyway) and may be used in conjunction with the zerofill attribute. It does not influence the space required to store the given data. If you want to use less storage space, then smller integer data type must be used, such as tinyint or mediumint
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.