mysql 保留两位小数查询_如何使用MySql查询将数据库中的数字四舍五入到小数点后两位...

最新推荐文章于 2022-03-15 10:56:21 发布

weixin_39708737

最新推荐文章于 2022-03-15 10:56:21 发布

阅读量337

点赞数

文章标签： mysql 保留两位小数查询

本文链接：https://blog.csdn.net/weixin_39708737/article/details/111742315

版权

I want to round the numbers to 2 decimal points in a woo commerce database table wp_postmeta.

There is a column 'meta_key' with row '_price' and a column 'meta_value' which has all kinds of data in it as well as the numbers in the row '_price' that need to be rounded and reduced to two decimal places.

(I know I'll also have to do that for _regular_price)

I am a complete beginner with SQL. I've managed to do a few things in PHPMyAdmin by using the Search to give me the syntax or copying and changing answers found on the net, but this one has me stumped, just not enough knowledge yet.

I managed to add 10% to all the prices with this query -

update wp_postmeta set meta_value = meta_value * 1.10 where meta_key='_regular_price'

I've read that there's a 'ROUND' function, but not sure how to write the query.

I'm guessing it would be something like this -

UPDATE wp_postmeta ROUND meta_value where meta_key='_price'

Hope someone here can help.

解决方案ROUND(X), ROUND(X,D)

Rounds the argument X to D decimal places. [...] D defaults to 0 if not specified.

So your update would be:

UPDATE wp_postmeta

SET meta_value = ROUND(meta_value, 2)

WHERE meta_key='_price'

Display formats

If your concern is to display a number with 2 decimal digits, it might be better to keep the complete precision as it is, but change the way you select values from your table, and us format:

Without the above update, you can still do this:

SELECT FORMAT(meta_value, 2)

FROM wp_postmeta

WHERE meta_key='_price'

If in your table you have the value 15.002916 then the above select will render it to a string: 15.00.

Finally, if the data type of meta_value is a varchar (so, not a numerical data type), you can of course store the additional trailing zeroes:

UPDATE wp_postmeta

SET meta_value = FORMAT(meta_value, 2)

WHERE meta_key='_price'

But do realise that this only works as expected if the data type is of the text kind. In numerical data types the values 15.00 and 15 are exactly the same; it is just their display format which is different.