title: Empty string in not-null column in MySQL?
body: <p>I used to use the standard mysql_connect(), mysql_query(), etc statements for doing MySQL stuff from PHP. Lately I've been switching over to using the wonderful MDB2 class. Along with it, I'm using prepared statements, so I don't have to worry about escaping my input and SQL injection attacks.</p>
  
  <p>However, there's one problem I'm running into. I have a table with a few VARCHAR columns, that are specified as not-null (that is, do not allow NULL values). Using the old MySQL PHP commands, I could do things like this without any problem:</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO mytable SET somevarchar = '';
  </code></pre>
  
  <p>Now, however, if I have a query like:</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO mytable SET somevarchar = ?;
  </code></pre>
  
  <p>And then in PHP I have:</p>
  
  <pre><code>$value = "";
  $prepared = $db-&gt;prepare($query, array('text'));
  $result = $prepared-&gt;execute($value);
  </code></pre>
  
  <p>This will throw the error "<code>null value violates not-null constraint</code>"</p>
  
  <p>As a temporary workaround, I check if <code>$value</code> is empty, and change it to <code>" "</code> (a single space), but that's a horrible hack and might cause other issues.</p>
  
  <p>How am I supposed to insert empty strings with prepared statements, without it trying to instead insert a NULL?</p>
  
  <p><strong>EDIT:</strong> It's too big of a project to go through my entire codebase, find everywhere that uses an empty string "" and change it to use NULL instead. What I need to know is why standard MySQL queries treat "" and NULL as two separate things (as I think is correct), but prepared statements converts "" into NULL. </p>
  
  <p>Note that "" and NULL are <strong>not</strong> the same thing. For Example, <code>SELECT NULL = "";</code> returns <code>NULL</code> instead of <code>1</code> as you'd expect. </p>