Understanding Length/Values a little more

[9three]

Hey,

 

I have a question about the Length/Value option that is available through MySQL.

 

If I use `ID` and set the type to INT and 255 length/value, does that mean it will go up to 255 digits like so:

 

1,2,3...134...212...255

 

With different inputs in each and then reset back to 1 or whatever is available? Or what happens when it finally reaches 255 length?

[corbin]

The length is usually only useful when zerofill is involved.

 

 

The length of fields is limited by... the field length.

 

 

Int fields are 32 bits, so the max length is 2^31-1 if it's signed, or 2^32-1 if it's unsigned.

 

[9three]

So an INT  can hold about 2147483648(give or take a few) inputs before it runs out?

 

The length of fields is limited by... the field length.

 

The field length of an HTML page? Or do you mean the the length within SQL. So if I do varchar(255), and I input into it a 255 characters it will stop there and not allow any more?

[corbin]

Oh yeah, on var fields, the var means variable, and the length is the maximum length.

 

 

I was thinking length of numeric fields.

 

So an INT  can hold about 2147483648(give or take a few) inputs before it runs out?

 

 

The highest value an integer field (signed) can hold is 2^31-1.

[9three]

If I set `ID` to INT(50) and it reaches 50. Does it try to find an open space between 0-49? Or does it through an error?

[Zane]

[tex]2 ^{32-1} = 4294967295 = 11111111111111111111111111111111[/tex] in binary

That's 32 ones just to keep you from counting them

That is what is known as a 32 bit unsigned integer.

 

[tex]2 ^{31-1} = 2147483647 = 01111111111111111111111111111111[/tex] in binary

That's 31 ones just to keep you from counting them

That is what is known as a 32 bit signed integer. *note the preceding zero

 

The signed part of it means....that the zero is used to determine if the number is negative or positive.....I'm not too sure about which is positive or negative...0 or 1, but that's the idea of it.  I maybe completely wrong about this part though...don't take my word on it......but I know there is a dedicated bit somewhere

 

 

VARCHAR is different...if you give it a value of 255 characters....there is no binary aspect to that.....that straight up means.....255 characters.

 

EDIT: More reading if you need it

http://en.wikipedia.org/wiki/Integer_(computer_science)

[corbin]

0 is positive ;p.

[9three]

Thanks for the wiki. In addition to what I already said, I was also trying to ask if the ID(INT) is incremented automatically it will do so until whatever value I set it to? Meaning if ID is 50 then it will do:

 

1

2

3

..

50

 

then reset? or just not allow anything? or throw an error?

[Zane]

you can't ...."set" a custom length on an INT...at least I don't think you can..but either way

if you have a 8 bit ID (int)...that is autoincrementing.....it will...yes...throw an error after a while.. ONLY IF THE FIELD IS A PRIMARY KEY THOUGH

 

There error will be a duplicate entry error. So when you get to ID 128...or what is supposed to be 128....you can't do it, because 127 is the max on an 8-bit INT ... unsigned.

 

 

In short...there is no reason to set a length for Integer datatypes

[9three]

Thank you.

 

Do any of you guys know a site where I can view the different types of options available and which one suites it best? INT vs TinyINT, VARCHAR vs CHAR, etc etc,

[Zane]

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

 

Straight from the MySQL manual

and which one suites it best? INT vs TinyINT, VARCHAR vs CHAR, etc etc,

It's pretty straight forward....if you think you're gonna need numbers up in the billions then use a BIGINT

IF you think you won't need numbers past 127 then stick with tinyint.  It's all about what you need...not what's the rule.

[9three]

Thanks. Last question:

 

I noticed if you dont put a length/value to INT, it automatically it set to 11. So it looks like this if I dont set anything INT(11).

 

So in essence, it's setting something, when you said I shouldn't.

[Zane]

the 11 stands for the max number of digits....but either way....if you have an 11 digit length and you enter 99999999999 ... it will not work

 

you may get an out of range error...and you will ONLY get the maximum value for that numeric type for your value.....in this case 2147483647

[9three]

thank you for everything

[Zane]

remember to mark as solved

[fenway]

INT UNSIGNED should suffice for most tables... are you really going to store over 4 billion records in any given table?