mysql query ignores where clause when query for wrong data type

Question

I have a table that has a STATUS TINYINT(1) column and DEFAULT 0. All the records now have 0 for this column. When I query this with where status = 'active', the results seem to ignore this clause and return all the results. Is this the default behavior of SQL or MySQL?

Gordon Linoff · Accepted Answer · 2016-03-05 21:55:40Z

2

What is happening is that MySQL is doing silent conversion. When a string is used in a numeric context, it is converted to a number, based on the leading digits.

There are no leading digits in 'active'. So, the value is converted to 0. Hence, your logic become (after the conversion):

status = 0

Pay attention to types and to the constants used for comparison.

answered Mar 5, 2016 at 21:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

TurtleTread Over a year ago

Is there a way to have MySQL not do this? like give out an error?

Gordon Linoff Over a year ago

I don't think so (although I am not 100% sure). There is an option to prevent the conversion when loading data into tables.

Collectives™ on Stack Overflow

mysql query ignores where clause when query for wrong data type

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related