Select matches beginning numbers of string on integer

Question

Hi,

I have this table:

CREATE TABLE `test` (
    `ID` int(11) NOT NULL auto_increment,
    `text` varchar(250) collate utf8_unicode_ci default NULL,
    PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Content:

ID     text  
1      70-and-some-text  
70     blub

Then I make execute this query:

SELECT
    *
FROM
    test
WHERE
    ID = '70-and-some-text'
OR
    text = '70-and-some-text'

and get both lines as a result. It seems that MySQL tries to match as many numbers against ID and then stops. I think I read that somewhere, but couldn't find it anymore.

Answer and hints are highly appreciated. Thanks in advance. And be kind, I'm new. Tell me, if I did something wrong. I have a lot of respect for you guys.

Steffen

==================

Thanks to Tomalak, I know now where the problem comes from. Any best practices for dealing with this? I control the 'text', so I could go for '_70-and-some-text' or the the string with PHP. But a solution in MySQL would be the "purest".

Answer 1

This line

ID = '70-and-some-text'

makes MySQL convert the right-hand-side operand to an INT, since the column is of type INT. conversion obviously results in the number 70. You can pass any data type into a MySQL query in form of a string, MySQL does the necessary type-conversion transparently, like in this case.

Answer 2

At the moment I can't point out to if this behavior is described anywhere in the MySQL documentation but it boils down to the following.

MySQL tries to interpret any data sent to a number column (e.g. int, decimal) as a number, no matter if it makes sense or not to do so. Even a date could be interpreted as number or if MySQL isn't able to make out anything useful of the data passed along it simply stores 0.

e.g.

INSERT INTO yourtable (intcolumn) VALUES (NOW()); //2009
INSERT INTO yourtable (intcolumn) VALUES ('aa'); //0
INSERT INTO yourtable (intcolumn) VALUES (12.12); //12
INSERT INTO yourtable (intcolumn) VALUES (12.8); //13 (!)
.....

I guess this behavior could be turned of by using the right MySQL Server Mode