为什么可以在MySQL int字段中插入字符? [英] Why can you insert characters into a MySQL int field?
问题描述
我有一个MySQL数据库，它表现出我想更好地理解的行为.为什么我要搜索插入到INT字段中的CHAR值?我有一个INT类型的字段，但是它似乎能够记录字符值，这怎么可能?
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?
我试图通过使用INT和VARCHAR创建数据库来隔离问题.我在INT值中插入了"TEST1"，但仍然能够使用ID字符串值搜索行.将字符串插入ID值后的警告是
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
| Warning | 1366 | Incorrect integer value: 'TEST1' for column 'ID' at row 1 |
但是我仍然能够搜索该值.我想了解为什么这是可能的.
but I was still able to search for that value. 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)
SELECT之后的警告是
The warning after the SELECT is
| Warning | 1366 | Incorrect integer value: 'TEST1' for column 'ID' at row 1 |
但结果仍然正确.
我希望上面的SELECT可以找到0个结果，但事实并非如此，为什么?
I would expect the SELECT above to find 0 results, but that is not the case, why?
答案:
借助以下Asaph的答案和Pekka的评论，答案似乎显而易见.
With the help of Asaph's answer below and Pekka's comments the answer seems obvious now.
在INSERT期间，MySQL无法将字符值插入到INT字段中，因此将其替换为0.在SELECT期间发生了相同的事情，因此实际上我对ID = 0的任何字符值都执行了SELECT搜索.
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.
mysql> SELECT * FROM test1 WHERE ID = 'SOMETHING_OTHER_THAN_TEST1';
+------+-------+
| ID | DATA |
+------+-------+
| 0 | TEST1 |
+------+-------+
1 row in set, 1 warning (0.00 sec)
返回的结果与我最初选择的结果相同，因为它的确运行为
That returns the same result as the my initial select since it really is running as
SELECT * FROM test1 WHERE ID = 0;
在后端.
无论如何，最佳实践似乎是在MySQL配置文件或SQL语句本身中使用sql_mode ='STRICT_ALL_TABLES'.
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.
要在MySQL服务器上为所有SQL查询启用STRICT_ALL_TABLES，您需要在my.cnf文件的[mysqld]标头下添加以下内容，该文件通常位于/etc/my.cnf
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
[mysqld]
...
...
...
sql-mode=STRICT_ALL_TABLES
推荐答案
You can prevent nonsense values from being inserted in the first place by setting the STRICT_ALL_TABLES mode which can be set in my.cnf or on the fly:
mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test1(ID int, DATA varchar(255));
Query OK, 0 rows affected (0.42 sec)
mysql> INSERT INTO test1(ID,DATA) VALUES('TEST1', 'TEST1');
ERROR 1264 (22003): Out of range value adjusted for column 'ID' at row 1
mysql> SELECT * FROM test1 WHERE ID = 'TEST1';
Empty set (0.00 sec)
mysql> SELECT * FROM test1;
Empty set (0.00 sec)
这篇关于为什么可以在MySQL int字段中插入字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
