MySQL automatically cast/convert a string to a number?

Question

Does MySQL automatically casting\converting the string to numeric value?
How does that conversion works?

• '1234'=1234 ?
• '1abc' = 1 ?
• 'text' = 1 ?

Given that units.id is of bigint type, how this query will be interpreted?

SELECT table.* 
FROM table 
WHERE id='text'

Answer 1

The answers to your first three questions are: yes, yes, and no.

When the string 'text' is converted to a number, it becomes the value 0.

The documentation that describes type conversion is here.

For your query:

SELECT table.* 
FROM table 
WHERE id='text';

The rule is captured by this excerpt from the documentation:

In all other cases, the arguments are compared as floating-point (real) numbers.

In other words, this is actually equivalent to:

WHERE id = 0.0