Jeremy Jacob Jeremy Jacob - 2 months ago 7
MySQL Question

MySql strange behaviour - strips characters from right of id in where clause

I noticed something strange today. Say you have a mysql table:

create table usr(
usr_id serial
)


And you insert a row:

INSERT INTO usr (usr_id) VALUES ('1');


And then you run a query:

SELECT * FROM usr WHERE usr_id='1sfgfsdgs'


MySql returns the result as if you had entered:

SELECT * FROM usr WHERE usr_id='1'


Should this happen?? If yes - can someone point me to the documentation describing this? I'm just curious as to whether this is expected behaviour.

[jeremy@jjxps15 ~]$ mysql --version
mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1

Answer

Well this is the page that has the dirt, http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

But it's clear as mud

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

It does have a few examples that makes things a bit clearer

mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1

What this essentially means is that if a number is compared to an alpha numeric string and that string starts with a number all non numeric characters towards the end are discarded.

The fact that you use serial doesn't really have anything to do with this.