# What is the meaning of tinyint(N)?

When we use an argument length with numerical data types, as far as I know this specifies the display width.
I tried the following:

``````mysql> create table boolean_test (var1 boolean, var2 tinyint);
Query OK, 0 rows affected (0.10 sec)

mysql> show create table boolean_test;
+--------------+-------------------------
| Table        | Create Table
+--------------+-------------------------
| boolean_test | CREATE TABLE `boolean_test` (
`var1` tinyint(1) DEFAULT NULL,
`var2` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+---------------------------
1 row in set (0.00 sec)
``````

First of all tinyint is a 1 byte value. So what is the meaning of `tinyint(4)`? There can not be 4 digits.

``````mysql> insert into boolean_test values(101,112);
Query OK, 1 row affected (0.03 sec)

mysql> select * from boolean_test;
+------+------+
| var1 | var2 |
+------+------+
|   10 |  112 |
|  101 |  112 |
+------+------+
2 rows in set (0.00 sec)
``````

I see that in tinyint I stored a 10 and 101 and I could get back these values despite that it is defined as tinyint(1).
Shouldn't I see a 1 for `var1`? I.e. just 1 display digits?

-

Data-wise, `tinyint(1)`, `tinyint(2)`, `tinyint(3)` etc. are all exactly the same. They are all in the range -128 to 127 for `SIGNED` or 0-255 for `UNSIGNED`. As other answers noted the number in parenthesis is merely a display width hint.

You might want to note, though, that application=wise things may look different. Here, `tinyint(1)` can take a special meaning. For example, the Connector/J (Java connector) treats `tinyint(1)` as a boolean value, and instead of returning a numerical result to the application, it converts values to `true` and `false`. this can be changed via the `tinyInt1isBit=false` connection parameter.

-
This answer is vital for developers. +1 !!! – RolandoMySQLDBA Dec 15 '13 at 18:04

A tinyint(1) can hold numbers in the range -128 to 127, due to the datatype being 8 bits (1 byte) - obviously an unsigned tinyint can hold values 0-255.

It will silently truncate out of range values:

``````mysql> create table a
-> (
->    ttt tinyint(1)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a values ( 127 );
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values ( -128 );
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values ( 128 );
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into a values ( -129 );
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from a;
+------+
| ttt  |
+------+
|  127 |
| -128 |
|  127 |
| -128 |
+------+
4 rows in set (0.00 sec)

mysql>
``````

... unless you change the `sql_mode` or change the server config:

``````mysql> set sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values ( -129 );
ERROR 1264 (22003): Out of range value for column 'ttt' at row 1
mysql>
``````

The value used in the DDL for the datatype (eg: tinyint(1)) is, as you suspected, the display width. However, it is optional and clients don't have to use it. The standard MySQL client doesn't use it, for example.

-
This answer is more thorough. +1 !!! – RolandoMySQLDBA Dec 15 '13 at 17:34