Mysql float precision issues with Rails

For my current project, I've had to redesign our old database and retain all of the old data. I wrote a rake task to copy all of the data (it's not a direct one-to-one mapping) which is working fine except for a few float values.

Some of the values are defined as f开发者_如何转开发loats in the old Mysql database. The issue is that my ActiveRecord queries are returning a rounded value instead of the actual value. If I run a query on the old database, I get:

mysql> select mean, format(mean, 10) from example_table;
+---------+------------------+
| mean    | format(mean, 10) |
+---------+------------------+
| 1.10844 | 1.1084357500     |
| 1.10223 | 1.1022269726     |
| 1.11771 | 1.1177104712     |
+---------+------------------+

The actual values of mean match the formatted value MOST of the time. Occasionally, some values have fewer significant digits and the format(mean, 10) will provide incorrect digits (e.g. 1.1 will give something like 1.1000001421).

In my rake task, I query the old database with:

values = ActiveRecord::Base.connection.execute('SELECT mean FROM example_table')

Unfortunately, values will contain that rounded value instead of the actual value. Is there a way to get the actual value stored instead of the rounded value?

I'm using Mysql Distrib 5.1.51 and Rails 3.0.9

Clarification:

The problem isn't with the new database precision. The problem occurs before I ever try to save the data.

values = ActiveRecord::Base.connection.execute('SELECT mean FROM example_table') #selects from the legacy database

Using that line, each mean that is returned is rounded by the time I have access to it. I have not done anything with the new database at this point so it shouldn't have anything to do with this.

In other words, when I query mysql, I want it to return the exact value that was inserted. That may have been 1.0 or 1.23456789. I don't care how many significant digits, I just want that exact value.

Somewhere between executing my select statement and AR returning the values, the values are rounded. I'm trying to understand where that point is.

---

I have been having an issue with precision myself, but I am working with currency values. In order to get around the rounding, I converted my float column to a decimal specifying a precision and scale appropriate for my app:

def self.up
    change_column :revenues, :amount, :decimal, :precision => 12, :scale => 2
end

If you know the upper bound for your precision and how many significant digits you need after the decimal, then you might be able to use the decimal approach. Also, when I ran the migration against my test database, all of my data was preserved and 'fixed' the weirdness I was seeing in my app.

---

This explains why you NEVER should use floating point for precision - floating are for quantities.

If one should measure the distance to the moon in light years - they will have a diviation, but if you use millimiters, as an integer, you would be very precise number - right ?

http://download.oracle.com/docs/cd/E19957-01/806-3568/ncg_goldberg.html

The reason why, is fractions between deci and hexa, cannot be transformed exactly.

When i do XML, i use an attribute containing the number of fractions (divisor) when doing high precision calculations - e.g. value="123456789" divisor="1000" - this allows for smooth handling to and from the different layers WITHOUT loosing anything ...

In MySQL I do not want to columns - so decimal representations, is your answer ...