Numeric literal 0 is equal to any content in a VARCHAR column

In your last example, MySQL will try CAST

your column VARCHAR

to enter a numeric value. When you try to convert a non-numeric string to, for example, the conversion DECIMAL

stops at the first non- numeric character . An empty string will match0

For example:

> select cast('x123' as decimal);
+-------------------------+
| cast('x123' as decimal) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: 'x123' |
+---------+------+-------------------------------------------+

      

        
        
        
      

    

On the other hand, if you have leading numeric characters, they will be converted to decimal:

> select cast('123x' as decimal);
+-------------------------+
| cast('123x' as decimal) |
+-------------------------+
|                     123 |
+-------------------------+
1 row in set (0.00 sec)

      

        
        
        
      

    

In this case, non-numeric trailing characters will be silently ignored. Conversion to INTEGER will happen in a similar manner with the same results. More details can be found in the manual.