CODEWITHSUNDEEP
mysql
Sundar
Sundar

Reputation: 4650

MySQL integer column with string search returns the invalid result

Can anyone please help me to understand the MySQL select query behavior and help me to solve this,

How to show no results found message based on user search?

Table Structure:

CREATE TABLE `dummytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` int(11) NOT NULL,
  `welcome` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

enter image description here

Question: Why I am getting this result?

SELECT * FROM `dummytable` WHERE `mobile` = '\'\'' LIMIT 50


enter image description here

Upvotes: 0

Views: 1242

Answers (3)

Marcus
Marcus

Reputation: 1930

Your column mobil is an integer column. So everything is converted to int. An empty string is converted to 0 (and any other string not starting with a number will also be converted to zero I think)

Upvotes: 1

Pustovalov Dmitry
Pustovalov Dmitry

Reputation: 1047

After execution of this query MySQL shows you a warning message:

mysql> select * from foo where bar_integer='\'\''

....

10 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'okay' |
+---------+------+------------------------------------------+

MySQL tries to convert you string value to destination type (integer), if convertion fails mysql interprites it as 0 which results to what you see.

If you turn on MySQL strict mode this query would produce an error instead of making this implicit cast.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The column mobile is declared as an integer. When MySQL compares an integer to a string, then it converts the string to a number -- not the other way around.

How does MySQL do this? It converts the leading numeric characters to a number. Your condition is:

WHERE mobile = ''''

(The traditional SQL way to put a single quote in a string is to double it up.)

The single quote is not a numeric character, so the conversion results in 0. And hence, that is the result that you get.

Upvotes: 3

Related Questions