Why the length of tinyint seems to make no difference?

Question

I've been searching for what the length in the tinyint declaration means (e.g. tinyint(5)). And the answers seem to be that it should just be a rule for how MySQL displays the data. But in my case there seems to be no change whichever length i use, or i just heavily misunderstood what they meant.

For example, setting the length to be shorter than the length of the number i store, i thought maybe it would crop it, but it didn't:

MariaDB [test]> describe test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| hello | tinyint(1) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.002 sec)

MariaDB [test]> select * from test;
+-------+
| hello |
+-------+
|    20 |
+-------+
1 row in set (0.000 sec)

Then i changed the length to be longer than the length of the number, expected it to be padded to left with zeroes, but again it wasn't:

MariaDB [test]> describe test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| hello | tinyint(3) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.002 sec)

MariaDB [test]> select * from test;
+-------+
| hello |
+-------+
|    20 |
+-------+
1 row in set (0.001 sec)

The version i am using is: 5.5.5-10.3.25-MariaDB-0ubuntu.

How can i see where this length declaration makes any difference?

Answer 1

In SQL, INTEGER types are not defined by the number of digits. Instead the number of bytes available to represent the value are declared. E. g. if you define a TINYINT(1) you'll get one byte to store the number.

With signed integers this will give you a range of -128 to 127 while you would get a range of 0 to 255 with unsigned integers:

create table test (signed_tiny tinyint(1), unsigned_tiny tinyint(1) unsigned);
insert into test (signed_tiny, unsigned_tiny) values (-128, 0), (127, 255);
select * from test;
+-------------+---------------+
| signed_tiny | unsigned_tiny |
+-------------+---------------+
|        -128 |             0 |
|         127 |           255 |
+-------------+---------------+
2 rows in set (0.00 sec)

When you try to insert values out of these ranges, you will get an error message:

insert into test (signed_tiny, unsigned_tiny) values (128, 256);
ERROR 1264 (22003): Out of range value for column 'signed_tiny' at row 1

It doesn't make any difference if you declare a higher number within the paranthesis of TINYINT because it is statically defined to be one byte, so will still get the same error e. g.

alter table test modify signed_tiny tinyint(2);
insert into test (signed_tiny) values (128);
ERROR 1264 (22003): Out of range value for column 'signed_tiny' at row 1

To avoid this error, you could either use SMALLINT which is defined to use two bytes for the value represenatation or use the standard SQL INT datatype:

alter table test add signed_smallint smallint;
insert into test (signed_smallint) values (128);
Query OK, 1 row affected (0.02 sec)

According to the MariaDB TINYINT manual you can add a number in paranthesis but it will make no difference. So TINYINT in MySQL and MariaDB is an alias for the standard SQL INT(1) and SMALLINT another alias for INT(2).

I hope this makes it a bit clearer. Anyway I would recommend to have a look at the MariaDB documentation.

Answer 2

The (5) is useless info except when coupled with ZEROFILL. It is harmless.

MySQL 8.0.19 has removed both. MariaDB may follow suit in a few years.

FLOAT(m,n) does mean something, but, in my opinion, it should never be used. It is arguably harmful. (8.0.17 deprecates it.)

Meanwhile, it is time to move past version 5.5.