Why does TINYINT(1) function as a boolean but INT(1) does not?

Question

Why does 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 1 or 0.

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?

Solution 1

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 '0' or '1' at all, it stores integers 0 or 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.

Solution 2

TINYINT columns can store numbers from -128 to 127.

TINYINT(1) is a bit weird though. It is (perhaps because it is supposed to act as a BOOLEAN datatype), returns only 0 and 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

but:

SELECT CAST(i AS SIGNED) i2
FROM test ;

  i2
-----
   0
   1
   6
 120
  -1

Solution 3

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

from: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

Should the driver treat the datatype TINYINT(1) as the BIT type (because the server silently converts BIT -> TINYINT(1) when creating tables)?

Default: true

Solution 4

The engine is smart enough to know that TINYINT(1) and 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.

http://alexander.kirk.at/2007/08/24/what-does-size-in-intsize-of-mysql-mean/

View more solutions