List:General Discussion« Previous MessageNext Message »
From:Scott Haneda Date:July 20 2005 10:38pm
Subject:Re: Insert decimals with rounding
View as plain text  
on 7/20/05 2:23 PM, sean c peters at speters@stripped wrote:

> Hi all. 
> I am trying to figure out if there is a way to configure MySQL so that when
> inserts occur on decimal fields where the value being inserted has more
> precision than the field specification, then the value inserted is rounded
> instead of truncated.
> 
> For example, if i have a column:
> Acres DECIMAL(14,3) NOT NULL DEFAULT 0
> 
> and i'm inserting the value '1.5467'
> 
> currently, the value 1.546 is inserted.
> 
> But i want the behaviour to round this number to 1.547
> 
> Is this possible, or will i have to do that manually (programmatically) for
> all the decimal fields that i want to behave this way.

I think you can do it with the ROUND() function, using the precision
argument.

mysql> select ROUND(1.5467, 3);
+------------------+
| ROUND(1.5467, 3) |
+------------------+
|            1.547 |
+------------------+
1 row in set (0.00 sec)

So it would be something like:
INSERT into table 
SET foo = ROUND(1.5467, 3);
-- 
-------------------------------------------------------------
Scott Haneda                                Tel: 415.898.2602
<http://www.newgeo.com>                     Novato, CA U.S.A.


Thread
Insert decimals with roundingsean c peters20 Jul
  • Re: Insert decimals with roundingScott Haneda21 Jul
  • Re: Insert decimals with roundingMichael Stassen21 Jul