MySQL Variable for human height (tinyint or smallint)

Question

I am new to SQL! So if I want to add a person's height to my table (ex 1.86) what variable type should I use?? Tinyint or Smallint?

You should probably mention which SQL engine are you using, MySQL, MSSQL, Oracle etc. If you want to store the height value as a decimal then you can't use an integer type such as TINYINT, you will need to look at fixed-point or floating-point data types instead. If you were to use an integer type (to store centimetres for example rather than fractional meters) then an UNSIGNED TINYINT would limit you to a maximum value of 255 and a SIGNED TINYINT to 127, see: http://dev.mysql.com/doc/refman/5.7/en/integer-types.html — Robert Hunt, May 26 '16 at 09:55

score 0 · Answer 1 · edited May 23 '17 at 12:24

You should use

to store :: 1.86 AS human height, use DOUBLE(3,2)

in above case, you may also use DECIMAL(3,2), but DECIMAL is mostly used for financial figures as DOUBLE causes some rounding issues.

if you can convert 1.86 meters into cm

then to store :: 186 cm as human height, use SMALLINT

for optimal datatypes :

score 0 · Answer 2 · answered Jul 14 '17 at 15:31

I would skip the decimal point and store it as centimeters.

Then, just use an UNSIGNED TINYINT (max value of 255 = 2.55 meters) or a SMALLINT (max value of 32767).

It'd use a fraction of the byte storage (compared to a FLOAT or DECIMAL) and is arguably nicer to work with.

2 Answers2