int(3) returns 5 digit number on select 2

[Ghodmode]

If I have a field with a data type of int with a size of 3, why would I get a 5-digit number when I query it? ...

mysql> desc prod_cat;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| rel_id      | int(3)       | NO   | PRI | NULL    | auto_increment | 
| prod_id     | varchar(255) | NO   | MUL | NULL    |                | 
| category    | varchar(255) | NO   |     | NULL    |                | 
| subcategory | varchar(255) | NO   |     | NULL    |                | 
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> select * from prod_cat where prod_id = 'DATAPROZ';
+--------+----------+--------------------+-------------+
| rel_id | prod_id  | category           | subcategory |
+--------+----------+--------------------+-------------+
|  20955 | DATAPROZ | Money & Employment | Jobs        | 
+--------+----------+--------------------+-------------+
1 row in set (0.01 sec)

Thank you.

--
-- Ghodmode

Give a man a fish and he'll come back to buy more... Teach a man to fish and you're out of business.

 

[johnwm]

You may have misunderstood what INT(3) actually does.

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.

See

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

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[audiopro]

You are getting 5 digits because that is what the field contains.
Quote from MYSQL docs - I didn't know either.

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.

Keith

 

[audiopro]

Simultaneous posting.

Keith

 

[r937]

INT(3) is the same as INT(11) is the same as INT(937)

if you want only 3 digits, you might try TINYINT, which, if declared UNSIGNED, allows values from 1 to 255

r937.com | rudy.ca

 

[guelphdad]

the (3) after the int would only show the number of digits shown in a zerofill column. So if you have numbers 1 to 1000 in your column then all would show but 1 would show as 001 since that would give you three digits. the (3) does not limit your column to under 1000. INT holds values up to a specific size and is not constrained by the number in brackets after it.