I have a MySQL database that demonstrates behavior that I would like to better understand. Why can I find the CHAR value inserted in the INT field? I have a field of type INT, but it looks like it can write character values, how is this possible?
I tried to isolate the problem by creating a database with INT and VARCHAR. I inserted "TEST1" into the INT value, but I could still search for the string using the value of the string ID. Warning after inserting row in ID value
| Warning | 1366 | Incorrect integer value: 'TEST1' for column 'ID' at row 1 |
but I could still look for that meaning. I would like to understand why this is possible.
mysql> CREATE TABLE test1(ID int, DATA varchar(255)); Query OK, 0 rows affected (0.18 sec) mysql> INSERT INTO test1(ID,DATA) VALUES('TEST1', 'TEST1'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT * FROM test1 WHERE ID = 'TEST1'; +------+-------+ | ID | DATA | +------+-------+ | 0 | TEST1 | +------+-------+ 1 row in set, 1 warning (0.00 sec)
Warning after SELECT
| Warning | 1366 | Incorrect integer value: 'TEST1' for column 'ID' at row 1 |
but the results are still true.
I would expect the SELECT above to find 0 results, but that is not the case, why?
ANSWER:
With Asaf's answer below and Pecky's comments, the answer seems obvious now.
During INSERT, MySQL was unable to insert the character value into the INT field to replace it with 0. The same thing happened during SELECT, so I did SELECT for ID = 0 for any character value that I was looking for.
mysql> SELECT * FROM test1 WHERE ID = 'SOMETHING_OTHER_THAN_TEST1'; +------+-------+ | ID | DATA | +------+-------+ | 0 | TEST1 | +------+-------+ 1 row in set, 1 warning (0.00 sec)
This returns the same result as my original choice, as it really works like
SELECT * FROM test1 WHERE ID = 0;
in the backend.
In any case, it is best to use sql_mode = 'STRICT_ALL_TABLES' in the MySQL configuration file or the SQL query itself.
To enable STRICT_ALL_TABLES for all SQL queries on the MySQL server, you need to add the following to the [mysqld] header in the my.cnf file, which is usually located in the /etc/my.cnf file
[mysqld] ... ... ... sql-mode=STRICT_ALL_TABLES