MySQL Boolean "tinyint(1)" holds values up to 127?

Question

I wanted to make a true/false field for if an item is in stock.

I wanted to set it to Boolean ( which gets converted to tinyint(1) ), 1 for in stock, 0 for not in stock.

I am getting feeds from vendors, so I thought to myself, "What if they pass how many are instock?"

So I wondered if I inserted a number higher than 1 what would happen. I assumed it would default to 1.

To my surprise it will allow me to hold any number up to 127, anything over defaults to 127.

Can anyone explain why?

Scholar

Answer 1

The signed TINYINT data type can store integer values between -128 and 127.

However, TINYINT(1) does not change the minimum or maximum value it can store. It just says to display only one digit when values of that type are printed as output.

Fellow

Is there any reason not to use bit(1) instead? Though I do notice that it defaults to 0 if I attempt to set it to >1. – Walling

@JoshN: Not too sure about that, but I found this question which may help. – Fellow

So does this mean when I try to select it in my application (php) I will just get the first digit, so 127 will be 1 and 97 will be 9? – Scholar

@John: No, the values are still displayed as is, but MySQL will also send the display length if the application needs it. In PHP, you get the display length using mysql_field_len(), then call substr() or whatever you need to display values appropriately, in case the values overflow the display length. – Fellow

thanks for the link. I guess it depends on whether you will ever need to switch to a third non-NULL value. – Walling

I still do not get it - why to use tinyint(4) if tinyint(1) holds the same. – Madeup

@Odelya: (4) lets you display values of up to 4 chars, (1) only single-digit numbers. – Fellow

What do you mean by display? When I do select from db, and the tinyint(1) value is 100, it still displays 100 for instance – Madeup

@Dejel Like BoltClock mentioned, the length value in parentheses acts like metadata. It allows the application (in case it needs) to know the expected length of the column field. – Heliacal

@Madeup Display is the "length" and specifies how many characters to display but only takes effect in the mysql command line client. – Illyricum

Answer 2

The tinyint data type utilizes 1 byte of storage. 256 possible integer values can be stored using 1 byte (-128 through 127). if you define as tinyint unsigned then negative values are discarded so is possible to store (0 through 255).

Sanctity

Answer 3

See here for how MySQL handles this. If you use MySQL > 5.0.5 you can use BIT as data type (in older versions BIT will be interpreted as TINYINT(1). However, the (1)-part is just the display width, not the internal length.

Rigadoon

Can you please explain the difference between display width and internal length. I am new to this concept, I thought what was in the data-base was what was selected? – Scholar

requesting to explain this too. – Least

Answer 4

CREATE TABLE foo_test(
col_1 TINYINT
, col_2 TINYINT(2) 
, col_3 TINYINT(3) 
, col_4 TINYINT(2) ZEROFILL
, col_5 TINYINT(3) ZEROFILL
);

INSERT INTO foo_test( col_1,col_2,col_3,col_4,col_5 )
SELECT 1, 1,1,1,1
UNION ALL
SELECT 10, 10,10,10,10
UNION ALL
SELECT 100, 100,100,100,100;

SELECT * FROM foo_test; 

**OUTPUT:-**   
 col_1   col_2   col_3   col_4   col_5  
------  ------  ------  ------  --------
     1       1       1      01       001
    10      10      10      10       010
   100     100     100     100       100

MySQL will show the 0's in the start if zerofill is used while creating the table. If you didn't use the zerofill then it is not effective.

Thelmathem