54

In my database I have declared a variable of datatype Int(10). If I type a number in a textbox in my web page, that number is stored in a variable whose largest value can be Int(10) in Mysql. If I type a very large number in the textbox it is giving IndexOutofRangeException.

So I am planning to use the maximumlength property of text box. What is the maximum number that can be stored in a variable of type Int(10) in mysql?

theazureshadow
  • 9,499
  • 5
  • 33
  • 48
Manoj Nayak
  • 2,449
  • 9
  • 31
  • 53
  • 1
    possible duplicate of [Whats the size of an SQL Int(N)?](http://stackoverflow.com/questions/4151259/whats-the-size-of-an-sql-intn) – ChrisWue Jun 04 '12 at 09:53

3 Answers3

104

INT(10) means you probably defined it as INT UNSIGNED.

So, you can store numbers from 0 up to 4294967295 (note that the maximum value has 10 digits, so MySQL automatically added the (10) in the column definition which (10) is just a format hint and nothing more. It has no effect on how big number you can store).

You can use BIGINT UNSIGNED if you want to store bigger values. See the MySQL docs: Integer Types (Exact Value)

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • i have checked it that integer will be 4 bytes and allow its max size whatever the length you set means `int(10)`, but if you set `char(4)` it will not allow more than 4 characters, i am thinking why it behaves differently with different datatypes? – Haritsinh Gohil Apr 24 '19 at 07:36
  • 1
    `INT` is 4 bytes always. It make no difference if you define it as `int(1)`, `int(4)` or `INT(10)`, it allows 4 byte integers. `CHAR(x)` and `VARCHAR(x)` are different, yes. The `x` means max number of characters there. – ypercubeᵀᴹ Apr 24 '19 at 11:13
  • 3
    (10) does not refer to the maximum numbers of characters an int can contain. It does not affect the number of digits or the maximum size of the int at all. You can even configure a column to be a bigint(5) if you want. The (10) is the "display width". It is pure metadata and normally it's not used at all. Tools can read the value and use it for example to set column widths when formatting output. MySQL uses it only when zerofill is enabled on a column, then it will prefix a number with zeroes until the display width is reached. – Gerben Aug 30 '19 at 15:24
  • @Gerben exactly.(10) is the display width and nothing more. It is used by some tools, e.g. the mysql cli, in order to display integer values nicely. – ypercubeᵀᴹ Aug 30 '19 at 18:07
19

An unsigned int has the max value of 4294967295 no matter if its INT(1) or int(10) and will use 4 bytes of data.

as stated here.

Andrius Naruševičius
  • 8,348
  • 7
  • 49
  • 78
  • i wonder then why we have to set length if it will always use 4 bytes and can store max length values in it? – Haritsinh Gohil Apr 24 '19 at 06:49
  • @HaritsinhGohil you don't have to (set it) and it's not size. It's just a format length hint, mostly ignored. – ypercubeᵀᴹ Apr 24 '19 at 11:16
  • yeah after reading on given link and other question- answer, now i knew that length is for display width which will only be applicable when we are using `zerofill` on `int`. – Haritsinh Gohil Apr 24 '19 at 12:00
2

Just use BigInt Instead of Int in Mysql and if you want explanation in brief then goto

MySQL DataTypes

Divyansh Chaudhary
  • 21
  • 3