Databases
StackOverflow2013
Postsdbo
POWhat validation does MySQL do when strict mode is enabled?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat validation does MySQL do when strict mode is enabled?
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload