Sundar Sundar - 4 months ago 9
MySQL Question

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

Answer

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.