SQL question: difference between MEDIUMINT(6) and MEDIUMINT(8)?

[stndn]

I'm designing a new database table for my project, and one of the columns (basically, the primary auto_increment index field) is going to be of type mediumint (n).

When adding the new column, MySQLFront asked for the length (n) of the data. Sometimes it will auto-fill the value with 6, other time it will auto-fill with the value 8. For INT() data type, the auto-fill value is 11.

A few of questions regarding this:


1. What does the length value means in this context?

2. In MySQL documentation, the (n) value is specified as 'number of bits per value'. Does this mean the amount of space it will take to store each number, or the maximum / largest number I can store in the column?

3. Is there any difference in the number range I can specify if I create MEDIUMINT(6) vs MEDIUMINT(8)?

4. Should I specify lower or higher number in the length?


Thank you in advance.


-stndn.

 

[kamper]

2. In MySQL documentation, the (n) value is specified as 'number of bits per value'. Does this mean the amount of space it will take to store each number, or the maximum / largest number I can store in the column?

8 bits = max number of 255 (unsigned)
6 bits = max number of 63 (unsigned)
Your number will probably take all 8 (or 6) bits no matter how big it is.

I'd suggest picking something you know is going to be big enough for the largest number you're going to put in there

 

[stndn]

Hmmm...
after reading some more, I found this information from the MySQL Reference Manual, Section 11.2:

Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional display width specification is used to left-pad the display of values having a width less than the width specified for the column.

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.

So I guess I've found the answers to my own questions -)


-stndn.

 

[stndn]

Originally posted by: kamper

2. In MySQL documentation, the (n) value is specified as 'number of bits per value'. Does this mean the amount of space it will take to store each number, or the maximum / largest number I can store in the column?

8 bits = max number of 255 (unsigned)
6 bits = max number of 63 (unsigned)
Your number will probably take all 8 (or 6) bits no matter how big it is.

I'd suggest picking something you know is going to be big enough for the largest number you're going to put in there

oopss...didn't see your post before answering...

Well, I guess I should clear it up that when I read that the value is specified as number of bits per value, it turned out tha I was looking at the description for BIT type instead of MEDIUMINT type.

BIT[(M)]

A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted

But yah, I guess it's always better to go with the largest possible number now instead of scratching my head later on when a number cannot be added to the database.

thanks -)


-stndn.

 

[kamper]

It would probably be better to find out now what happens when you add a number that is too big. It might just roll over silently (like if you're using an auto_increment or something) which could be a very bad thing.