CODEWITHSUNDEEP
mysqlsqldatabasetypes
tread
tread

Reputation: 11098

MySQL datatypes for business applications?

Good day, I am confused with the datatype for MySQL.

I am using decimal as apparently it is the safest bet for accuracy in a business application. However, I find that when fields are returned I have values of 999999999.99, where my datatype is DECIMAL(10,2). So the actual value has overflowed outside the (10, 2) parameter.

Would it be correct that even though I have specified 10 places before the comma and 2 places after the comma. MySQL still stores the complete number?

Also would it be possible to turn off the maximum amount of digits displayed before and after the comma?

Upvotes: 1

Views: 86

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

Would it be correct that even though I have specified 10 places before the comma and 2 places after the comma. MySQL still stores the complete number?

No, it wouldn't.

First, you specified 10 digits altogether; two are to the right of the decimal point, and eight are to the left.

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

Second, MySQL will silently convert the least significant digits to scale if there are more than two. That will probably look like MySQL truncates, but the actual behavior is platform-dependent. It will raise an error if you supply too many of the most significant digits.

Finally, when you're working with databases, the number of digits displayed has little to do with what a data type is or with what range of values it stores.

Upvotes: 1

Related Questions