title: Storing dollar amounts in MySQL: INT vs LONG vs BIGINT
body: <p>I am constructing a database where I need to store currency values. I am storing the currency values as cents ($100.00 = 10000 ¢). So, I decided against using <code>INT</code> to store the currency values (a signed int is limited to storing <code>$21,474,836.48</code>).</p>
  
  <p>I saw that MySQL has two other similar types: <code>BIGINT</code> and <code>LONG</code>.</p>
  
  <p>After researching and not being able to figure out the difference, I made an arbitrary decision to use <code>BIGINT</code>. But then when I went to write the PreparedStatement:</p>
  
  <pre><code>int id = ...;
  BigInteger amount = ...;
  
  String sql = "insert into transaction(id, amount) VALUES(?,?)";
  PreparedStatement pstmt = conn.prepareStatement(sql);
  pstmt.setInt(1, id);
  if(amount == null)
      pstmt.setNull(2, java.sql.Types.BIGINT);
  else
      pstmt.setBigInteger(2, amount); // &lt;---- This method does not exist
  </code></pre>
  
  <p>There is no <code>PreparedStatement::setBigInt()</code> method. The only <code>PreparedStatement</code> methods that exist are <code>setInt()</code>, <code>setLong()</code>, and <code>setBigDecimal</code>.</p>
  
  <p>So, then I reversed my decision and decided to use <code>LONG</code> instead, but when I went to write the same code, I noticed this:</p>
  
  <pre><code>int id = ...;
  BigInteger amount = ...;
  
  String sql = "insert into transaction(id, amount) VALUES(?,?)";
  PreparedStatement pstmt = conn.prepareStatement(sql);
  pstmt.setInt(1, id);
  if(amount == null)
      pstmt.setNull(2, java.sql.Types.LONG); // &lt;---- This java.sql.Type does not exist
  else
      pstmt.setLong(2, amount.longValue());
  </code></pre>
  
  <p>So the <code>PreparedStatement</code> does not have a <code>setBigInt</code> (or <code>setBigInteger</code>) method, and the <code>java.sql.Types</code> package does not contain a <code>LONG</code> enum.</p>
  
  <p>The questions are:</p>
  
  <ol>
  <li>What is the difference between <code>BIGINT</code> and <code>LONG</code> on the MySQL level? Which is more ideal for storing currencies?</li>
  <li>What would happen if I use <code>PreparedStatement::setLong</code> on a <code>BIGINT</code> column?</li>
  <li>That also begs the question, what happens if I use <code>PreparedStatement::setBigDecimal</code> on a <code>BIGINT</code> column? </li>
  </ol>
  
  <p>EDIT/UPDATE:</p>
  
  <p>My apologies for the bad question. I had no idea that MySQL doesn't even have <code>LONG</code> datatypes. In part because the data modeler I am using to construct my database (MySQL's own: MySQL Workbench) does allow you to set <code>LONG</code> as a datatype. However, when you forward engineer the database, it changes the <code>LONG</code> to <code>BIGINT</code>.</p>
  
  <p><img src="https://i.stack.imgur.com/tNszb.png" alt="picture from MySQL Workbench datamodeler which I&#39;m using to assist the construction of my DB"></p>
  
  <p>(picture from MySQL Workbench datamodeler which I'm using to assist the construction of my DB).</p>