MySQL data type DECIMAL usage

MySQL data type DECIMAL usage

In the MySQL database, DECIMAL is a data type used to store precise values. It is stored as a string and occupies a fixed storage space, so precision and accuracy are ensured. The DECIMAL data type is suitable for storing numbers that require precise calculations, such as monetary amounts, percentages, etc.

The syntax of the DECIMAL type is as follows:

DECIMAL(P, D)

Among them, P represents the total number of digits, and D represents the number of digits after the decimal point. The value range of P is 1 to 65, and the default value is 10. The value range of D is 0 to 30, and the default value is 0. The value of P must be greater than or equal to D.

Next, we illustrate the usage of the DECIMAL type through some specific code examples.

1. Create a table that stores currency amounts:

CREATE TABLE currency (

id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(10, 2)

);

In this example, We created a table called currency, which contains two columns: id and amount. The data type of the amount column is DECIMAL, with a total of 10 digits, including 2 digits after the decimal point.

2. Insert data into the table:

INSERT INTO currency (amount) VALUES (123.45);
INSERT INTO currency (amount) VALUES (67.89);

In this example, we inserted two pieces of data into the currency table, namely 123.45 and 67.89, which are both valid currency amounts. Since the data type of the amount column is DECIMAL(10, 2), the inserted data will be automatically rounded to two decimal places.

3. Query the data in the table:

SELECT * FROM currency;

Run this query statement to view all the data in the currency table. The output is similar to the following format:

id	amount
1	123.45
2	67.89

4. Perform numerical calculations:

SELECT amount * 2 AS doubled_amount FROM currency;

In this example, we query the value of the amount column and multiply it by 2, and then return the calculation result as the alias doubled_amount . The output is similar to the following format:

doubled_amount

246.90

135.78

Through this calculation example, we can see that the DECIMAL type is very efficient and accurate for precise calculations.

Summary:
The DECIMAL data type is a data type used to store precise values ​​in MySQL. It is suitable for numbers that require precise calculations, such as monetary amounts and percentages, etc. When using the DECIMAL type, you need to specify the total number of digits and the number of digits after the decimal point. The DECIMAL type ensures the precision and accuracy of numbers and supports numerical calculation operations.

The above is the detailed content of MySQL data type DECIMAL usage. For more information, please follow other related articles on the PHP Chinese website!