Numeric literal 0 is equal to any content in a VARCHAR column
In MySQL, VARCHAR is the same as numeric literal 0, but not numeric literal 1. What accounts for this behavior?
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.