MySQL Money Data Type
This tutorial introduces the DECIMAL(P,D) data type that best suits storing money values in MySQL.
MySQL Money Data Type
Money values need the exact representation. We don’t need to use approximate data types only, for instance, float.
To insert monetary data, we can get the advantage of a fixed-point numeric data type, for example, DECIMAL(P,D).
The DECIMAL(P,D) tells that a column can store up to P digits and D decimals. The P and D are explained after the syntax.
We can define a column with the DECIMAL(P,D) data type.
# Syntax
columnName DECIMAL(P,D)
Here, P represents the precision that shows the significant digits’ number. Its range is 1 to 65.
While D is a scale, it speaks for the number of digits that we can have after the decimal point. The range of D is 0 and 30.
While using DECIMAL(P,D) must meet the condition saying D<=P; otherwise, we will get an error. If the P and D are not specified, the maximum number of digits would be 65 for the DECIMAL type column.
The range of a DECIMAL type column depends on the precision (P) and scale (D). Let’s understand it by creating a table demo with two attributes, ID and MONEY, and populating some data.
You may use the following queries to follow up with us.
Example 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 the FIXED, DEC, or NUMERIC keywords instead of the DECIMAL keyword because all of the mentioned keywords are the synonym of DECIMAL. See the following example as a demonstration.
Example 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)
Like INT, we can use the ZEROFILL and UNSIGNED attributes of the DECIMAL data type. Using the UNSIGNED attribute, the table field of type DECIMAL(P,D) UNSIGNED cannot take negative values.
For instance, the table created using the following query does not accept the negative value for the MONEY field.
Example Code:
# create a table
CREATE table demo2(
ID INT NOT NULL PRIMARY KEY,
MONEY DECIMAL(12,2) UNSIGNED NOT NULL
);
Using the ZEROFILL attribute, the MySQL pads the result values with 0 up to the width specified by a column definition.
Remember, MySQL adds UNSIGNED automatically if we use the ZEROFILL attribute. We can check it by using the 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 the DECIMAL(P) is equivalent to the DECIMAL(P,0), and the DECIMAL is equivalent to the DECIMAL(P,0). We will not have a decimal part (fractional part) by using DECIMAL(P,0).
The DECIMAL(P) and DECIMAL(P,0) implementations let us decide the value of P. The default value of P is 10 if we use the default implementation DECIMAL.
You can check this for more information.
