CODEWITHSUNDEEP
mysqlsqldatatypes
realtebo
realtebo

Reputation: 25637

MySql: Tinyint (2) vs tinyint(1) - what is the difference?

I knew boolean in mysql as tinyint (1).

Today I see a table with defined an integer like tinyint(2), and also others like int(4), int(6) ...

What does the size means in field of type integer and tinyint ?

Upvotes: 276

Views: 354259

Answers (4)

AamirR
AamirR

Reputation: 12198

It means display width

Whether you use tinyint(1) or tinyint(2), it does not make any difference.

I always use tinyint(1) and int(11), I used several mysql clients (navicat, sequel pro).

It does not mean anything AT ALL! I ran a test, all above clients or even the command-line client seems to ignore this.

But, display width is most important if you are using ZEROFILL option, for example your table has following 2 columns:

A tinyint(2) zerofill

B tinyint(4) zerofill

both columns has the value of 1, output for column A would be 01 and 0001 for B, as seen in screenshot below :)

zerofill with displaywidth

Upvotes: 172

Ja͢ck
Ja͢ck

Reputation: 173522

The (m) indicates the column display width; applications such as the MySQL client make use of this when showing the query results.

For example:

| v   | a   |  b  |   c |
+-----+-----+-----+-----+
| 1   | 1   |  1  |   1 |
| 10  | 10  | 10  |  10 |
| 100 | 100 | 100 | 100 |

Here a, b and c are using TINYINT(1), TINYINT(2) and TINYINT(3) respectively. As you can see, it pads the values on the left side using the display width.

It's important to note that it does not affect the accepted range of values for that particular type, i.e. TINYINT(1) still accepts [-128 .. 127].

Upvotes: 292

zloctb
zloctb

Reputation: 11177

mysql> CREATE TABLE tin3(id int PRIMARY KEY,val TINYINT(10) ZEROFILL);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO tin3 VALUES(1,12),(2,7),(4,101);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tin3;
+----+------------+
| id | val        |
+----+------------+
|  1 | 0000000012 |
|  2 | 0000000007 |
|  4 | 0000000101 |
+----+------------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT LENGTH(val) FROM tin3 WHERE id=2;
+-------------+
| LENGTH(val) |
+-------------+
|          10 |
+-------------+
1 row in set (0.01 sec)


mysql> SELECT val+1 FROM tin3 WHERE id=2;
+-------+
| val+1 |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)

Upvotes: 20

Devart
Devart

Reputation: 121902

About the INT, TINYINT... These are different data types, INT is 4-byte number, TINYINT is 1-byte number. More information here - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT.

The syntax of TINYINT data type is TINYINT(M), where M indicates the maximum display width (used only if your MySQL client supports it).

Numeric Type Attributes.

Upvotes: 19

Related Questions