MySQL - Default value in field type INT

Question

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

2. 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

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

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

5. In that case you must use INT

6. 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.

Answer 1

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.

Answer 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

2. 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

3. 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

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

5. 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.

6. 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