What is the MAX number if I store int(255) in MySQL?

Question

I use int(255) in mysql as my id. Is this long enough? If I got about 1,000,000 records....Thank you.

score 55 · Accepted Answer · edited Nov 25 '18 at 16:56

55

Something is probably just converting that to int(11) for you. Since you can't have 255 visible digits in an int, the maximum value will be 2147483647.

If you need more than that you can set it to be unsigned, since I'm assuming you have no negative ids and then you can have up to 4294967295.

If you are ever going to have more than 4 billion records (very unlikely if you're at 1 million right now), then you could use a bigint instead, which allows you to store numbers up to 18446744073709551615 at a cost of more storage space of course.

edited Nov 25 '18 at 16:56

Donald Duck

8,409
22
75
99

answered Aug 24 '11 at 06:34

Paul

139,544
27
275
264

it means, I type 255 is useless? – DNB5brims Aug 24 '11 at 06:42
4

Yes, no integer will be displayed as 255 characters. The longest is (11) which is enough for a `-` symbol and 10 digits. If you are using unisigned int then you might as well set it to int(10) – Paul Aug 24 '11 at 06:44

gbn · Answer 2 · 2011-08-24T06:43:59.440

8

-2147483648 to 2147483647 as per the docs for a signed 32 bit integer value

The 255 is simply display width and doesn't affect the range of values

edited Aug 24 '11 at 06:43

answered Aug 24 '11 at 06:37

gbn

422,506
82
585
676

score 6 · Answer 3 · edited Sep 05 '13 at 19:22

6

The INT in mysql use 4 byte storage, and range from -2147483648 to 2147483647. If you use unsigned int, the range is 0 to 4294967295.

edited Sep 05 '13 at 19:22

M Khalid Junaid

63,861
10
90
118

answered Aug 24 '11 at 06:39

xdazz

158,678
38
247
274

score 5 · Answer 4 · answered May 26 '16 at 14:44

See this blog.

SELECT ~0 as max_bigint_unsigned
,      ~0 >> 32 AS max_int_unsigned
,      ~0 >> 40 AS max_mediumint_unsigned
,      ~0 >> 48 AS max_smallint_unsigned
,      ~0 >> 56 AS max_tinyint_unsigned
,      ~0 >> 1  AS max_bigint_signed
,      ~0 >> 33 AS max_int_signed
,      ~0 >> 41 AS max_mediumint_signed
,      ~0 >> 49 AS max_smallint_signed
,      ~0 >> 57 AS max_tinyint_signed
\G

*************************** 1. row ***************************
   max_bigint_unsigned: 18446744073709551615
      max_int_unsigned: 4294967295
max_mediumint_unsigned: 16777215
 max_smallint_unsigned: 65535
  max_tinyint_unsigned: 255
     max_bigint_signed: 9223372036854775807
        max_int_signed: 2147483647
  max_mediumint_signed: 8388607
   max_smallint_signed: 32767
    max_tinyint_signed: 127
1 row in set (0.00 sec)

score 5 · Answer 5 · answered Aug 24 '11 at 06:33

5

If unisgned, from 0 to 4 294 967 295, so that is more than eough.

More info in mysql docs.

answered Aug 24 '11 at 06:33

Deele

3,728
2
33
51

@Deele: the docs (*the ones you linked to*) state that INTEGER is signed by default and that UNSIGNED is optional (and non-standard) – gbn Aug 24 '11 at 07:02
3

@gbn, did you find it hard to read first two words in my answer? – Deele Aug 26 '11 at 07:21
Not at all. I find it hard to believe that you mentioned "unsigned" when 1. it isn't the default 2. OP didn't mention it. So *what value does your answer add*? None. It's based on at best an assumption: Occam's razor tells you it's default signed. – gbn Aug 26 '11 at 07:23
5

@gbn, The question was not about any standarts, but was about specific situation. If guy does not know data types and is mentioning INT and that he wants to use it for ID field, that means, guy does not know SQL and I gave newbie advice to use unsigned value for ID's and mentioned how many fields it can hold... The value of answer is in advice, I gave... C'mon, this is turing into nonsense.... – Deele Aug 27 '11 at 13:17

What is the MAX number if I store int(255) in MySQL?

5 Answers5

Linked