0

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

2
  • 1
    Why are you even trying to check if an integer column is a string?
    – Philᵀᴹ
    Commented Aug 3, 2016 at 11:34
  • where mobile = '' makes no sense at all. An integer can not contain an (empty) string. Other DBMS would simply reject to compare apples with oranges.
    – user1822
    Commented Aug 3, 2016 at 19:07

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.