title: What validation does MySQL do when strict mode is enabled?
body: <p>I was quite surprised when MySQL allowed me to insert a <code>NULL</code> into a field that was created with <code>NOT NULL</code>. I did some research and discovered how to enable strict mode. However, I am not quite sure what validation MySQL does when <code>STRICT_ALL_TABLES</code> is enabled.</p>
  
  <p><strong>The manual says:</strong></p>
  
  <blockquote>
    <p>Strict mode controls how MySQL handles input values that are invalid or missing. <strong>A value can be invalid for several reasons.</strong> (emphasis mine) For example, it might have the wrong data type for the column, or it might be out of range.</p>
  </blockquote>
  
  <p>I understand what it considers missing and how it handles that. I am unclear what it considers <em>invalid</em>. I've done some testing and discovered the following:</p>
  
  <ul>
  <li>strings that are too long are invalid</li>
  <li>numbers that are out of range are invalid</li>
  <li><code>NULL</code>s for a non-<code>NULL</code> column are invalid</li>
  <li><code>TRUE</code> and <code>FALSE</code> always seem to be valid (they become 1 and 0 respectively)</li>
  <li>invalid dates are invalid</li>
  <li>zero dates are valid (additional modes can be enabled to change this behaviour)</li>
  <li>floats in an integer field are valid (they get rounded)</li>
  <li>letters in a number field are invalid</li>
  </ul>
  
  <p>Does MySQL do any other validation checks other than what is mentioned above?</p>
  
  <p>The manual says 'wrong data type for the column', but the only situation I see where this actually comes into play is letters in a number field. Are there other examples of data type errors?</p>
  
  <p>Is there a list somewhere of exactly what checks MySQL performs?</p>
  
  <p><strong>EDIT:</strong> For the record, my application already does extensive validation. I am using strict mode as a last-chance, just-in-case check. If I forget to check something, I want it to <em>fail fast</em> rather than 'silently mangle my data'.</p>