40

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?

JD Isaacks
  • 56,088
  • 93
  • 276
  • 422

4 Answers4

59

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.

BoltClock
  • 700,868
  • 160
  • 1,392
  • 1,356
  • 2
    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. – fncomp Dec 09 '10 at 18:30
  • 1
    @JoshN: Not too sure about that, but I found [this question](http://stackoverflow.com/questions/488811/tinyint-vs-bit) which may help. – BoltClock Dec 09 '10 at 18:31
  • 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? – JD Isaacks Dec 09 '10 at 19:16
  • 1
    @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. – BoltClock Dec 09 '10 at 19:29
  • thanks for the link. I guess it depends on whether you will ever need to switch to a third non-NULL value. – fncomp Dec 09 '10 at 20:01
  • I still do not get it - why to use tinyint(4) if tinyint(1) holds the same. – Dejell Dec 09 '12 at 09:32
  • @Odelya: (4) lets you display values of up to 4 chars, (1) only single-digit numbers. – BoltClock Dec 09 '12 at 09:34
  • 3
    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 – Dejell Dec 09 '12 at 09:37
  • 1
    @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. – Nikunj Madhogaria Aug 20 '15 at 11:53
  • @Dejell Display is the "length" and specifies how many characters to display but only takes effect in the mysql command line client. – SandroMarques Jun 05 '17 at 14:43
9

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).

Nathan
  • 1,700
  • 12
  • 15
6

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.

mdm
  • 5,528
  • 5
  • 29
  • 28
  • 3
    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? – JD Isaacks Dec 09 '10 at 18:41
  • 1
    requesting to explain this too. – MaXi32 Sep 11 '15 at 12:56
1
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.

Rabeel Javed
  • 21
  • 1
  • 7