mysql数据库数据类型money_钱的MySQL数据类型

i was trying to create a money related app in which users can choose their currency. Mysql datatype i tried is decimal(19,4). Now the problem is few currencies need three precisions and some need two

Eg:

oman rial needs three precisions. ie 1000 baisa = 1 omani rial. Hence my customers may enter 6.783 omani rial.

Where as my US customers will need only 2 precisions as 100 cents = 1 dollar and they may enter 5.50.

When i insert these two entries to my database using decimal(19,4), it is saved as 6.7830 and 5.5000 respectively.

Now the real pain is when i need to display their entrys as i dont want to display that extra 0 in omani rial entry and that 00 in US dollar. I also tried float but last digit gets rounded off at times.

Is there any mysql data type in which i can save exact entry as it is without any rounding off or extra zeros? If there is no such entry, how can i make it ppssible?

解决方案

You can use VARCHAR to store exact representations, but I don't recommend that because it takes more bytes to store a number as a string. And any arithmetic you do on the value will convert it to a number anyway.

I recommend you use DECIMAL(19,4), and then format the value in application code, to display it with the appropriate digits. Every programming language has some function like printf() that allows you to control the output formatting, regardless of the value stored.