金钱的MySQL数据类型 [英] Mysql datatype for money

问题描述

我试图创建一个与货币相关的应用程序，用户可以在其中选择其币种.我尝试过的MySQL数据类型是十进制(19,4).现在的问题是，很少有货币需要三种精度，而有些则需要两种精度 例如: 阿曼里亚尔需要三个精度.即1000 baisa = 1奥马里亚尔.因此，我的客户可能会输入6.783. 在我的美国客户中，只需2个精度(100美分= 1美元)，他们就可以输入5.50.

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.

当我使用十进制(19,4)将这两个条目插入数据库时​​，分别保存为6.7830和5.5000.

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

现在真正的痛苦是当我需要显示其条目时，因为我不想在额外的条目中显示0，而在美元中显示00.我也尝试过浮点运算，但最后一位数字有时会四舍五入.

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.

是否有任何mysql数据类型，我可以在其中保存精确的输入而无需四舍五入或多余的零?如果没有这样的条目，我如何使其可输入?

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?

推荐答案

您可以使用VARCHAR存储精确的表示形式，但是我不建议这样做，因为它需要更多的字节才能将数字存储为字符串.而且，您对值进行的任何算术运算都会将其转换为数字.

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.

我建议您使用DECIMAL(19,4)，然后在应用程序代码中格式化该值，以使用适当的数字显示它.每种编程语言都有一些功能，例如printf()，无论您存储什么值，都可以控制输出格式.

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.

这篇关于金钱的MySQL数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！