Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

220
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda