title: Why can you insert characters into a MySQL int field?
body: <p>I have a MySQL database that is exhibiting behavior I would like to understand better.  Why can I search for a CHAR value inserted into an INT field?  I have a field that is of type INT but it seems to be able to record character values, how is that possible?</p>
  
  <p>I tried to isolate the issue by creating a database with an INT and VARCHAR.  I inserted "TEST1" into the INT value but was still able to search for the row using the ID string value.  The warning after inserting the string into the ID value is </p>
  
  <pre><code>| Warning | 1366 | Incorrect integer value: 'TEST1' for column 'ID' at row 1 |
  </code></pre>
  
  <p>but I was still able to search for that value.  I would like to understand why this is possible.</p>
  
  <pre><code>mysql&gt; CREATE TABLE test1(ID int, DATA varchar(255));
  Query OK, 0 rows affected (0.18 sec)
  
  mysql&gt; INSERT INTO test1(ID,DATA) VALUES('TEST1', 'TEST1');
  Query OK, 1 row affected, 1 warning (0.00 sec)
  
  mysql&gt; SELECT * FROM test1 WHERE ID = 'TEST1';
  +------+-------+
  | ID   | DATA  |
  +------+-------+
  |    0 | TEST1 |
  +------+-------+
  1 row in set, 1 warning (0.00 sec)
  </code></pre>
  
  <p>The warning after the SELECT is</p>
  
  <pre><code>| Warning | 1366 | Incorrect integer value: 'TEST1' for column 'ID' at row 1 |
  </code></pre>
  
  <p>but the results is still correct.</p>
  
  <p>I would expect the SELECT above to find 0 results, but that is not the case, why?</p>
  
  <p><strong>ANSWER:</strong></p>
  
  <p>With the help of Asaph's answer below and Pekka's comments the answer seems obvious now.</p>
  
  <p>During the INSERT, MySQL failed to insert the character value into an INT field so it replaced it with 0.  The same thing happened during the SELECT so in effect I was doing a SELECT on ID = 0 for any character value I was searching.</p>
  
  <pre><code>mysql&gt; SELECT * FROM test1 WHERE ID = 'SOMETHING_OTHER_THAN_TEST1';
  +------+-------+
  | ID   | DATA  |
  +------+-------+
  |    0 | TEST1 |
  +------+-------+
  1 row in set, 1 warning (0.00 sec)
  </code></pre>
  
  <p>That returns the same result as the my initial select since it really is running as</p>
  
  <pre><code>SELECT * FROM test1 WHERE ID = 0;
  </code></pre>
  
  <p>in the backend.</p>
  
  <p>In any case the best practice seems to be to use sql_mode = 'STRICT_ALL_TABLES' in the MySQL configuration file or the SQL statement itself.</p>
  
  <p>To enable STRICT_ALL_TABLES for all SQL queries on a MySQL server you need to add the following under the [mysqld] header in your my.cnf file which is usually located in /etc/my.cnf</p>
  
  <pre><code>[mysqld]
  ...
  ...
  ...
  sql-mode=STRICT_ALL_TABLES
  </code></pre>