Detailed explanation of the usage of MySQL data type DECIMAL

MySQL DECIMAL data type is used to store exact numeric values ​​in the database. We often use the DECIMAL data type for columns that retain exact precision, such as monetary data in accounting systems.

To define a column with the data type DECIMAL , use the following syntax:

column_name DECIMAL(P,D);

In the above syntax:

• P is the precision indicating the number of significant figures. The range of P is 1〜65 .
• D represents the number of digits after the decimal point. The range of D is 0 ~ 30 . MySQL requires that D be less than or equal to ( <= ) P .

DECIMAL(P，D) means that the column can store P digits with D decimal places. The actual range of a decimal column depends on the precision and scale.

Like the INT data type, the DECIMAL data type also has the UNSIGNED and ZEROFILL attributes. If the UNSIGNED attribute is used, a DECIMAL UNSIGNED column will not accept negative values.

If you use ZEROFILL , MySQL pads the displayed value with 0 to display the width specified by the column definition. Additionally, if we use ZERO FILL for DECIMAL column, MySQL will automatically add the UNSIGNED attribute to the column.

The following example defines a column called amount using the DECIMAL data type.

amount DECIMAL(6,2);

In this example, amount column can store up to 6 digits with 2 decimal places; therefore, the range of amount column is from -9999.99 to 9999.99 .

MySQL allows the following syntax:

column_name DECIMAL(P);

This is equivalent to:

column_name DECIMAL(P,0);

In this case, the column contains no decimal part or decimal point.

Moreover, we can even use the following syntax.

column_name DECIMAL;

In this case, the default value of P is 10 .

MySQL DECIMAL storage

MySQL allocates storage space for the integer and decimal parts separately. MySQL stores DECIMAL values ​​using binary format. It packs 9 digits into 4 bytes.

For each part, 4 bytes are needed to store each multiple of 9 bit number. The storage required for the remaining numbers is shown in the following table:

For example, DECIMAL(19,9) has 9 digits for the fractional part and 19 = 10 digits for the integer part, requiring 4 bytes for the fractional part. The integer part requires 4 bytes for the first 9 digits and 1 byte for the 1 remaining digit. DECIMAL(19,9) column requires a total of 9 bytes.

MySQL DECIMAL data type and monetary data

Monetary data such as prices, salaries, account balances, etc. often use the DECIMAL data type. If you want to design a database to handle monetary data, you can refer to the following syntax −

amount DECIMAL(19,2);

However, if you want to comply with Generally Accepted Accounting Principles (GAAP) rules, the currency column must contain at least 4 decimal places to ensure that the rounded value does not exceed $0.01 . In this case, the column with 4 decimal places should be defined as follows:

amount DECIMAL(19,4);

MySQL DECIMAL Data Type Example

First, create a new table called test_order with three columns: id , description , and cost .

CREATE TABLE test_order (
 id INT AUTO_INCREMENT PRIMARY KEY,
 description VARCHAR(255),
 cost DECIMAL(19,4) NOT NULL
);

The second step is to insert data into the test_order table.

INSERT INTO test_order(description,cost)
VALUES('Bicycle', 500.34),('Seat',10.23),('Break',5.21);

The third step is to query data from the test_order table.

SELECT * from test_order

查詢結果：


Step 4. Change cost column to include the ZEROFILL attribute.

ALTER TABLE test_order
MODIFY cost DECIMAL(19,4) zerofill;

Step 5. Query the test_order table again.

SELECT * from test_order

Query results:

As you can see above, there are many zeros filled in the output values.

Because of zerofill, we will get an error when we insert a negative value:

INSERT INTO test_order(description,cost)
VALUES('test', -100.11);
<br>Tips:
[SQL]INSERT INTO test_order(description,cost)
VALUES('test', -100.11)

[Err] 1264 - Out of range value for column 'cost' at row 1

Other insertion test conclusions:

When the value is within the range and has extra decimal places, the extra decimal places will be truncated directly after rounding.

If the value is outside the range, an Out of range value error will be reported directly.

This is the end of this article about the detailed usage of the MySQL data type DECIMAL. For more information about the MySQL data type DECIMAL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!