英文:
Difference between calculated value in PHP and the same value inserted in database
问题
我遇到了一个非常奇怪的问题。我正在开发一个自定义的WordPress插件，我有一个数据库表，其中有一个名为pending_balance的列，它是decimal(8,8)数据类型。另一方面，我有这段代码（实际上是一个定时任务）：
if ( ! empty( $subscriptions ) ) {
foreach ( $subscriptions as $subscription ) {
if($subscription->status != "inactive") {
// 计算 $new_pending_balance
error_log($algorithm . " - " . $new_pending_balance . PHP_EOL, 3, WCM_PLUGIN_DIR . 'error.log');
$wpdb->update(
$table_name,
array( 'pending_balance' => $new_pending_balance ),
array( 'id' => $subscription->id ),
array( '%f' ),
array( '%d' )
);
}
}
}
当定时任务运行时，我可以清楚地在error.log中看到新的日志。例如：
bitcoin - 0.00038531
litecoin - 0.10420367
ethereumclassic - 1.27727247
但是，当我打开数据库表以查看ethereumclassic的新待处理余额时，它显示为0.99999999而不是1.27727247。我尝试了很多方法，比如将$new_pending_balance转换为完全相同的数据类型，我尝试使用原始查询而不是在$wpdb上使用->update()方法。唯一我还没有尝试的是将列的数据类型更改为字符串，以便不会出现问题。但是我担心这样做，因为插件内部的所有计算都使用从数据库检索的纯值，而我现在实际上从数据库中检索到了一个浮点数。如果我将其更改为字符串，可能会导致错误。各位，我该怎么办？
英文:
I'm having a really strange problem. I'm developing a custom WordPress plugin and I have a database table which has a column named pending_balance which is a decimal(8,8) datatype. And on the other hand I have this code (which is actually a cron job):
if ( ! empty( $subscriptions ) ) {
foreach ( $subscriptions as $subscription ) {
if($subscription->status != "inactive") {
// Calculating $new_pending_balance
error_log($algorithm . " - " . $new_pending_balance . PHP_EOL, 3, WCM_PLUGIN_DIR . 'error.log');
$wpdb->update(
$table_name,
array( 'pending_balance' => $new_pending_balance ),
array( 'id' => $subscription->id ),
array( '%f' ),
array( '%d' )
);
}
}
}
When the cron job runs I can clearly see the new logs in error.log. For example:
bitcoin - 0.00038531
litecoin - 0.10420367
ethereumclassic - 1.27727247
And when I open the database table so I can see the new pending balance for ethereumclassic it says 0.99999999 and not 1.27727247. I've tried many things like converting the $new_pending_balance to 100% exact same datatype, I've tried to do a RAW query instead of using the ->update() method on the $wpdb. The only thing I haven't tried yet is to change the column datatype to string so I can have no problems. But I'm afraid doing this as everywhere inside the plugin the calculations are with the pure value retrieved from the database and I'm actually retrieving a float number from the database right now. If I change it to a string it may cause bugs. What can I do, guys?
答案1
得分: 1
DECIMAL(8,8) 意味着可以存储八位数字，其中八位是小数部分。这就不留空间给 1 在 1.27727247 中；您的列可以存储从 -0.99999999 到 0.99999999 的值。
> 标准 SQL 要求 DECIMAL(5,2) 能够存储具有五位数字和两位小数的任何值，因此可以存储在薪水列中的值范围为 -999.99 到 999.99。
您应考虑打开MySQL 的"严格模式"，这样会让您收到一个"超出范围"的错误，而不是静默转换数值。
英文:
DECIMAL(8,8) means eight digits can be stored, eight of which are decimals. That leaves no room for your 1 in 1.27727247; your column can store values from -0.99999999 to 0.99999999.
https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html
> Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.
You should consider turning on MySQL's "strict mode", which would've given you an "out of range" error instead of silently converting the value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论