How to determine the most suitable data type for a fixed-size numeric field?

1

I have a question about the characteristics of some attributes of my table.

Problem: I have to store an identification number of my items, this is always composed of 3 digits (zero left in case of numbers less than 3 digits). I can literally record the final number in the database, just as I can write ignoring 0 (zero) on the left and treat it in the application. I believe that to write zero to the left I will have to use the data type char(3) , since to write the integer I must use smallint(3) .

Note: also consider that the data types cited consume:

  • smallint = 2 bytes
  • char = 1 byte

Considering that this is an identification number that has no relationship and will not be used for calculation formulas, there is some rule that determines the type of data most suitable for this column, considering performance ( in my performance context is priority) and storage ?

Note: My environment is composed of Laravel 5.6, php 7.2 e MySQL 5.7

    
asked by anonymous 20.03.2018 / 15:55

1 answer

1

In terms of performance anyway, it will change nanoseconds into something that should take milliseconds.

You'll get more performance tuning the database than doing this.

The busy space account is not always so simple, and it will change very little for you.

If you want to be automatically incremented, it can only be numeric.

If the code is a text, the right one is a char (3) and would have to manage the increment.

Someone gives a solution using both, one to increment and to be primary key and another to show the code as it should be.

    
20.03.2018 / 18:05