Mysql: comparing tinyint column with varchar value

Question

I was looking into some code and some bug caught my eye. Someone compared tinyint column with varchar value.

But surprisingly this is working (as intention was to compare with numeric value 0).

Sample Query:

 create table t1(x1 tinyint);

 insert into t1 values (0),(0), (1), (2);

 select * from t1 where x1 = 'live'

Result:

My question is why (any) varchar value behaves the same way as numeric value 0 for int/tinyint column?

If you need to compare a varchar column with a int/tinyint column, there's probably something wrong with you db design. — jarlh
– jarlh, Commented Mar 1, 2016 at 13:10
@jarlh A use case when you may need to compare a varchar value with a column of arbitrary type is a general "search" field, which searches all columns to contain a given value. — iloo
– iloo, Commented Aug 17, 2018 at 17:08

Gordon Linoff · Accepted Answer · 2016-03-01 13:00:52Z

3

MySQL silently converts strings to numbers, in a numeric context.

It does so by converting leading digit-like characters to a number. If there are no digits, the value is 0.

So, this is equivalent to x1 = 0, because of this conversion.

answered Mar 1, 2016 at 13:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

encounter this today, very weird behavior. I expect an error or at least show an empty result

@LuqmanSungkar . . . You will get an error if you use cast(). Implicit conversion does not generate an error -- sometimes this is convenient.