Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

219
Visualizações
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 Respostas
Responde à pergunta

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 Relatório

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda