MySQL conversion from float (10,2) to fixed(10,2)

floating pointMySQL

I have a database that contains financial data stored as floats. Naturally, this can present a real issue at random times (eg, when there is an exact value that cannot be approximated closely enough with a floating-point algorithm).

My question is:

Will using ALTER TABLE table MODIFY field fixed(10,2) preserve the fixed value that is currently shown by MySQL when you select the field?
Is there any possibility that the value would be something other than that?

Best Answer

BAD NEWS : I do not think the value will be preserved !!!

I wrote this post (July 25, 2011) showing how sensitive floating point numbers are to conversions.

GOOD NEWS : Here is how you can safely compare the data conversion:

If your table has these characteristics

table is called moneytable
field you wish to convert is called moneyfield
primary key is called moneyid

Then run these commands:

DROP TABLE IF EXISTS moneytabletest;
CREATE TABLE moneytabletest LIKE moneytable;
ALTER TABLE moneytabletest MODIFY moneyfield fixed(10,2);
INSERT INTO moneytabletest SELECT * FROM moneytable;
SELECT A.moneyid,A.moneyfield olddata,B.moneyfield newdata
FROM moneytable A LEFT JOIN moneytabletest B USING (moneyid)
WHERE A.moneyfield <> B.moneyfield;

Here is the bottom line: If any rows come back from the LEFT JOIN query, the conversion will be bad. You will have to modify the definition of moneyfield, reload the moneytabletest table, and run the LEFT JOIN query again and again until no rows come back from the LEFT JOIN query. Once zero(0) rows come back, you will know what conversion will be safe.

Give it a Try !!!

Related Solutions

Mysql – How to fix slow LIMIT queries with big offsets without heavily rewriting them

You can add a new column to replies, call it position, and fill it with consecutive numbers of replies per thread (the position of the reply in the thread).

For example

id | thread_id | text | position
 1 |         1 | .... | 1
 2 |         2 | .... | 1
 3 |         1 | .... | 2
 4 |         1 | .... | 3
 5 |         2 | .... | 2
 6 |         3 | .... | 1

Further put an index on (thread_id, position, id) and it allows you to write queries like

SELECT * FROM replies
WHERE thread_id = 1234
AND position BETWEEN 125400 AND 125410
ORDER BY id ASC

which runs fast, since this does not need a full index scan.

You can either update this column in your application, or write a database trigger to do this automatically.

The initial effort is quite high I admit. We used this trick a few years ago on a high write frequented, quite large table, and like I said it cost some effort to get it running, but when the solution was in place, the performance gain was overwhelming.

MySQL – How to Unit Test Queries Based on Dates

Why I would not change the application

I do not like the answers which propose to change the application to fulfill testing needs. To me this is the most wrong way to go. The implementation should only be driven by concepts, requirements and design decisions of the application, not of any helper tools used while implementing.

Use dynamic fixtures instead

It is a very basic concept of all tests to set up a testing environment, also called test fixtures. This concept found its way into the unit testing as well, see for example fixtures in Rails, Python or Java. All propose to use fixtures for preparing specifically crafted databases:

Loading a database with a specific, known set of data

https://github.com/junit-team/junit/wiki/Test-fixtures

Fixtures allow you to populate your testing database with predefined data before your tests run.

http://guides.rubyonrails.org/testing.html#the-low-down-on-fixtures

This may involve, for example, creating temporary or proxy databases […]

https://docs.python.org/2/library/unittest.html

Nothing stops you from populating the test database dependent on the current date!

Actually this is pretty common anyway: For example every Rails object one creates gets the current timestamp as created_at and updated_at. Since they are created as part of preparing the fixture, all Rails objects are created with the date of the current test and thrown away afterwards.

I do not see a reason to verify the test fixture. If something is wrong concerning e.g. consistency, your application should throw an exception. To make sure that the tests cover all situations is your responsibility and no automatic verification will answer this question anyway.