Comparing string against numeric field returning unexpected results

Question

Given the table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Add a couple of rows:

INSERT INTO `users` (`id`,`name`) VALUES (NULL , 'Bob'),(NULL , 'Larry'),(NULL , 'Steve');

Why, OH WHY! does this query return results:

SELECT * FROM `users` WHERE id = "2this-is-not a numeric value"

Result:

query returned 1 row(s) in 0.0003 sec
id  name
-----------------
2   Larry

The string used in the where clause is clearly being converted to a numeric value -- who said to do that?! I cannot find any documentation that suggests mysql or PHP would presume to auto-cast my string literal.

This only works if the numeric character is the first one in the string, "this 2 is not numeric" would not return results. "12 2" would become 12, "1 2" (one-space-two) becomes 1.

Any articles or documentation explaining this behavior would be appreciated.

Answer 1

It's in the MySQL documentation here: http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html