8

Im new to Database programming and I have a very basic question:

In my PHPMyAdmin GUI that Im using to create tables in my database, what does it mean when the column "type" (ie. datatype) has the data type and something in brackets after that. For example: int(20), bigint(30) .....

I understand the type int and bigint imply the number of bytes that are used and consequently the range of values that can be stored. But what does the value in the brackets mean?

What does the (20) and the (30) stand for.... what impact does this have on....

Sorry if the Q is basic, I am trying to understand databases....

Thanks a lot

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
banditKing
  • 9,405
  • 28
  • 100
  • 157

3 Answers3

12

Basically this is a Display Width.

I've found very good explanation of this concept here is so decided to not describe it myself and let you read it yourself from the original source.

In the same way that a max-length can be specified for string data types (e.g. VARCHAR(5) = Maximum 5 Characters), Numeric data type cells can have a "Display Length" specified ( E.g.: INT(5) ).

There is a common misconception that specifying a Display Length on an INT column will limit that column's range. As example, it is quite often thought that defining a column as INT(1) will reduce the column's unsigned range to 0 - 9, and that INT(2) would reduce the column's unsigned range to 0 - 99. This is not the case. An INT data column will ALWAYS have a viable unsigned range of 0 - 4294967295, or a signed range of -2147483648 to 2147483647, irrespective of the specified Display Width, whether it be 1 ( INT(1) ) or 20 ( INT(20) ).

  1. Display width doesn't change storage requirements for a data type.
  2. Display width doesn't alter the actual data in any way (ie: it stores the entire value for the data)
  3. A column returns it's full value when called in a query, regardless of the display width (the book directly contradicts this claim it makes as seen above)
sll
  • 61,540
  • 22
  • 104
  • 156
  • Thanks for your detailed answer sll. Appreciate it. – banditKing Jul 30 '11 at 20:38
  • @user657514 `INT(x)`/`BIGINT(x)` is *different* compared to `VARCHAR(x)`/`CHAR(x)` or `NUMERIC(x,y)`. In the former place it is mere a "display width". The latter cases it actually imposes a limit. This is addressed throughout the response, but it is confusing to "see". –  Jul 30 '11 at 20:59
  • @sll I find that link ... not so good. According to the article `VARCHAR(55)` can't store a string of zero character in length! My belief of accuracy in the source is diminished. :( –  Jul 30 '11 at 21:03
  • Here a good explanation of int, display width and size: http://matthom.com/archive/2006/11/28/mysql-integer-columns-and-display-width – Kaken Bok Jul 30 '11 at 21:22
0

Link to the mysql doc which explains it http://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html

Pratik Khadloya
  • 12,509
  • 11
  • 81
  • 106
0

The value in the bracket is the size or length of the field. [Edit strike]If set to 2 a uint field can only host values from 0 to 99.[/strike] You can set this value on your own and thus save a bit of memory if you expect your values not to exceed this limitation. Useful in connection with varchar.

Here another thread about varchar sizes: What are the optimum varchar sizes for MySQL?

Community
  • 1
  • 1
Kaken Bok
  • 3,395
  • 1
  • 19
  • 21
  • Are you sure that int(2) will limit value range which can host int column to 0-99 – sll Jul 30 '11 at 20:30
  • hmm. so if I have an INT as my datatype, then the range of values it supports is: -2147483648 to 2147483647. But if I anticipate that I wont use this whole range I can set a value in the brackets like this: INT(4) and doing that will mean that the Upper range is reduced to 3647 ? Could you give an actual example please to clarify. Thanks – banditKing Jul 30 '11 at 20:31
  • 1
    No i am sorry. This is only valid for chars. See the answer of sll! – Kaken Bok Jul 30 '11 at 20:32