PHP/MySQL:最佳的货币操作/存储方式? [英] PHP/MySQL: Best money operations/storing practices?

问题描述

因此，我打算制作一个可以处理大量金钱的应用程序(PHP/MySQL)，并且我正在考虑如何使用金钱来存储和操作这些金钱，这涉及到PHP float数据类型和MySQL十进制.

So, I am planning to make an application (PHP/MySQL) which deals a lot with money, and I am thinking about how to store and operate with the money, referring to PHP float data type and MySQL decimal.

我在想两个选择.其中之一是以整数美分格式($ dollars * 100)操作和存储货币，以便不处理浮点运算，也将其也存储为整数.另一种是在数据库中以小数形式存储，并在PHP中使用BC Math进行计算.

I was thinking of two options. One of them is to operate and store money in integer cents format ($dollars * 100) in order not to deal with float inprecisions and to store it in the DB as integer too. The other one is to store in DB as decimal and to use BC Math in PHP for calculations.

因此，我整夜在Google上搜索以找出哪种方法是最好的选择，却没有找到明确的答案. 我所见过的唯一合理的选择是整数美分(我不是很喜欢，因为这意味着在浏览器中的每次显示之前以及存储在数据库中之前，都需要从美元转换为美分，反之亦然).

So I googled all the night to find out which is the best option to use and didn't find a clear answer. The only reasonable option that I've seen was the integer cents one (which I don't really like because it would imply a lot of converting from dollars to cents and viceversa before every display in the browser and before storing in the DB).

此外，人们还抱怨MySQL的十进制数(MySQL将十进制数存储为字符串，将其作为浮点数进行操作等)，但这是古老的帖子.根据MySQL文档，当前版本正确处理小数，唯一的抱怨是它会截断超过声明的分数长度的值的分数(例如，如果在声明为October(9,2)的列中存储值12.326) ，但根据我的调查，它会四舍五入，而不仅仅是截断(12.326变为12.33)，这在我看来是正确的.

Also, people have complained about MySQL decimal (MySQL stores decimals as strings, operates them as floats etc.), but that were old posts. According to MySQL documentation, the current version handles decimals correctly, the only complaint was that it truncates the fraction of the values that exceeds the declared fraction length (e.g. if you store a value of 12.326 in a column declared decimal(9,2)), but from my investigations it rounds it instead of just truncating (12.326 becomes 12.33), which is correct in my opinion.

而且，我没有找到任何建议将货币存储为小数并使用PHP BCMath进行计算，在我看来，这是因为很少有人了解BC和GMP数学函数.

And, I didn't find any recommendation on storing money as decimals and make calculations using PHP BCMath, and in my opinion this is because few people know about BC and GMP math functions.

那么，考虑到精度，速度(BCMath计算速度，MySQL十进制速度与整数的比较)和编程舒适性，什么是最好的选择?

So, what would be the best option to use, considering precision, speed (BCMath calculations speed, MySQL decimal speed vs integer) and programming comfort?

推荐答案

我肯定会使用ints并通过数据对象(ORM)样式路由所有内容，然后由您处理所有转换.使用数据对象的客户端代码将永远不需要进行转换，也不会在乎，同时您也不会遇到存储问题，因为DB可以轻松处理int.此外，您可以轻松地添加货币对象所需的任何其他方法(例如货币类型之间的转换等).

I'd definitely go for using ints and routing everything through a data object (ORM) style that then handles all conversion for you. The client code using the data object will never need to do conversion and won't care, while you won't have problems with storage as ints are handled easily by the DB. Furthermore, you can then add whatever other methods are needed for the money object (like conversions between money types, etc) quite easily.

这篇关于PHP/MySQL:最佳的货币操作/存储方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！