CODEWITHSUNDEEP
mysqlsql
cosmos
cosmos

Reputation: 2303

Mysql: comparing tinyint column with varchar value

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:

x1

0

0

Sqlfiddle

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

Upvotes: 1

Views: 2086

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 3

Related Questions