MySQL is the Most Popular Database Software when it comes to Websites. All Popular Content Management Systems work with MySQL like WordPress, vBulletin, Drupal, etc. But, you are probably reading this post because you are designing your own database wish to get a better understanding of the Number data types of MySQL, especially their size/capacity.
Integer Types
TINYINT – It Can Hold values from -128 to 127. Or 0 to 255 for Unsigned.
SMALLINT -It can Hold values -32768 to 32767 or 0 to 65535 UNSIGNED. This is most commonly used field for most websites.
MEDIUMINT – It can Hold values from -8388608 to 8388607 or 0 to 16777215 UNSIGNED.
INT – It can hold Values from -2147483648 to 2147483647 or 0 to 4294967295 UNSIGNED.
BIGINT – -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615.
What to store a Bigger Integer? I don’t think MySQL provides anything Larger than BIGINT which can store values upto 264, which is a huge Number indeed. To Store Larger Values you can store them as Varchar, but won’t be able to process them like Integers.
Do you wish to store only a 4 Digit Number? To do so you can declare the data-type for any field as SMALLINT(4). All the above types take a parameter (size), which specifies the No. of Digits to be stores. But, you can not do something like TINYINT(8).
Floating Types
There are 3 Such Types in MySQL.
FLOAT
DOUBLE
DECIMAL
All these three Types, can be specified by the following Parameters (size, d). Where size is the total size of the String, and d represents precision. E.g To store a Number like 12345.678, you will set the Datatype to DOUBLE(8, 3) where 8 is the total no. of digits excluding the decimal point, and 3 is the no. of digits to follow the decimal.
FLOAT and DOUBLE, both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column. FLOAT is accurate to approximately 7 decimal places, and DOUBLE upto 14.
Decimal’s declaration and functioning is similar to Double. But there is one big difference between floating point values and decimal (numeric) values. We use DECIMAL data type to store exact numeric values, where we do not want precision but exact and accurate values. A Decimal type can store a Maximum of 65 Digits, with 30 digits after decimal point.
For those who did not understand, let me explain with an Example. Create 2 Columns with Types Double and Decimal and Store value 1.95 in both of them. If you print each column as Integer then you will see than Double Column has printed 1, while Decimal column printed 2.
Generally, Float values are good for scientific Calculations, but should not be used for Financial/Monetary Values. For Business Oriented Math, always use Decimal.
What about using measure numbers, for example. I have a db where i need to put weight, dimensions, height, etc… (76.749 kg), (4455.56 m).
In the last example, know i can just set for example 76749 g., but what is the best column type for this, when not using decimals?
And if i need to use decimals, what is the best choice?
Good article by the way:
Regrets, Miguel.
The answer to this question would be really subjective. For Example, if the weight you are talking about refers to the weight of Gold, which has to be sold at some point in time. I am sure you would want to use Decimal for it, and not Double at all unless you can afford major losses.
If you are talking about Human beings, then I don’t think errors upto 100 grams is a major deal. So for weight, you could use here something like DOUBLE(5,2) to store 074.74 kg.
You say 1234.567 would be DOUBLE(8, 3), but the separator (the dot) isn’t counted, so it must be DOUBLE(7, 3), right?
I am sorry about that error. It has been corrected. to Store, 1234.567 We would use
DOUBLE(7,3)
.I know this is an old post, but are you sure that the maximum number of digits include the decimal point?
The MySQL docs do not specify that M in the declaration syntax for decimal: DECIMAL(M,D) is supposed to include the decimal point.
Please correct me if I’m wrong.
No, You are correct. I have made the changes in the article.
Storing statistical data, do I need DECIMAL, FLOAT or DOUBLE? – CodingBlog