Why does "DELETE WHERE (VARCHAR)=0" execute successfully? DELETE FROM stats WHERE name=0


"name" is type VARCHAR(64). The above resulted in all records in the table being deleted, instead of just the 10~ that should have been affected. Only that many were actually equal to "0".

Why did this succeed in such cases as: "Mike"=0

Thankfully this was done on a personal development server and not production! I surely want to avoid this happening in the future.
When a string (VARCHAR) is compared to a number (0), any digits at the beginning of the string are converted to a number.  If there are no digits (eg, 'Mike'), the numeric value is 0.

WHERE 'Mike' = 0 -- succeeds
WHERE 'Mike' = '0' -- fails


Coding like that cannot be automatically prevented.  This is part of debugging your application.

Similarly, if you use an INT in a string context (eg, CONCAT()), the INT value will be converted to a string.