MySQL integer columns and display width Nov28 '06

# (1 of 8): Peekay

4 days, 15 hours after the fact. (Sun 03 Dec 2006, 6:25 AM CST)

If the unsigned range of TINYINT is -128 to 127, should the display width be TINYINT(3) or TINYINT(4)?

I understand that the unsigned range would be 0 - 255 so three digits is the max. I'm trying to find out if the minus sign needs to be accounted for.

# (2 of 8): Matthom

4 days, 16 hours after the fact. (Sun 03 Dec 2006, 7:19 AM CST)

Peekay, it should be TINYINT(4), for the signed range. The negative sign does count as part of the width, as far as I know...

For example, if you don't include a display width, when creating the field, MySQL will put a default display width, and it will be 4 for signed TINYINT. (and 3 for unsigned TINYINT.)

That's usually a good way to test it - just let MySQL put the default value in there.

Hope that helps...

Previous comment Return to entry

# (3 of 8): Peekay

6 days, 14 hours after the fact. (Tue 05 Dec 2006, 5:06 AM CST)

Many thx Matthom. :-) I couldn't find any mention of this on the official MySQL website. Thanks also for the tip about letting MySQL set its own default width. I'm using PHPMyAdmin to build some tables and it throws an error if you leave the width empty for character fields. I didn't realise you could leave this empty for numeric fields.

Previous comment Return to entry

# (4 of 8): Jason

3 weeks, 1 day after the fact. (Wed 20 Dec 2006, 11:34 PM CST)

Thanks - had a db field which was about to increment to 100,000 (probably overnight) and had just noticed the fields were MEDIUMINT(5). Was concerned that the values would be chopped off. Looks like I won't need to worry about... I have about 3 centuries before it will increment to 16.7 million, I think I can make the necessary modifications by then. Thanks Matthom!

Previous comment Return to entry

# (5 of 8): Lakshitha

1 year, 9 months after the fact. (Thu 28 Aug 2008, 4:24 AM CST)

hi... this is a very straightforward answer.. i was thinking about this matter, now it has cleared.. thanks.. a good job.. :)

Previous comment Return to entry

# (6 of 8): Vincent Voyer » vincent-voyer.fr

1 year, 10 months after the fact. (Wed 01 Oct 2008, 5:39 AM CST)

Thank you so much i have been wrong for 5 years since now !

:):)

Previous comment Return to entry

# (7 of 8): Shawn

1 year, 10 months after the fact. (Fri 17 Oct 2008, 2:25 PM CST)

Thanks a lot! This helped me out a great deal (The ZeroFill tip)

Previous comment Return to entry

# (8 of 8): Marcelo Oliveira » iboletim.com.br

1 year, 11 months after the fact. (Wed 05 Nov 2008, 1:35 PM CST)

thx. wonderfully explained!

Previous comment Return to entry