JIYIK CN >

This article introduces the DECIMAL(P,D) data type which is best suited for storing monetary values ​​in MySQL.

MySQL currency data type

Monetary values ​​need to be represented accurately. We don't need to use only approximate data types such as float.

To insert monetary data, we can use fixed-point numeric data types such as DECIMAL(P,D).

DECIMAL(P,D)Indicates that a column can store at most P digits and D decimal places. P and D are explained after the syntax.

We can define a DECIMAL(P,D)column with data type .

# Syntax
columnName DECIMAL(P,D)

Where P represents the precision of the number of significant digits, and its range is 1 to 65.

Although D is a scale, it represents the number of digits after the decimal point. The value range of D is 0 to 30.

DECIMAL(P,D)The condition D<=P must be met when using ; otherwise, we will get an error. If P and D are not specified, the maximum number of digits in a DECIMAL type column is 65.

The range of a DECIMAL type column depends on the precision (P) and scale (D). Let us understand it by creating a table demonstration with two attributes ID and MONEY and fill it with some data.

You can follow up with us using the following enquiries.

Sample code:

# create a table
CREATE table demo(
    ID INT NOT NULL PRIMARY KEY,
    MONEY DECIMAL(12,2) NOT NULL
);

# insert data
INSERT INTO demo VALUES
(1,1254367892.50),
(2,8754367893.60),
(3,9854367895.65),
(4,9224367896.70);

# display data
SELECT * FROM demo;

Output:

+----+---------------+
| ID | MONEY         |
+----+---------------+
|  1 | 1254367892.50 |
|  2 | 8754367893.60 |
|  3 | 9854367895.65 |
|  4 | 9224367896.70 |
+----+---------------+
4 rows in set (0.00 sec)

We can also use FIXED, DEC or NUMERIC keywords instead of DECIMAL keyword as all mentioned keywords are synonyms of DECIMAL. See the following example as a demonstration.

Sample code:

# create a table
CREATE table demo1(
    ID INT NOT NULL PRIMARY KEY,
    MONEY FIXED(12,2) NOT NULL
);

# insert data
INSERT INTO demo1 VALUES
(1,1254367892.50),
(2,8754367893.60),
(3,9854367895.65),
(4,9224367896.70);

# display data
SELECT * FROM demo;

Output:

+----+---------------+
| ID | MONEY         |
+----+---------------+
|  1 | 1254367892.50 |
|  2 | 8754367893.60 |
|  3 | 9854367895.65 |
|  4 | 9224367896.70 |
+----+---------------+
4 rows in set (0.00 sec)

As with INT, we can use the ZEROFILL and UNSIGNED attributes of the DECIMAL data type. With the UNSIGNED attribute, DECIMAL(P,D) UNSIGNEDthe table field of the type cannot take negative values.

For example, the table created with the following query does not accept negative values ​​for the MONEY field.

Sample code:

# create a table
CREATE table demo2(
    ID INT NOT NULL PRIMARY KEY,
    MONEY DECIMAL(12,2) UNSIGNED NOT NULL
);

With the ZEROFILL attribute, MySQL fills the result value with zeros until the width specified by the column definition.

请记住, if we use the ZEROFILL attribute, MySQL will automatically add UNSIGNED. We can check it using DESCRIBE tableName; query.

See the following code as an example.

# create a table
CREATE table demo3(
    ID INT NOT NULL PRIMARY KEY,
    MONEY DECIMAL(12,2) ZEROFILL NOT NULL
);

# describe a table
DESCRIBE demo3;

Output:

+-------+---------------------------------+------+-----+---------+-------+
| Field | Type                            | Null | Key | Default | Extra |
+-------+---------------------------------+------+-----+---------+-------+
| ID    | int                             | NO   | PRI | NULL    |       |
| MONEY | decimal(12,2) unsigned zerofill | NO   |     | NULL    |       |
+-------+---------------------------------+------+-----+---------+-------+
2 rows in set (0.26 sec)

Always remember that DECIMAL(P) is equivalent to DECIMAL(P,0), DECIMAL is equivalent to DECIMAL(P,0). By using DECIMAL(P,0), we will have no decimal part (fractional portion).

The implementations DECIMAL(P) and DECIMAL(P,0) let us decide the value of P. If we use the default implementation of DECIMAL, the default value of P is 10.