TINYINT(1) work as a boolean? The way I understood the official docs, the (1) should mean it has a display width of 1, so if I store
56 in it, I thought it should print
5. But for some reason it always prints either
And another case is if I store
56 in an
INT(1), then it prints
56 (at least according to SQLFiddle). What’s going on here?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
The (1) in parentheses for a MySQL integer type has nothing to do with the range of values accepted by the data type, or how it is stored. It’s only for display.
See also my answer to Types in MySQL: BigInt(20) vs Int(20) etc.
TINYINT is no different from TINYINT(1) or TINYINT(2) or TINYINT(64). It’s an 8-bit signed integer data type, and it accepts any 8-bit integer value from -128 to 127.
mysql> create table b (i tinyint(1)); mysql> insert into b values (42); mysql> select * from b; +------+ | i | +------+ | 42 | +------+
For convenience, MySQL supports an alias for BOOL, which is replaced immediately by TINYINT(1).
mysql> create table b2 (i bool); mysql> show create table b2; CREATE TABLE `b2` ( `i` tinyint(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
As I said, the use of (1) means almost nothing, it’s only a convention so that if you see TINYINT(1) it’s reasonable to assume the column is intended to be used as a boolean. But nothing in MySQL prevents you from storing other integer values in it.
If you want a column to accept only 0 or 1, you can use BIT(1):
mysql> create table b3 (i bit(1)); mysql> insert into b3 values (0), (1); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into b3 values (-1); ERROR 1406 (22001): Data too long for column 'i' at row 1 mysql> insert into b3 values (2); ERROR 1406 (22001): Data too long for column 'i' at row 1
This doesn’t save any space compared to TINYINT though, because the storage for a given column rounds up to the nearest byte.
PS: Despite answer from @samdy1, TINYINT does not store strings
'1' at all, it stores integers
1, as well as other integers from -128 to 127. There is no need to quote integers in SQL, and I am often puzzled why so many developers do.
TINYINT columns can store numbers from
TINYINT(1) is a bit weird though. It is (perhaps because it is supposed to act as a
BOOLEAN datatype), returns only
1 in some context, while it still keeps the stored (-128 to 127) values.
(Correction: I only see this weird behaviour in SQL-Fiddle and not when accessing MySQL locally so it may well be a SQL-Fiddle quirkiness, possibly related to the quivalence with
BOOLEAN) and not a MySQL problem.
See the SQL-Fiddle
CREATE TABLE test ( i TINYINT(1) ) ; INSERT INTO test (i) VALUES (0), (1), (6), (120), (-1) ;
Where we get (only in SQL-Fiddle, not if we access MySQL otherwise!):
SELECT i FROM test ; i ----- 0 1 1 1 1
SELECT CAST(i AS SIGNED) i2 FROM test ; i2 ----- 0 1 6 120 -1
This is a mysql Jdbc configuration subject.
You can config mysql jdbc to convert TinyInt(1) to Boolean or Integer through set jdbc url config property “tinyInt1isBit” to “true” (default) or “false”.
Should the driver treat the datatype TINYINT(1) as the BIT type (because the server silently converts BIT -> TINYINT(1) when creating tables)?
The engine is smart enough to know that
BOOL are the same. However
INT(1) only affects the Display Width instead of the underlying storage size. Display width only comes into play when the value is less width then the display width. Then it gets padded.
As I understand it,
TINYINT(1) can only hold
'1' (from own experience).
Thus, one can assume that the
'1' is translated into