What Is the Data Type for MONEY in MySQL?

What Is the Data Type for MONEY in MySQL?

In MySQL, the data type used for storing monetary values is MONEY. The MONEY data type allows you to store and manipulate currency values accurately and efficiently in your database.

MONEY Data Type

The MONEY data type in MySQL is a fixed-point number type that can store decimal values with up to four decimal places. It is commonly used to represent and perform calculations on financial data such as prices, salaries, or any other value involving currency.

When defining a column with the MONEY data type, you can specify the maximum number of digits before the decimal point and the maximum number of digits after the decimal point. For example, MONEY(10, 2) will allow for a maximum of 10 digits (including both integral and fractional parts) with two decimal places.

Creating a Table with a MONEY Column

To create a table with a column of the MONEY data type, you can use the following syntax:

CREATE TABLE products (
  id INT,
  name VARCHAR(100),
  price MONEY(10, 2)
);

In this example, we have created a table called “products” with three columns: “id” of type INT, “name” of type VARCHAR(100), and “price” of type MONEY(10, 2). The “price” column will store monetary values up to 10 digits with two decimal places.

Working with MONEY Values

Once you have created a table with a column of the MONEY data type, you can insert and retrieve monetary values using SQL queries. Here’s an example:

INSERT INTO products (id, name, price) VALUES (1, 'Product A', 9.99);
INSERT INTO products (id, name, price) VALUES (2, 'Product B', 19.99);

To retrieve the monetary values from the table, you can use a SELECT statement:

SELECT name, price FROM products;

The above query will return the names and prices of all the products in the “products” table.

Performing Calculations with MONEY Values

The MONEY data type in MySQL allows you to perform various calculations on monetary values using arithmetic operators such as +, -, *, and /.

Here’s an example that demonstrates how to calculate the total price of all products:

SELECT SUM(price) AS total_price FROM products;

The above query will return the sum of all prices in the “products” table as “total_price”.

Conclusion

The MONEY data type in MySQL is a reliable and efficient way to store and manipulate monetary values in your database. By understanding how to define columns with the MONEY data type and perform calculations on these values, you can ensure accurate financial operations within your application.