MySQL Boolean "tinyint(1)" holds values up to 127?
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 d开发者_StackOverflowefault 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?
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.
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).
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.
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.
精彩评论