INT data type

Question

What data type is best to use to represent a 1 digit number from 0 to 9 while also making use of storage space efficiently?

Something doesn't make sense though, if int uses 4 bytes and tinyint uses 1 byte. That doesn't mean that a value suxh as 3 stored as int data type will use 4 bytes totally and that same value will use 1 byte if data type is tinyint. 4 bytes is the MAXIMUM a value can take not that it will take 4 bytes totally right? Then how does it save space if I use tinyint instead?

Answer 1

In MySQL, using tinyint is ok for this. However you should be aware that tinyint is not standard SQL. It is specific to MySQL and actually MSSQL has it too. But it is always good to consider standard features that are universal for all database engines.

If you declare a column with type of INT, it will always occupy 4 bytes. Doesn't matter if the number you are storing, is actually smaller. A column with TINYINT type will always occupy 1 byte. So it is 4 times more efficient in terms of space. But is space really such a big concern? If your data set has only few rows, then it will really not make a big difference. If you have millions of rows, then the difference will be in terms of megabytes. One could argue that this can still be insignificant, depending on your actual project and constraints. Storage is very cheap.

Answer 2

You could take BIT(size)
a bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.

Because your max number should be 9 and that equals to 1001 you can work with
BIT(4).