In this tutorial, we'll show you how to change a number of decimal places in MySQL for the decimal column type. You'll need to use MODIFY operator and set both type and type definition - in this case, that would be DECIMAL (17,6) where we have  17 non-decimal (significant digits) and 6 decimal places. So, what we need to do is to execute the following SQL code:

ALTER TABLE someTable
MODIFY someField DECIMAL(17,6)

More about MySQL DECIMAL type

The number of non-decimal places is also called precision and its range is between 1 and 65. The number of decimal places is called scale and its range is between 0 and 30, so yes, you can define a decimal type in MySQL without any decimal places. An important rule in MYSQL is that number of decimal places must be less than or equal to the number of non-decimal places. So, scale <= precision.

It's also important to mention that DECIMAL keyword has synonyms in MySQL, and those are: DEC, FIXED, or NUMERIC. So, we can use any of them to get to the same result. Like the INT data type, the DECIMAL also has UNSIGNED and ZEROFILL attributes.

DECIMAL is often used to store financial data like salaries, transaction values, etc.  Public companies in the United States must follow GAAP (Generally Accepted Accounting Principles) that propose using 4 decimal places to store financial data. Keep this in mind when creating columns that should contain financial data.

Pin It

Newsletter

Please enable the javascript to submit this form