- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- The best data type to store 0, 1, null values in MySQL?
- What is the best way to earn money online?
- Best data type for storing currency values in a MySQL database?
- What is the best site to invest money in stock market?
- What is the best data type to use for currency in C#?
- What is MySQL ENUM data type? What are the advantages to use ENUM data type?
- What is BLOB data type in MySQL?
- What is TEXT data type in MySQL?
- 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?
- What is the data type for unix_timestamp in MySQL?
- Best data type for storing large strings in MySQL?
- Best way to store weekly event in MySQL?
- Which MySQL Data Type can be used to store Negative Number?
- How should I store data into my Mysql database, what type should I assign to a column storing salt values?