How to save a decimal number to mysql database without knowing the exact amount of digits after the-CodePudding

I have a MySQL database that I need to save decimal numbers in. I normally just use the decimal type, but I am saving currency pair values that can be 2, 3, 4 or 5 digits after the decimal. My temporary fix is to use a Varchar or set the decimal length to 5 and work with some extra zeros on smaller decimals, but this is not optimal for calculations. How can I store a decimal number with different amounts of digits

CodePudding user response：

You can define a DECIMAL column with scale of 5. That will allow values with any number of digits after the decimal point up to 5.

Demo:

mysql> create table mytable (d decimal(12,5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable values (123), (123.4), (123.45), (123.456), (123.4567), (123.45678), (123.456789);
Query OK, 7 rows affected, 1 warning (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 1

mysql> show warnings;
 ------- ------ ---------------------------------------- 
| Level | Code | Message                                |
 ------- ------ ---------------------------------------- 
| Note  | 1265 | Data truncated for column 'd' at row 7 |
 ------- ------ ---------------------------------------- 
1 row in set (0.00 sec)

mysql> select * from mytable;
 ----------- 
| d         |
 ----------- 
| 123.00000 |
| 123.40000 |
| 123.45000 |
| 123.45600 |
| 123.45670 |
| 123.45678 |
| 123.45679 |
 ----------- 
7 rows in set (0.00 sec)

You can see that when I tried to use a number with 6 digits after the decimal point, it was forced to round the value.