I like MySQL as well as the next person, but I’ve recently run into a problem with it that I think is going to lead to a divorce. What blows my mind though is that I can’t find anyone else on the Web who’s having the same problem with it that I am, and it makes me think that I’m doing something horribly wrong. I mean, I don’t see how people use MySQL for anything but storing Weblog content if MySQL has the bug that I think it has.

My problem started when I wanted to create a field that contains a currency value. In other words, a fixed point decimal number that is precise (normal floating point numbers are approximations, very close approximations, but approximations nonetheless). In the Java world, you have to use a class like BigDecimal for currency, because float is not precise. Same thing with MySQL, where you need to use the decimal field type rather than the floating point type. (See the documentation.)

I create my tables using Hibernate and XDoclet, so when it sees a BigDecimal in my code, it defines the column as decimal (31, 30), which means that the column can hold 31 digits, and 30 of them can be to the right of the decimal. You’d think, then, that a number like 1000 would be stored as 1000, or 1000.25 would be stored as 1000.25. Unfortunately, that’s not how things work. Let’s say I create a table like this:

create table example (example_num decimal(31, 30))

Then I run an insert statement like this: insert into example values (1000) The value stored in the database is 99.999999999999999999999999999999. This leads me to believe that there’s no way to store a fixed point number in MySQL. You can hack around it by defining the column as decimal(31,2), limiting the number of digits after the decimal. That’s still broken, though, because there’s rounding going on. Is it really true that there’s no way to store a fixed point number in MySQL unless you use a varchar field instead of a numeric field? Could things really be this broken?

I’ve started a QuickTopic for this. Maybe there’s something I’m missing.

Update: Looks like there are two problems here, involving bad documentation and a bad developer. The first problem is that if you define a column as decimal(31,30) in MySQL, it’s going to put 30 digits behind the decimal no matter what. I didn’t get that from the documentation. So if you put in any number larger than 99, it’s going to overflow. (That’s the 99.99999 thing.) The second thing is that if you insert a value into a decimal field, you have to put it in single quotes or MySQL will convert it to a floating point number first and you’ll lose precision (and sleep). I also figured out what I was doing wrong with XDoclet and Hibernate so now I can set up my decimal columns properly. Some days I hate software engineering.