title: DOUBLE vs DECIMAL in MySQL
body: <p>OK, so I know there are tons of articles stating I shouldn't use DOUBLE to store money on a MySQL database, or I'll end up with tricky precision bugs. The point is I am not designing a new database, I am ask to find way to optimise an existing system. The newer version contains 783 DOUBLE typed columns, most of them used to store money or formula to compute money amount.</p>
  
  <p>So my first opinion on the subject was I should highly recommend a conversion from DOUBLE to DECIMAL in the next version, because the MySQL doc and everybody say so. But then I couldn't find any good argument to justify this recommandation, for three reasons :</p>
  
  <ul>
  <li>We do not perform any calculation on the database. All operations are done in Java using BigDecimal, and MySQL is just used as a plain storage for results.</li>
  <li>The 15 digits precision a DOUBLE offers is plenty enough since we store mainly amounts with 2 decimal digits, and occasionaly small numbers wit 8 decimal digits for formula arguments.</li>
  <li>We have a 6 years record in production with no known issue of bug due to a loss of precision on the MySQL side.</li>
  </ul>
  
  <p>Even by performing operations on a 18 millons rows table, like SUM and complex multiplications, I couldn't perform a bug of lack of precision. And we don't actually do this sort of things in production. I can show the precision lost by doing something like </p>
  
  <p><code>SELECT columnName * 1.000000000000000 FROM tableName;</code></p>
  
  <p>But I can't figure out a way to turn it into a bug at the 2nd decimal digit. Most of the real issues I found on the internet are 2005 and older forum entries, and I couldn't reproduce any of them on a 5.0.51 MySQL server.</p>
  
  <p>So as long as we do not perform any SQL arithmetic operations, which we do not plan to do, are there any issue we should expect from only storing and retreiving a money amount in a DOUBLE column ?</p>