- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
What is the best data type to store money values in MySQL?
We can store the money values in MySQL in decimal(value1,value2). Here, value1 is the total range including value2. The value2 specifies the number of digits after the decimal point. To understand this concept, the steps are given below.
First a table is created using the create command.
mysql> CREATE table MoneyDemo -> ( -> Id int, -> Money decimal(10,2) -> ); Query OK, 0 rows affected (0.46 sec)
As can be seen from the above command, the decimal value has 10 digits only and also 2 digits only after the decimal point.
After creating the table, some records are inserted with the help of the insert command. This is given as follows −
mysql> INSERT into MoneyDemo values(1,98777445.50); Query OK, 1 row affected (0.13 sec) mysql> INSERT into MoneyDemo values(2,12345678.00); Query OK, 1 row affected (0.18 sec) mysql> INSERT into MoneyDemo values(3,88888888.50); Query OK, 1 row affected (0.20 sec)
If more than 10 digits are inserted in the above commands, an error message will be generated. This can be seen below −
mysql> INSERT into MoneyDemo values(3,8888888888.50); ERROR 1264 (22003): Out of range value for column 'Money' at row 1
All the records in the table can be displayed with the help of the select statement. The query for this is given below −
mysql> SELECT * from MoneyDemo;
The output obtained is as follows
+------+-------------+ | Id | Money | +------+-------------+ | 1 | 98777445.50 | | 2 | 12345678.00 | | 3 | 88888888.50 | +------+-------------+ 3 rows in set (0.00 sec)
- Related Articles
- The best data type to store 0, 1, null values in MySQL?
- Best data type for storing currency values in a MySQL database?
- What is the best data type to use for currency in C#?
- What is the best way to earn money online?
- How should I store data into my Mysql database, what type should I assign to a column storing salt values?
- Which MySQL Data Type can be used to store Negative Number?
- What is JDBC Blob data type? how to store and read data from it?
- What is JDBC Clob data type? how to store and read data from it?
- Best data type for storing large strings in MySQL?
- What is MySQL ENUM data type? What are the advantages to use ENUM data type?
- Best way to store weekly event in MySQL?
- What is the best site to invest money in stock market?
- How MySQL use YEAR data type to store year value in a table?
- What is BLOB data type in MySQL?
- What is TEXT data type in MySQL?