Mysql string implicit conversion causes query exceptions _ MySQL
Source: Internet
Author: User
Mysql string implicit conversion causes query exceptions bitsCN.com
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 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 | title | 10 + ---- + -------- + 11 | 1 | hello | 12 | 2 | hello | 13 | 3 | 0 | 14 + ---- + -------- + 15 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. In addition, 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
BitsCN.com
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.