Should I input 2 digit numbers in TINYINT?

Question

I need a column that can store values from -1 (negative 1) to +15 (positive 15).

I am using a TINYINT(2) column at the moment and it works just fine.

I need to know if this is ideal and safe to use because I read online that TINYINT should ONLY be used for binary value such as 1 and 0.

You are good. A signed `TINYINT` is perfect for this usage, if your values are in the range -128 to +127. — The Impaler, Jul 17 '20 at 14:41
Beware of learning rules along the line of "X should ONLY be used for Y". If that didn't come with a) reasons and b) constraints on when that rule should be followed/can be ignored, it's probably bad advice. If it did come with such, you need to remember/reference those and determine whether this rule actually applies to your situation. — Damien_The_Unbeliever, Jul 17 '20 at 15:55
BIT is the data type that can only store 0 and 1. You are fine using TINYINT for storing a number -1 to +15. As has been stated multiple times TINYINT can store between 0 and 255 OR -128 and 127 — Daniel, Jul 17 '20 at 19:18
What should the Length/Value be instead of 2? If I remove it and save, it automatically sets to 4 — Kabir Mongeau, Jul 17 '20 at 19:21

ns15 · Answer 1 · 2020-07-17T16:35:54.607

3

MySQL TINYINT has a storage of 1 byte.

Unsigned value in range 0 to 255 or signed value of range -128 to 127 can be stored.

Also note that 2 in TINYINT(2) does not limit the length of your value to 2 digits. more info in this link - What is the size of column of int(11) in mysql in bytes?

edited Jul 17 '20 at 16:35

answered Jul 17 '20 at 14:50

ns15

5,604
47
51

Then what should be the ideal Length/Value of TINYINT if not 2, for my specific requirement? – Kabir Mongeau Jul 17 '20 at 18:59
1

display width 4 is the default for mysql TINYINT, and i suggest you leave it as it is. In majority of cases display width will not matter. You can read more on this in the link i shared above. As far as limiting the length is concerned, easy way is to have this logic in your application(this is how its mostly done) or use mysql check constraint in your db. – ns15 Jul 18 '20 at 03:54

Philip Petrov · Answer 2 · 2020-07-17T15:56:30.753

TINYINT will store your value perfectly fine; however it will not guarantee that it will be correct (exactly in that range). Please note that (2) is just related to padding when selecting via the command line and it has nothing to do with the limit. Normally you do not need it at all. TINYINT (signed) will store values from -128 to 127 regardless if you add (n) at the end of it or not.

If you want to make sure that the value is always exactly in the range between -1 and 15, you have two options:

Using CHECK constraint over the existing TINYINT (recommended solution):

CREATE TABLE tiexample(
  val TINYINT NOT NULL,
  CONSTRAINT val_range CHECK(val>=-1 AND val<=15)
);

INSERT INTO tiexample(val) VALUES(122);
ERROR 4025 (23000): CONSTRAINT `val_range` failed for `test`.`tiexample`

INSERT INTO tiexample(val) VALUES(12);
Query OK, 1 row affected (0.001 sec)

Listing all values as ENUM:
```
CREATE TABLE tiexample(
   val ENUM("-1","0","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15") NOT NULL,
   CONSTRAINT val_range CHECK(val<>"")
);

INSERT INTO tiexample(val) VALUES("122");
ERROR 4025 (23000): CONSTRAINT `val_range` failed for `test`.`tiexample`

INSERT INTO tiexample(val) VALUES("12");
Query OK, 1 row affected (0.001 sec)
```
Note that using this method you must insert the values with quotes (like "8", not just 8). Also first it may seem strange that we use CHECK constraint here but it is needed because by default inserting invalid value on ENUM field will insert an empty string in the table (not NULL - it's an empty string). You can get around that issue and skip the CHECK constraint if you use this sql mode before inserting into the table:
```
SET SQL_MODE = 'Traditional';
```
Personally I would not struggle with the ENUM type. It is more suitable for different use-cases.

Footnote: CHECK constraints are available and working only in MySQL MySQL 8.0.16+ and MariaDB 10.2.1+. Prior versions accept the constraint but never use it (it will ignore them).

Then what should be the ideal Length/Value of TINYINT if not 2, for my specific requirement? — Kabir Mongeau, Jul 17 '20 at 19:00
when I remove the length/value, it automatically sets it to 4 after saving. — Kabir Mongeau, Jul 17 '20 at 19:20
You do not understand what the (2) is doing. It is a display width setting. It does not control the value ranges that the column can store - therefore TinyINT(2), TINYINT(4) or just TINYINT is all storing exactly the same data. What is does is just a formatting for how the table is displayed in the console when you execute the query. For practical uses, it is not important at all. — Philip Petrov, Jul 17 '20 at 19:33

Should I input 2 digit numbers in TINYINT?

2 Answers2