Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

217
Views
Should I input 2 digit numbers in TINYINT?

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.

over 4 years ago · Santiago Trujillo
2 answers
Answer question

0

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?

over 4 years ago · Santiago Trujillo Report

0

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:

  1. 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)
    
  2. 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).

over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!