What is the utility of tinyint(M) in MySQL?

Question

This is more of an academic question. In a MySQL database, I know tinyint(M) and its differences from tinyint(1) have already been discussed on this site, but I'm wondering something else.

Could a column "REVIEWED TINYINT(5) be used to store the values of 5 different boolean checkboxes in the frontend form? I'm thinking along the lines of treating it as an array. If so, how would they be referenced? Would something like REVIEWED(3) or REVIEWED[3] work for referencing their elements? Would there be a syntax for checking whether all their elements were 1 or 0 (or null)?

score 5 · Accepted Answer · answered Feb 04 '22 at 15:20

TINYINT(1) and TINYINT(5) and TINYINT(12) or any other length are actually stored exactly the same. They are all an 8-bit signed integer. They support integer values from -128 to 127. Or values from 0 to 255 if the column is defined as an unsigned integer.

What's with the "length" argument then? Nothing. It doesn't affect the size of the integer or the number of bits or the range of values. The argument is a display hint only. It's useless unless you use the ZEROFILL option.

mysql> create table mytable (i1 tinyint(1) zerofill, i2 tinyint(5) zerofill, i3 tinyint(12) zerofill);
Query OK, 0 rows affected (0.04 sec)
    
mysql> insert into mytable values (255,255,255);
Query OK, 1 row affected (0.02 sec)

mysql> select * from mytable;
+------+-------+--------------+
| i1   | i2    | i3           |
+------+-------+--------------+
|  255 | 00255 | 000000000255 |
+------+-------+--------------+

The ZEROFILL option forces the column to be unsigned, and when you query the column, it pads the result with zeroes up to the length you defined for the column. The zeroes are not stored in the database, they are added only when you fetch query results.

The "length" argument of integers is misleading, and it causes a lot of confusion for MySQL users. In hindsight, it would have been better to make the syntax like TINYINT ZEROFILL(12) but it's too late to change it now.

What is the utility of tinyint(M) in MySQL?

1 Answers1