What type is used for amounts in mysql?

In mysql, the amount uses the "DECIMAL" type. The DECIMAL type is a data type specially designed for financial-related problems. It can solve the problem of data range and accuracy. It is often used for monetary data, such as prices, wages, account balances, etc.; it is actually stored in the form of a string. The number of digits in the integer part and the decimal part can be demarcated when defining. The syntax is "DECIMAL(M,D)". The parameter M is the maximum number of numbers (precision), and the parameter D is the number of digits to the right of the decimal point (scale).

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, the amount uses the "DECIMAL" type.

Decimal (fixed-point number) is a data type specially designed for financial-related problems, which can solve the problem of data range and accuracy.

DECIMAL was introduced from MySQL 5.1, and the column declaration syntax is DECIMAL(M,D). In MySQL 5.1, the value range of the parameter is as follows:

• M is the maximum number of numbers (precision). Its range is 1 to 65 (in older MySQL versions, the allowed range is 1 to 254), and the default value of M is 10.

• D is the number of digits to the right of the decimal point (scale). Its range is 0~30, but it must not exceed M.

Explanation: float occupies 4 bytes, double occupies 8 bytes, and decimail(M,D) occupies M 2 bytes.

For example, the maximum value of DECIMAL(5,2) is 9 9 9 9 . 9 9 because there are 7 bytes available.

The DECIMAL type is different from FLOAT and DOUBLE. DOUBLE is actually stored in the form of a string. The possible maximum value range of DECIMAL is the same as DOUBLE, but the effective value range is determined by M and D. If M is changed and D is fixed, the value range will become larger as M becomes larger.

Emphasize: In MySQL, fixed-point numbers are stored in string form. When precision requirements are relatively high (such as currency, scientific data), use The type of DECIMAL is better. It is also easy to cause problems when subtracting and comparing two other floating-point numbers. Therefore, you need to pay attention when using floating-point numbers and try to avoid floating-point comparisons.

MySQL DECIMAL data type and monetary data

We often use the DECIMAL data type for monetary data, such as prices, wages, account balances, etc. If you are designing a database that handles monetary data, the following syntax should be fine.

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, you should define a column with 4 decimal places like this:

amount DECIMAL(19,4);

MySQL DECIMAL data type example

First, create a new The table name materials has three fields: id, description and cost.

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

Secondly, insert data into the materials table.

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

Third, query data materials from the table.

SELECT 
    *
FROM
    materials;

Fourth, modify the cost column to include the ZEROFILL attribute.

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

Five, query the material table again.

SELECT 
    *
FROM
    materials;

As you can see, we have padded the output value with a lot of zeros.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What type is used for amounts in mysql?. For more information, please follow other related articles on the PHP Chinese website!