Why SELECT "099anystring" = 99 returns true in Mysql?

user_4094231 0
Viewed 65

Yesterday I ran a query

UPDATE table SET col='newvalue' WHERE col = 99

where col is a VARCHAR

It updated rows like "099string" and "99something" which obviously resulted in wrong updates

I expected to update only where col = "99"

I am using 10.2.40-MariaDB-1:10.2.40+maria~bionic-log

PS: I know "099anystring" = "99" will work as I intend.

But that is not my question.

My question is what is the technical reason for "099anystring" = 99 returning true?

mariadb mysql
1 Answers

When you're comparing an integer with a string, it casts the string as an integer.

"099anystring" = 99 is the same as if you're writing 099 = 99 which in both cases return true

https://mariadb.com/kb/en/equal/

user_99424180
Related