MySQL Integer Column String Search Returns Invalid Result

MySQL

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

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

Best Answer

A column declared to be INT is designed to hold a number, but not a "phone number". It can be in the range of about +/- 2 billion.

For a phone number, use VARCHAR(20) CHARACTER SET ascii; that will probably be long enough (20 characters) to handle country-code, dashes, extension, and other kruft that the user might enter.

If you desire to validate that the number is a "valid" string, do that in your application code before trying to store it.

Related Question