CODEWITHSUNDEEP
mysqltypes
Shafizadeh
Shafizadeh

Reputation: 10340

Best datatype to store a single digit?

I need a datatype to store this range: [0 - 9]. Something like this:

+-----+
| col |
+-----+
| 3   |
| 2   |
| 8   |
| 0   |
| 2   |
| 1   |
+-----+

What datatype is the best in this case?

Upvotes: 8

Views: 5399

Answers (3)

MAK
MAK

Reputation: 39

You can use int, bigint, smallint, and tinyint depends on the requirement.

https://msdn.microsoft.com/en-us/library/ms187745.aspx

May be you can store the values in enumeration and use the same class.

@Shafizadeh I'm new here and I've mention to use enum first among all the answers but my answer got marked negative sadly.

Upvotes: -1

Harper Maddox
Harper Maddox

Reputation: 550

You can force it to store a single digit using DECIMAL(1,0).

See https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

Upvotes: 2

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385144

Despite the documentation, I'm not clear on whether the size of a DECIMAL(1,0) is "rounded up" to four bytesm or whether the single digit counts as a "left-over digit", resulting in a width of a single byte for the whole data type.

Regardless, you can't use less space than a TINYINT, because a TINYINT is a single byte wide, and a byte is the smallest unit of data on a computer (without getting into bitpacking, which doesn't seem plausible on tabular data!).

So, of the numeric types, use a TINYINT. Anything else is needless obfuscation.

That being said, I'm a big fan of ENUMs, and this would seem to be a perfect case for them. Since your enumeration would have fewer than 255 possible values, like a TINYINT it would only take a single byte.

ENUM('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')

Sadly, it's a little verbose to write out. But at least now your values are inherently range-constrained.

Upvotes: 8

Related Questions