MySQL – Implicit Type Conversion in Expressions

SQL's Implicit Type Conversion - Adam Johnson
Image source: https://adamj.eu/tech/

When an operator is used with operands with different types in MySQL queries, a type conversion occurs to make the operands compatible. 

The following rules describe how conversion occurs for comparisons operations:

1. If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

Example: 

Table Name – people 

SELECT *  FROM people WHERE lastname = NULL; will return no records as stated above because it returns NULL. The NULL-safe equality comparison operator can be used to compare null operands : SELECT * FROM people WHERE lastname <=> NULL; will return the record having firstname Arthur.  

2. If both arguments in a comparison operation are strings, they are compared as strings.

3. If both arguments in a comparison operation are integers, they are compared as integers.

4. If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. 

Example:

Table Name – people

SELECT date_of_birth from people where date_of_birth = 100; returns the record having firstname Alice.

SELECT date_of_birth from people where date_of_birth = 1; returns the record having firstname Bob.

5. If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal, integer or string value. 

Example: 

Table Name – people

SELECT * from people WHERE account_balance = ?

  • ‘100.0’ or ‘100’ or ‘100.000’
  • 100
  • 100.00

 All return the record having firstname Alice.

6. In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers

Example: 

Table Name – people

SELECT * from people WHERE bonus_code = 8; returns the record with firstname Alice. The string and integer are both converted to floating numbers before comparisons take place.

In summary, always try to compare type for type in MySQL to avoid unexpected results.

REFERENCES: