Mysql string implicit conversion causes query exceptions first look at the problem: 01 create table ceshi (id int (10) unsigned not null AUTO_INCREMENT, title varchar (1000) not null default '0 ', primary key (id); 02 www.2cto.com insert into ceshi (title) VALUES ('Hello '); 03 insert into ceshi (title) VALUES ('hello '); 04 insert into ceshi (title) VALUES ('0'); 05 insert into ceshi (title) VALUES ('1 '); 06 07 mysql> select * from ceshi where title = 0; 08 + ---- + -------- + 09 | id | t Itle | 10 + ---- + -------- + 11 | 1 | hello | 12 | 2 | hello | 13 | 3 | 0 | 14 + ---- + -------- + 15 www.2cto.com 3 rows in set (0.00 sec) 16 17 mysql> select * from ceshi where title = '0 '; 18 + ---- + ------- + 19 | id | title | 20 + ---- + ------- + 21 | 3 | 0 | 22 + ---- + ------- + 231 row in set (0.00 sec) Explanation: the field is of the varchar type. When you select * from ceshi where title = 0;, You need to convert the type of the title field to the int type for comparison. For a text or letter value, if it is converted to the int type, it is 0, so abc, Chinese, or something is converted to 0. If it is a number value, it is converted to the corresponding data, therefore, '0' is converted to 0, and '1' is converted to 1. In this way, all records except '1' meet the condition of = 0. The second statement is compared with the character '0', which does not require type conversion. The original value is compared. Therefore, only '0' is a completely consistent record. Www.2cto.com: Why is title = 0 not converted to character 0 for comparison? Because the title field is of the varchar type. It can be understood that constant 0 is written by yourself, indicating that your "intention" is to use the number 0 for comparison. If we say that all the constant values we write are converted, isn't this program hard to comprehend? In this conversion, you can use 0 and '0' to indicate whether to use a value or a character for comparison. This gives the user great freedom. If it is determined by the title type, it is not very difficult to compare it with numerical values. It also requires forced conversion such as cast, obviously, it is not as convenient as the existing rules.
Http://topic.csdn.net/u/20120921/11/b5166478-386f-4c63-90f6-a2bcab141170.html